August 8, 2010

SQL SERVER Session at Dotnetchaps - Developer Conference 2010

Dotnetchaps is a group of people focused on Microsoft technologies .Net and SQL Server. The User Group organizes technical sessions for sharing knowledge on .Net and SQL Server. The motto of this group is to Learn, Share and Grow.

DotNetChaps user group is also associated with community and officially opened the SqlPass chapter at Anand. The chapter name is Anand India Sql Server Users group.

Today, I am presenting a SQL SERVER session in this User Group meeting organized at SURAT, GUJARAT.

My session is at 2:10 PM which consists of following:

1. What's new in SQL SERVER 2008 R2?
2. Demo of SSRS Reports

In my SQL SERVER session, I am going to explain Utility Control Point (UCP), Data-Tier Application (DAC), Unicode compression. I am going to explain this feature by making examples on SQL SERVER R2, so people have better idea about it.

For SQL SERVER Reporting Services, its very simple to Design, develop and deploy report to report Server. It is very simple to integrate deployed report to web application.

August 1, 2010

SQL SERVER: Find executing queries

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.
SELECT OBJECT_NAME(ObjectID) as ObjectName,
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
It will give us details like about running SQL Objects, with Database name:
Reference : Tejas Shah

June 9, 2010

My First article on SQL SERVER Central about Access variables values from Trigger

I am glad to inform you all that, my article on "Accessing variable from Trigger", which I wrote a few days back, have been published by "SQL SERVER CENTRAL" site.

I would recommend all SQL lovers, to subscribe to the news letter of SQL SERVER CENTRAL, which consists of tips and tricks, useful in real world applications.

Please feel free to contact me at for any MS SQL SERVER query/help.

PS: Hope you all will read more and more articles from me, in forthcoming newsletters ;)

Enjoy Reading!!

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties

4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

Let me know your comments/questions.

March 7, 2010

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'

  • @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  • @OptionName, should be "startup" always.
  • @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.

That's it, I hope this is very clear to use this feature.

Reference : Tejas Shah (

January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue.

Let me share that problem with all readers.

With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ?

This is very practical problem that we need to fix.

To solve this, I come up with following solution.

1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.

 SSIS For Each Loop Container II 

2. Configure Derived Column:

SSIS Derived Column Data Flow Transformation Configuration 
As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression.

By default it assign UNICODE STRING DataType to this new column. We need to change it by:

A. Right click on "Derived Column", Go to Show Advanced Editor
B. Set DataType to String as:

SSIS For Each Loop Container II Advanced Editor

3. That's it. Now just add it to Destination Column Mapping with your Database column.

Let me know your suggestions.


December 20, 2009

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 If you want to register for Ahmedabad User Group meeting, please register at: Click Here
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
UG Group Photo
If you want to register for Ahmedabad User Group meeting, please register at: Click Here

December 7, 2009

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.

SSIS Transfer Jobs Task

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:

SSIS Transfer jobs Task Editor

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. 

SSIS Transfer jobs Task Editor Jobs

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.

SSIS Transfer jobs Task Editor JobList

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:

SSIS Transfer jobs Task Editor ObjectExist

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.

SSIS Transfer jobs Task Editor Expression

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.

SSIS Transfer jobs Task Editor Execute

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

December 3, 2009

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,

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

That's it.

December 2, 2009

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
--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 

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.