SQL - SUBSTRING (Transact-SQL)

Syntax

SUBSTRING ( expression ,start , length )

Arguments

expression

Is a character, binary, text, ntext, or image expression.

start

Is an integer or bigint expression that specifies where the returned characters start. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned



Sample:

SELECT date, substring(recpt_desc, 1, 3), branch from DATABASE 
where date='2014-12-03'
order by branch
group by date, substring(recpt_desc, 1, 3), branch;

MS SQL Server: Create SQL Job using Transact-SQL

To create a SQL Server Agent job

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.
4. 1. USE msdb ;
      GO
      EXEC dbo.sp_add_job
          @job_name = N'Weekly Sales Data Backup' ;
      GO
      EXEC sp_add_jobstep
          @job_name = N'Weekly Sales Data Backup',
          @step_name = N'Set database to read only',
          @subsystem = N'TSQL',
          @command = N'ALTER DATABASE SALES SET READ_ONLY', 
          @retry_attempts = 5,
          @retry_interval = 5 ;
      GO
      EXEC dbo.sp_add_schedule
          @schedule_name = N'RunOnce',
          @freq_type = 1,
          @active_start_time = 233000 ;
      USE msdb ;
      GO
      EXEC sp_attach_schedule
         @job_name = N'Weekly Sales Data Backup',
         @schedule_name = N'RunOnce';
      GO
      EXEC dbo.sp_add_jobserver
          @job_name = N'Weekly Sales Data Backup';
      GO
      

   For more information, see:
• sp_add_job (Transact-SQL)
• sp_add_jobstep (Transact-SQL)
• sp_add_schedule (Transact-SQL)
• sp_attach_schedule (Transact-SQL)
• sp_add_jobserver (Transact-SQL)

.

MS SQL Server: Create SQL Job in SQL Server 2012

This topic describes how to create a SQL Server Agent job in SQL Server 2012 by using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects (SMO).

Limitations and Restrictions

• To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. 
• Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully.
• Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.

Security

• You must be a system administrator to change the owner of a job.
• For security reasons, only the job owner or a member of the sysadmin role can change the definition of the job. Only members of the sysadmin fixed server role can assign job ownership to other users, and they can run any job, regardless of the job owner.
• If you change job ownership to a user who is not a member of the sysadmin fixed server role, and the job is executing job steps that require proxy accounts (for example, SSIS package execution), make sure that the user has access to that proxy account or else the job will fail.

Using SQL Server Management Studio

To create a SQL Server Agent job

1. In the Object Explorer, click the plus sign to expand the server where you want to create a SQL Server Agent job.
2. Click the plus sign to expand SQL Server Agent.
3. Right-click the Jobs folder and select New Job….
4. In the New Job dialog box, on the General page, modify the general properties of the job. For more information on the available options on this page, see Job Properties / New Job (General Page)
5. On the Steps page, organize the job steps. For more information on the available options on this page, see Job Properties / New Job (Steps Page)
6. On the Schedules page, organize schedules for the job. For more information on the available options on this page, see Job Properties / New Job (Schedules Page)
7. On the Alerts page, organize the alerts for the job. For more information on the available options on this page, see Job Properties / New Job (Alerts Page)
8. On the Notifications page, set actions for Microsoft SQL Server Agent to perform when the job completes. For more information on the available options on this page, see Job Properties / New Job (Notifications Page).
9. On the Targets page, manage the target servers for the job. For more information on the available options on this page, see Job Properties / New Job (Targets Page).
10. When finished, click OK.
    
    
    .

MS SQL Server: SQL Script to Push data from 1 server to another Server

To insert/push data from one to another db, basically using common insert script : INSERT INTO SELECT statement

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

In my case:
I want to push data for integration purpose from
Server A: table dbA:Jdata1 and table dbA.Jdata2
to Server B: table dbB.Jdata1 and table dbB.Jdata2

Let say; server ip like this:
Server A: 10.10.1.20
Server B: 10.10.2.25

The script must take place in server which you create the Linked Server . It will look like this;

INSERT INTO [10.10.2.25].[dbB].[Jdata1]
(trx_id, name, amount, recept_no, trx_date)
SELECT (trx_id, name, amount, recept_no, trx_date)
FROM  [10.10.1.20].[dbA].[Jdata1] where trx_date = cast(dateadd(d, -1, GETDATE()) as DATE)

** cast(dateadd(d, -1, GETDATE()) as DATE) refer to yesterday transaction date

