SQL SERVER: Presentation at Ahmedabad User Group Meeting

Last Saturday, 19th December 2009, I presented session on "Write CROSS TAB Query with PIVOT".

 

This was a regular User Group meeting held every third Saturday of month. I have presented session with Jacob Sebastian (SQL SERVER MVP) and Pinalkumar Dave (SQL SERVER MVP).

 

If you need PowerPoint Presentation and Demo script, send me an email at:tejasnshah.it@gmail.com. If you want to register for Ahmedabad User Group meeting, please register at: Click Here

 

Tejas Shah (me)

Tejas Shah (Me), Presenting session

Tejas Shah (Me, Presenting session

Pinal Dave (SQL SERVER MVP), Presenting session

User Group Members with President, Jacob Sebastian, Pinal Dave

If you want to register for Ahmedabad User Group meeting, please register at: Click Here

SQL SERVER: SSIS - Transfer Jobs Task

The Transfer Jobs task can be configured to transfer all jobs, or only specified jobs. You can also indicate whether the transferred jobs are enabled at the destination.

The jobs to be transferred may already exist on the destination. The Transfer Jobs task can be configured to handle existing jobs in the following ways:

• Overwrite existing jobs.
• Fail the task when duplicate jobs exist.
• Skip duplicate jobs.

Let's take an example to easily understand how to use Transfer Jobs Task with SSIS.

1. Select and Drag, Transfer Jobs Task, from Container Flow Items to designer surface.

2. To configure a task, Right click on  Transfer Jobs Task, which we dragged to Design surface. Click on "Edit.", you will get page as:

3. SSIS Transfer Jobs Task - General : Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we need to design this task.

4. SSIS Transfer Jobs Task - Jobs : Jobs page of the Transfer Jobs Task Editor dialog box is required to specify properties for copying one or more SQL Server Agent jobs from one instance of SQL Server to another. 

Let's take a view how each properties are used.

SourceConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the source server

DestinationConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the destination server.

TransferAllJobs: Select whether the task should copy all or only the specified SQL Server Agent jobs from the source to the destination server.  This contains two options:

TRUE: Which copy all jobs

FALSE: Which copy only selected jobs.

JobsList: Click the browse button (.) to select the jobs to copy. At least one job must be selected.

IfObjectExists: Select how the task should handle jobs of the same name that already exist on the destination server.

This property has the options listed in the following table:

FailTask: If job of the same name already exists on the Destination Server then task will fail.

Overwrite: If job of the same name already exists on the Destination Server then task will overwrite the job.

Skip: If job of the same name already exists on the Destination Server then task will skip that job.

EnableJobsAtDestination: Select whether the jobs copied to the destination server should be enabled. This contains two options:

TRUE: Enable jobs on destination server.

FALSE: Disable jobs on destination server.

5. SSIS Transfer Jobs Task - Expressions: Click the ellipsis to open the Property Expressions Editor dialog box.

Property expressions update the values of properties when the package is run. The expressions are evaluated and their results are used instead of the values to which you set the properties when you configured the package and package objects. The expressions can include variables and the functions and operators that the expression language provides.

Now let's run task, by right click on task and click on Execute Task, as shown in following figure. You can either Execute Package by right click on Package name, from Solution Explorer.

Once you run this then all/selected jobs will be transferred to destination server as per given criteria.

SQL SERVER: How to Read Excel file by TSQL

Many times developers asked that, they want to import data from Excel file. We can do this by many ways with SQL SERVER. 1. We can use SSIS package 2. Import/Export Wizard 3. T-SQL Today, I am going to explain, How to import data from Excel file by TSQL. To import Excel file by TSQL, we need to do following: 1. Put Excel file on server, means we need to put files on server, if we are accessing it from local. 2. Write following TSQL, to read data from excel file SELECT Name, Email, Phone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$]) NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file If you want to insert excel data into table, INSERT INTO [Info] SELECT Name, Email, Phone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$]) That's it.

SQL SERVER: How to remove cursor

Many times developer ask me that How can they remove Cursor? They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same. Please find this code to remove cursor with Table variable: --declare table to keep records to be processed DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100)) --populate table variable with data that we want to process INSERT INTO @Table(Column1, Column2) SELECT Column1, Column2 FROM <Table> WHERE <Conditions> --declare variables to process each record DECLARE @inc INT, @cnt INT --Assign increment counter SELECT @inc = 1 --Get Number of records to be processed SELECT @cnt = COUNT(*) FROM @Table WHILE @inc <= @cnt BEGIN --As we have AutoID declared as IDENTITY, it always get only one record. --Get values in Variable and process it as you want. SELECT @Column1 = Column1, @Column2 = Column2 FROM @Table WHERE AutoID = @inc --do your calculation here .... .... --Select next record SET @inc = @inc = 1 END By this way, we can remove CURSOR by Table variable. It is quite easy to implement. One more benefit is: It will process one record at a time, so it locks only that record at a time. Let me know if you have any questions.