Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 
In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.
“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.
Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.
–Compatability View
select * from sysdatabases
–Catalog View
select * from sys.databases
Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:
–return information about authenticated session in SQL Server
select * from sys.dm_exec_sessions
–returns info about currently locked object.
select * from sys.dm_tran_locks
–this is DMF and retun I/O stat for MDF and LDF file.
select * from sys.dm_io_virtual_file_stats(DB_ID(N’master’), 2);
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 
In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.
“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.
Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.
–Compatability View
select * from sysdatabases
–Catalog View
select * from sys.databases
Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:
–return information about authenticated session in SQL Server
select * from sys.dm_exec_sessions
–returns info about currently locked object.
select * from sys.dm_tran_locks
–this is DMF and retun I/O stat for MDF and LDF file.
select * from sys.dm_io_virtual_file_stats(DB_ID(N’master’), 2);
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

LOOKUP Transformation task in SSIS 2008 to copy no match row from source to destination in SQL Server 2008

Yesterday I wrote one of the very simple articles to copy data from source SQL Server to destination at “Transfer data from one instance/database to another instance/database in SQL Server with SSIS”. You can use it, if task needs to be run only one time. If you want to keep running this job daily, weekly or after every certain period of time, you don’t need all data to be copied over again and again. You want to copy only those data which are not exists in destination table.
In order to achieve this task in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.
In order to do this job, we will need two dummy database and table in which we perform this exercise. Let us create two data ADV1 and ADV2, or you can use your two databases. Run following TSQL script.
–database 1
USE adv1
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′ union all
SELECT 1,14,’06/07/2010′ union all
SELECT 2,8,’06/08/2010′ union all
SELECT 2,10,’06/09/2010′
GO
–database two
USE adv2
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′
GO
Now, open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.
Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.
Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.
Once your ADO NET Source is ready with configuration, drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.
Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

From the connection tab, select your destination database and table. For more information, please look at the below given screen shot.

Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination. 
Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.
Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.
As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen
Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.
Now, run your package by hitting F5 and see output of the package.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

LOOKUP Transformation task in SSIS 2008 to copy no match row from source to destination in SQL Server 2008

Yesterday I wrote one of the very simple articles to copy data from source SQL Server to destination at “Transfer data from one instance/database to another instance/database in SQL Server with SSIS”. You can use it, if task needs to be run only one time. If you want to keep running this job daily, weekly or after every certain period of time, you don’t need all data to be copied over again and again. You want to copy only those data which are not exists in destination table.
In order to achieve this task in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.
In order to do this job, we will need two dummy database and table in which we perform this exercise. Let us create two data ADV1 and ADV2, or you can use your two databases. Run following TSQL script.
–database 1
USE adv1
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′ union all
SELECT 1,14,’06/07/2010′ union all
SELECT 2,8,’06/08/2010′ union all
SELECT 2,10,’06/09/2010′
GO
–database two
USE adv2
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′
GO
Now, open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.
Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.
Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.
Once your ADO NET Source is ready with configuration, drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.
Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

From the connection tab, select your destination database and table. For more information, please look at the below given screen shot.

Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination. 
Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.
Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.
As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen
Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.
Now, run your package by hitting F5 and see output of the package.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Transfer data from one instance/database to another instance/database in SQL Server with SSIS

There are many occasions where you need to transfer data from one instance/database of SQL Server to another instance/database of SQL Server. May be archiving, may be for ware house etc.
This is really very much needed utility, not only that, I have came across this question many times in different forums so though to explain this in my blog.
Today, I am not going into much details of each aspect, just transfer data from one source of SQL Server to another source.
Well, open new project from your VS2008. Project type “Business Intelligence Projects” and template should be “Integration services project”.
As soon as you create new project, drag “DataFlow” from tool box to “control flow” which is your work area. See image below for more detail

To configure that “dataflow” double click on that so that you will be redirected to the “Data Flow” tab, right beside “Control Flow” tab.
Now, take Ado.Net Source and Ado.Net Destination task from tool box and drop it into your work area. Connect Ado.Net Destination with Ado.Net Source. Now we will configure both tasks one by one.
Double click on Ado.Net Source task to configure it.
Note: I assume our source is AdventrueWorks database and HumanResouce.Employee table and destination is DataBaseNew on same server. Destination table should be created as “HREmployee” in destination database.
Create your database connection by clicking on “New” button in “Ado.net connection manager”.
From “Data Access Mode” select “Table or View” and from “Name of the table or View” should be “HumanResouce.Employee”. Configuration should look like this:

 Now, double click on “Ado.Net Destination” task. Set the destination database. If you already have table in destination database than select it or click on “new” and create one. Finally click on OK button and run the package by hitting “F5” and confirm whether data is there in your destination database.