It same goes to Jdata2;

INSERT INTO [10.10.2.25].[dbB].[Jdata2]
(trx_id, account_no, amount_cr, amount_dr,trx_desc, trx_date)
SELECT (trx_id, account_no, amount_cr, amount_dr,trx_desc, trx_date)
FROM  [10.10.1.20].[dbA].[Jdata2] where trx_date = cast(dateadd(d, -1, GETDATE()) as DATE)

You can put both script in SQL Job Schedule step to be run daily.

Read more in: http://www.w3schools.com/sql/sql_insert_into_select.asp


.

MS SQL Server: How To Add Linked Server

*Notes; Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) > Add New Linked Server
   
   
   
   
   
   
2. The “New Linked Server” Dialog appears.  (see below).
   
   
   
   
   
   
   
   
3. For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
   
4. Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
5. Provider – Select “Microsoft OLE DB Provider for SQL Server”
6. Product Name – type: SQLSERVER (with no spaces)
7. Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
8. ProviderString – Blank
9. Catalog – Optional (If entered use the default database you will be using)
   

Define the Linked Server Security

Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.

1. Click OK, and the new linked server is created

.

MYSQL: GRANT ACCESS to DATABASE

GRANT ALL PRIVILEGES on db.* to 'username'@'applicationserverhost' IDENTIFIED BY 'password'

..

Install MySQL Server 5 on Ubuntu

Installing MySQL 5 Server on Ubuntu is a quick and easy process.
It almost feels like it should be more difficult.

Open a terminal window, and use the following command:

sudo apt-get install mysql-server

If you are running PHP you will also need to install the php module for mysql 5:

sudo apt-get install php5-mysql

To create a new database, use the mysqladmin command:

mysqladmin create [databasename]

See, really easy!

Source: http://www.howtogeek.com/howto/ubuntu/install-mysql-server-5-on-ubuntu/

MS SQL:Create Index (Transact-SQL)

An index can be created before there is data in the table. Relational indexes can be created on tables or views in another database by specifying a qualified database name.

Refer  notes from: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Sample:
create index PARAMETER on TABLE (COLUMN)

CREATE INDEX SHP_ID_CREATE_DATE

ON C.SHOPPER (CREATE_DATE)

CREATE INDEX ORDER_CREATE_DATE

ON E.ORDERS(CREATE_DATE)

.

Wordpress: Permalinks problem in UBUNTU

How to get WordPress permalinks / pretty links to work in Ubuntu 10.10 with Apache2

1. Manually create a ".htaccess" file and save it in your main WordPress directory. (This is the one with the wp-admin, wp-includes, and wp-content folders.)

2. Go to the Ubuntu terminal and type:
sudo chown -v :www-data "/enterYourFilePathHere/.htaccess"

You should see a line printed saying that the (group) file ownership has been changed to www-data (Apache2).

3. Give Apache2 write access to the file:
sudo chmod -v 664 "/enterYourFilePathHere/.htaccess"
You should see a line printed saying that the mode of the file has been retained.

4. Next, we have to allow WordPress to write to the .htaccess file by enabling mod_write in the Apache2 server. Type the following in the terminal:
sudo a2enmod rewrite

You should see a line printed saying that it is enabling mod rewrite and reminding you to restart the web server

5. So let's do that. Restart the web server, Apache2, for the changes to take effect by typing:
sudo /etc/init.d/apache2 restart

We are all done with the command line prompt; you can close the command line window now.

