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

.