This is really very basic example, I will post few more advanced article for this in very near future.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

For Loop container example in SSIS with C# script

Looping is one of the powerful tools since very long in each programming language. It has no different concept in SSIS too. Here in SSIS, For Loop container defining repetitive control flow in a package.
Generally in looping, three steps are very important. 1.) Initialization of loop variable 2.) condition for exiting the loop 3.) Increment/Decrement. In SSIS too, you have to be familiar with three steps only in order to execute For Loop Container.
 Let us now see the small example.
Create new project in BIDS (Business Intelligence Developer Studio) for this example and add two variables in the variable window.
1.)    “Count” type of Int32 with value of 5
2.)    “Increment” type of Int32 with value of 0
Once you are done with adding variable, drag For Loop container from tool box and drop it to the work area.  To configure loop container, double click on that.
Now, set following properties in “For Loop Editor”
1.)    InitExpression to @Increment=1
2.)    EvalExpression to @Icrement<@Count
3.)    AssignExpression to @Increment=@Increment+1
See the second paragraph of this article where I have mentioned three important steps for looping in programming language, same kind of three steps with different name we are setting up in “For Loop” container here in SSIS.
For more details, look at below screen shot.

Actually we are going to show message box for each iteration of “For Loop” container. Our Increment variable is set to 1 and it will run until it becomes greater than count variable which is 5. Show for displaying the message we are going to use “Script Task”.
Once you setup “For Loop” container, drag “Script Task” from tool box and put it inside “For Loop” container, double click on “Script Task” to configure it. We are going to use “Increment” variable inside the script task to display which iteration is going on. For using Increment variable, we have to select that variable in Script task. So, in “ReadWriteVariables” property of script task should have “Increment” variable, you can select that variable by clicking on Ellipse button besides “ReadWriteVariables” property. Once you set it, click on “Edit Script” button to write down script.
For more details, look at image below:

Once, you will click on “Edit Script” button, you will find one script edition and find “Main” method, which is our entry point, in that script editor. Put below given code in Main method and close “Script Editor” and click “OK” button in “Script Editor” dialog box.
System.Windows.Forms.MessageBox.Show(“Hi from SSIS, right now loop counter is at “ +  Dts.Variables[“Increment”].Value.ToString(), “information”);
            Dts.TaskResult = (int)ScriptResults.Success;
Now, you are ready to run this simple application by hitting “F5”.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

HTTP Connection Manager and Script task to download file via HTTP in SSIS 2008 with C# syntax

Download file via HTTP connection task would have lots of coding in .NET but in SSIS this task become much much easier with the help of “HTTP Connection Manager” , “HTTP Connection Manager” is one of the connection member of rich set of stock connection in SQL Server 2008 integration services.
Let us move ahead with creating package which can download the file from HTTP. Open new SSIS project.
To add “HTTP Connection Manager” in your package, right click on “Connection Manager” window and click on “New Connection”, from the “New Connection” dialog box, select “HTTP” and click on “Add” button.
For more detail, please look at below screen shot:
Once you insert “HTTP Connection Manager”, double click on it to configure. It will open dialog box editor, you have to give the path of file you wanted to download in “Server Settings” property.

I wanted to download my blog header so I am giving path of the same which is as below.

for more detail, look at below screen shot.

Once you are done with setup of “HTTP Connection Manager”, drag “Script Task” from tool box and drop it to the “Control Flow” which is your design area. Double click on “Script Task” to configure it and click on “Edit Script” button to write down script.
In the MAIN()  method which is our Entry Point, I am going to have following script.
Microsoft.SqlServer.Dts.Runtime.HttpClientConnection httpConn;
            Object obj;
            try
            {
                obj = Dts.Connections[“HTTP Connection Manager”].AcquireConnection(null);
                httpConn = new HttpClientConnection(obj);
                httpConn.DownloadFile(“d:\\SQLHub.jpg”, true);
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message, “”, 0);
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
As soon as you copy the script there, save and close script editor and run your package by hitting F5. You will see file created at the destination path. In this case, I would found SQLHub.JPG in D drive of my system.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Generate SP or function definition in SQL Server 2005/2008

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.
Look at the TSQL below:
select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id
–P for Stored Procedure
–FN for scalar function
–IF for Inlined table function
–TF for Table function
–TR for trigger
where so.type in (‘P’,‘FN’,‘IF’,‘TF’,‘TR’)
–if you want defination of specific function or SP, include below condition too
–and so.name=’YourSPorFunctionName’
Order by so.name,sc.colid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com