5. Go into your WordPress admin panel (i.e. http://yourDomain/wp-admin). Go to the Settings --> Permalinks and select the permalink format of your choice. Hit the "Save Changes" button.

6. DONE! Go to your site and check any page (other than your homepage) to ascertain that everything is working as expected.

**
7. The additional step I had to take was to edit /etc/apache2/sites-enabled/000-default.
    In that file you'll find an AllowOverride setting for /var/www, saying "None".

    Change the setting to say: AllowOverride All
    Restart the web server, Apache2, for the changes to take effect by typing:
  sudo /etc/init.d/apache2 restart
   

XAMPP: Apache cannot start because IIS

I was in a middle to activate my localhost using xampp, apache web service. But unluckily, my pc already have iis automatically run on startup. So I try to find a way to disable the IIS Service.

Finally I found this:

1. Control Panel > Programs and Features > Turn Windows features on or off. 

2. Uncheck the box for Internet Information Services.

3. Ok

Data Warehouse: Data Normalization

Data Normalization splits up data to avoid redundancy (duplication) by moving commonly repeating groups of data into new tables. 

Database Normalization Example

The following graphic gives an example of a typical conversion of an un-normalized table to multiple normalized tables.

Advantages of Database Normalization:

• Data integrity is maintained.
• Results of queries are predictable.
• Changes to data have to be made only once.

Amount of storage required by the database is optimal.

Read more reference:
http://en.wikipedia.org/wiki/Data_normalization
http://en.wikipedia.org/wiki/Database_normalization
http://www.sqlsteps.com/what-sql-classes-dont-normally-cover
.

Data Warehouse: Snowflake Schema

Snowflake Schema
In data warehouse, a snowflake schema is a logical arrangement of tables in a multidimensional database such that the entity relationship diagram resembles a snowflake in shape. The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions.

It is similar to the star schema. However, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema's dimensions are normalized with each dimension represented by a single table.

Example:Snowflake schema used by example query.

SELECT
        B.Brand,
        G.Country,
        SUM(F.Units_Sold)
FROM Fact_Sales F
INNER JOIN Dim_Date D             ON F.Date_Id = D.Id
INNER JOIN Dim_Store S            ON F.Store_Id = S.Id
INNER JOIN Dim_Geography G        ON S.Geography_Id = G.Id
INNER JOIN Dim_Product P          ON F.Product_Id = P.Id
INNER JOIN Dim_Brand B            ON P.Brand_Id = B.Id
INNER JOIN Dim_Product_Category C ON P.Product_Category_Id = C.Id
WHERE
        D.YEAR = 1997 AND
        C.Product_Category = 'tv'
GROUP BY
        B.Brand,
        G.Country

Read more: http://en.wikipedia.org/wiki/Snowflake_schema

Data Warehouse: Star Schema

Star Schema
In Data Warehouse, the combination of facts and dimensions is sometimes called a star schema.

The star schema (also called star-join schema, data cube, or multi-dimensional schema) is the simplest style of data warehouse schema. The star schema consists of one or more fact tables referencing any number of dimension tables.

Model (refer the graphic above)

• The fact table holds the metric values recorded for a specific event. Because of the desire to hold atomic level data, there generally are a very large number of records (billions). Special care is taken to minimize the number and size of attributes in order to constrain the overall table size and maintain performance. Fact tables generally come in 3 flavors - transaction (facts about a specific event e.g. Sale), snapshot (facts recorded at a point in time e.g. Account details at month end), and accumulating snapshot tables (e.g. month-to-date sales for a product).
  
• Dimension tables usually have few records compared to fact tables, but may have a very large number of attributes that describe the fact data.
  
  Read more: http://en.wikipedia.org/wiki/Star_schema

Data: Data Marts

Data warehouses can be subdivided into data marts. Data marts store subsets of data from a warehouse.

Data Mart is the access layer of the data warehouse environment that is used to get data out to the users. The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team.

In deployments, each department/business unit is considered the owner of its data mart including all the hardware, software and data.

Each department able to manipulate and develop their data any way they see fit; without altering information inside other data marts or the data warehouse. 

Read more: http://en.wikipedia.org/wiki/Data_mart

Data: Data Warehouse

I've been employed by new sub-govern company since last 2 weeks.
And till now I've been assigning different task from the first week to another weeks...
First, I was asked to backup finance system, then last week I was asked to help collection module for new system .. and today I just knowing will be assigned new task.. to handle data warehouse.
What is data warehouse?

Data Warehouse (DW or DWH) is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from multiple disparate sources. Data warehouses store current as well as historical data and are commonly used for creating trending reports for senior management reporting such as annual and quarterly comparisons.

The data may pass through an operational data store for additional operations before they are used in the DW for reporting.

ETL(extract, transform and load ) refers to a process in database usage and especially in data warehousing that involves:

• Extracting data from outside sources
• Transforming it to fit operational needs (which can include quality levels)
• Loading it into the end target (database, more specifically, operational data store, data mart or data warehouse)

The staging layer or staging database stores raw data extracted from each of the disparate source data systems. The integration layer integrates the disparate data sets by transforming the data from the staging layer often storing this transformed data in an operational data store (ODS) database.

The integrated data are then moved to another database, often called the data warehouse database. 

Read more: http://en.wikipedia.org/wiki/Data_warehouse 

.