Ashish's Blog On SQL Server

SSIS File Operations – Copy File, Move File, Rename File and Delete File

Ashish Gilhotra — Tue, 31 Aug 2010 07:00:00 GMT

In the previous post we saw that how to Load data from Text files to SQL Server Tables and successfully created a package which loads data in to SQL Server table from a flat file. In this post today we will see SSIS File Operations – Copy File, Move File, Rename File and Delete File. Several times we need to do some file operations like copy, move, delete or rename file while performing the integration tasks. In SSIS we can accomplish these by File System Task control. The File System Task control is used for performing operations on files and directories such as move, create, delete, rename file and directories. We will see each and every operation separately.

Create SSIS Project

First we need to create an SSIS Project before starting with SSIS File Operations, you can create a SSIS Project by looking in to this previous SSIS post. Let’s say we created an SSIS project as FileOperations.

Copy File:

We can copy file from one place to another with the help of SSIS. We will look into the whole process of copy file from one place to another in this section, for this you need a New Package in your project which you can create easily by looking into my pervious SSIS post. Now we have a SSIS project named FIleOperations, it is the time to create a package in our SSIS project. Add a new package and name it as CopyFile.

Adding File System Task in Your Package

Now we have a package and it is time to add File System Task to your package, to add File System Task control to the package go to control flow times in toolbox and double click on File System Tasks or drag and drop it in your package(See the image below).

Configuring File System Task

Now we have File System Task control added to your package and we can configure it. To configure right click on the control and choose edit(See the image below).

After that you will be navigated to a new window named File System Task Editor in which you have to configure the File System Task(See the image below).

The options on File system task editor are as below.

IsDestinationPathVariable : Destination path is stored as variable or not.
DestinationConnection : File Connection or vairable.
OverwriteDestination : Overwrite if file exists with same name. True/False
Name : name you want to give.
Description : Little description
Operation : Choose copy file.
IsSourcePathVariable : Source path is stored as variable or not.
SourceConnection : File Connetion or variable.

Here we have to choose operation as Copy File choose OverwriteDestination as True and IsDestinationPathVariable and IsSourcePathVariable as False. Now we have to configure Source and destination connections.

Configuring Source and Destination Connections

Now we have to configure source and destination connections.

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your source connection. Choose Existing FIle as Usage Type and provide fully qualified path for file which you want to copy to other location(See the image below).

Destination Connection : Once you done with your SourceConnection you have to configure Destination Connection. Go to DestinationConnection tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your destination connection(See the image below).

Here we also have to provide Usage Type & Folder as in Usage Type we choose Existing Folder so we have to provide the destination folder where we want to have a copy of file.

Now you done with both Source and Destination connection and your final File System Task Editor looks like this(See the image below).

Click OK and you are done with your Copy File Task. Save it and execute package as we did in previous post and this will copy specific file from one place to another.

Rename File:

You done with Copy File, we will see the Rename File operation next. This operation will rename any file to the another name you want, for this we need to create a package named Rename. Once you have package added to your project open it and drag and drop File System Task control to your package as we did earlier in this post. Now we will configure that file system task to rename files. For this you have to right click on the package and choose Edit as we did earlier in this post. This will open up File System Task Editor, we have to configure that editor to rename files(See the image below).

We will choose Operation as Rename File(See the image below).

Now we have to configure Source and Destination Connection for Rename File System Task.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection choose Existing File as Usage Type and provide Fully Qualified Path in File(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want your renamed file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Create File as Usage Type and provide Fully Qualified Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Rename File Task. You can run this package as shown in previous post and you will get new file with name Renamed.txt at your destination path.

Move File :

In this Move File Task we will see how to move files from one location to another location. For this we have to add a package name Move in our project and add File System Task Control to your package by Drag & Drop from ToolBox. Once you have File System Task added to your project you have to configure this to move file by right click and choose edit on File System Task as we did earlier in this post(See the image below).

Now after this we need to configure Source and Destination Connection as we did earlier in this post.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want to move file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Existing Folder as Usage Type and provide Folder Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Move File Task. You can run this package as shown in previous post and you will get a new file at DestinationConnection and deleted from SourceConnection.

Delete File:

Delete File Task is used to delete specific file from the specific location. For this we need to create a new package by name delete in our sample project and add File System Task control to your package by Drag & Drop it from ToolBox. Once you have File System Task added to your package Right Click on File System Task and choose Edit to configure it to delete files from a specific location. Now we get File System Task Editor window on our screen choose Delete File as operation in File System Task Editor window(See the image below).

Now we have to configure Source Connection for this from where we like to delete files.

Configure Source Connection :

Go to SourceConnection tab in File System Task Editor and choose New Connection(See the image below).

Click Ok and you are done with SourceConnection and navigated back to File System Task Editor, Click Ok here and you are done with your Delete File Task run this package as shown in previous post and the targeted file will be deleted from the path specified in SourceConnection.

In this post we looked in to different File Operations(Copy, Rename, Move and Delete File). If you are facing any problems or you have any question regarding these file operations let me know by posting a comment, I will try to resolve it at the soonest.

Loading data from text files to SQL Server Tables – CSV Files, Tab Separated Files or Fixed Column Length Files

Ashish Gilhotra — Sun, 25 Apr 2010 15:59:00 GMT

In the previous post we looked in How to Execute SQL Query or Store Procedure from SSIS and successfully created a package which execute SQL Query or Procedure on targeted SQL Server with the help of this. In this post we look in How to Load Data from text Files to SQL Server, in daily life several time we need to load data in SQL Server tables, we can achieve this with the help of SSIS. We look into the detailed process of loading data from text files to SQL Server tables.

Load Data From Text File to SQL Server Tables

We can load data from text files in to SQL Server tables with the help of Data Flow Task Control. We will see how to use Data Flow Task in detail. Before that you need to create a project or a new package in existing project, you can create a new project or add a new package to project as shown in the previous post.

So you can add a new package in your SSIS project as shown in previous post, now you have a new package in your project name it as loaddata.

Adding Data Flow Task Control to Package

Now you have a package so this is time to add Data Flow Task to your package, to add Data Flow Task control to the package, double click on the item or just drag and drop item from from the toolbox to your package window.

Now you have Data Flow Task control on your package window the item will show up on the package like this (see the below image)

Configuring Data Flow Task

Now we have Data Flow Task added in your package, the next step is to configure Data Flow Task. To configure Data Flow Task right click on the control and choose edit option to configure the control.

You will see a new window opened at your screen and you are navigated to Data Flow tab within your current package (as shown in below image).

Now we have a blank Data Flow Task and we have to configure it to load data from a text/csv file and insert it into an SQL Server table. For this we have to configure Data Flow Task, and we need to setup a source connection for Flat File and a destination connection for OLE DB.

Setting up an Flat File Source

To configure a Flat File Source, go to the toolbox and under the Data Flow Sources list you can find Flat File Source. To add the Flat file Source to the package, double click on the control or simply drag and drop it to the package.

Now you have to configure Flat File Source, for configuring Flat File Source right click on Flat File Source control and choose edit and you will get a window name Flat File Source Editor( image as shown below).

Now you have to create a new flat file connection, for this click on the New button on the Flat File Source Editor (as shown in the image above) and you will see a new window named Flat File Connection Manager Editor (as shown in image below).

You have to create a new connection with the help of this Flat File Connection Manager Editor you have to fill following information in General tab to configure it correctly.

Connection Manager Name : Name of the connection.
Description : Description(anything you want to write).
File Name : Here gives the path of the file in file system.
Locale : Default Value.
Code Page : Default Value.
Format : Choose Delimited(you have options Delimited, Fixed width, Ragged right choose appropriate, in our case delimited.)
Text Qualifier : “(In our case)
Header row delimiter : {CR}{LF} (default).
Header rows to skip : 0 (if you want to skip some rows you can put it there like 2 or 10 or whatever).
Column names in first row : Leave it uncheck.

Fill up the following connection and click on Columns Tab you will see the columns of your file in this tab, you can also change many properties of your columns go to Advance Tab and you will options like Data Type, OuputColumnWidth etc. you can alter them as per need. Now click OK and you will get Flat File Source Editor window again just once check the columns tab and click ok and you are done with configuring Flat File Source.

Setting up an OLE DB Destination

We have a Flat File Source from which we can take data from a flat file, now we have to configure a OLD DB Destination through which we will put our data into SQL Table. For adding OLE DB Destination in your Data Flow Task, go to Data Flow Destinations block in the toolbox and locate OLE DB Destination (as shown in image below) and double click on the control or drag and drop it to your Data Flow Task window.

Now the OLE DB Destination control needs to be configured. To configure the control, right click on the control and choose edit and you will see a new window named OLE DB Destination Editor. In the Connection Manager tab of OLE DB Destination control you have to provide information like connection, load method and the table name in which you have to insert data. Click on the New button and Configure OLE DB Connection Manager window will open up(see image below) you can create a New Connection or you can choose an existing database connection.

In this example we are going to choose already created connection in previous post (you can create a new as shown in previous post) or you can alternatively choose to New Connection as Create it as shown in previous post.

Click ok after setting up a connection to database. In Data Access Mode option of OLE DB Destination Editor choose Table or view- Fast Load. Now you have to choose table in the database in which you want to insert data if you already have table or view in database in which you want to insert data then you can choose it directly from the dropdown else you have to create a New Table for this you have to click New button provided in front of dropdown box after clicking that a Create Table window will open up, we are using a New Table in this example (as shown below in image).

Now click Ok. Now navigate to Mappings option in OLE DB Destination Editor here you have to set the mappings between source and destination. Mapping decides which input column is in inserted to which output column. We map input and output columns as shown below in image and click Ok.

Now you are done column mappings and back on OLE DB Destination Editor now click Ok on this window and you are done with configuring OLE DB Destination and so your data flow task, your final Data Flow Task will looks like as shown below in image.

So now you are done with your package just save this package and run it by just right click on your package in solution explorer and click Execute Package. If your package succeeded than it will look like this( as shown in image below).

You can check the results by querying the newly created table in your database. For this you have to query your newly created table like this

SELECT * FROM tbl_names

and you will find 500 records in your table.

So in this way you can create a package which takes data from a text file and insert it into an SQL Server table, your file type may be different like you may have CSV, Fixed Width or Tab Separated file still you can insert data into SQL Server table using this kind of files you just have to choose appropriate file type in Flat File Source.

If you found any problem in configuring this package or came across to any error, just post that error or problems in comments we will make sure that it will be rectified as soon as possible.

Getting started with SSIS – Executing a SQL Server Query or Stored Procedure from SSIS

Ashish Gilhotra — Thu, 01 Apr 2010 16:44:00 GMT

This article is intended to help the people who are beginners to SSIS. In this article we will see what is SSIS? Why we use SSIS ? and how to create a package using BIDS (Business Intelligence Development Studio). The article describes this through an example which will show us how a SQL Server query or Store Procedure can be executed SSIS.

What is SSIS

SSIS is an ETL (Extract, Transform and Load) process and a component of Microsoft which can be used to perform a broad range of data migration tasks. SSIS lets the user to move data from one data source to another data source. We can also perform numerous other tasks with the help of SSIS.

How To Create Your First SSIS Package?

We will see How to Create Your First SSIS Package by looking into an example which will walk through us from the opening BIDS (Business Intelligence Development Studio) to Execute SQL from SSIS.

Creating a SSIS Project

For creating a SSIS package you must have SSIS (SQL Server Integration Services) and BIDS (Business Intelligence Development Studio) installed on your machine. After installing SSIS and BIDS you can open BIDS. To open BIDS go to the option SQL Server Business Intelligence Development Studio under Start>>>All Programs>>>Microsoft SQL Server (Edition).

Go to File>>>New>>>Project from SQL Server Business Intelligence Development Studio (as shown in the below image) to open a new project.

Once you see the New Project window, select the project type name as Integration Services Project and name the project as MyFirstSSIS. Select the location where you want to save the project by clicking on browse button or just type in the path.Once all information is filled up click on the OK button to save the project.

Adding a Package to your Project

When the project is saved, the project file will be saved under the specified path. The project will be saved with a default package which will be blank.

To add a package in your project right click on the SSISPackages node in the solution explorer at the left hand side of window or go to project option from the menu and select Add SSIS Package. Name the package you add as MyFirstPackage.

Adding Execute SQL Task in Your Package

Now you have a package added to your project and it is time to add the control flow item Execute SQL Task. Go to view menu and select Tool Box option to open the Tool Box (See the image below).

To add the control flow item Execute SQL Task to the package, double click on the item or simply drag and drop the item to your package window. Once the item is added the item will show up on the package (see the below image).

Now your package with raw Execute SQL Task will look like this now next step is to configure this Execute SQL Task.

Configuring Execute SQL Task

Now we have to configure the Execute SQL Task control to execute a SQL Query or SQL Store Procedure from SSIS. To configure Execute SQL Task right click on the control and choose Edit option to configure the control.

First of all we have to set up the database connection which will be used by this control, to set up the DB connection go to general tab of the properties window and set the connection type as OLE DB. Click on Connection and select New Connection to set up a new connection ( see the below image).

Setting up a Connection

When you select the New Connection the Connection Manager window will open up, where you have to set up the connection information. The following are the information need to be set up.

Provider : Select the one which is default.
Server Name : The SQL server name on which the query should be executed.
Authentication Type : The type authentication through which the SQL server needs to be connected, which can be windows or SQL Server.
Database name : Select the database to which we will query. If you do not find the database to which you were supposed to make the query on clicking the drop down, check your connection information.

To insure that the connection information you entered are correct , you can click on Test Connection button .

After clicking ok you are done with creating a connection and your New Connection is ready to use you can see your connection at the bottom of the window in the Connection Manager (See below image). To edit the connection, right click on the connection choose edit or just double click on the connection.

Now its time to write the SQL statement to be executed, to write the statement to be executed choose the option SQL Source Type as Direct Input and write the SQL statement in the column against the SQL Statement option on the Execute SQL Task Editor window. For an example we are taking an insert script which will insert a row when we execute the statement. Run the script provided below in your test Database to create a new table. Enter the insert script below against the SQL Statement in the Execute SQL Task Editor.

-- Create Table Run In your Database
CREATE TABLE [dbo].[DateInfo]( 
    [Date] [datetime] NULL, 
    [Year] [int] NULL, 
    [Month] [smallint] NULL, 
    [DayOfYear] [smallint] NULL, 
    [DayOfMonth] [smallint] NULL, 
    [DayOfWeek] [smallint] NULL, 
    [MonthName] [varchar](10) NULL, 
    [DayName] [varchar](10) NULL 
) ON [PRIMARY]

--Script to Insert Into table put it at the place of SQLStatement
INSERT INTO DateInfo 
SELECT 
    GETDATE(), 
    DATEPART(YEAR,GETDATE()), 
    DATEPART(MONTH,GETDATE()), 
    DATEPART(DAYOFYEAR,GETDATE()), 
    DATEPART(DAY,GETDATE()), 
    DATEPART(WEEKDAY,GETDATE()), 
    DATENAME(MONTH,GETDATE()), 
    DATENAME(DW,GETDATE())

Once you enter the statement, click the OK button on the bottom of Execute SQL Task Editor window and you are done with Configuring Execute SQL Task. Now Save the package and the package is ready to execute. To execute the package go to Solution Explorer on the right hand side of window Right Click on the package you created in the example it is MyFirstPackage and click Execute Package as shown in the below image.

If the package successfully runs, you can see that the control turns into green color ( as shown in the below image).

To insure that the package executed successfully you can query the table in your database by executing the query below.

SELECT * FROM DateInfo

And here is the result set for the query. You will find a record in your table after the successful execution of package.

You can also create a procedure of this insert query as stated below

CREATE PROC Sp_InsertInfo
AS
	INSERT INTO DateInfo 
	SELECT 
		GETDATE(), 
		DATEPART(YEAR,GETDATE()), 
		DATEPART(MONTH,GETDATE()), 
		DATEPART(DAYOFYEAR,GETDATE()), 
		DATEPART(DAY,GETDATE()), 
		DATEPART(WEEKDAY,GETDATE()), 
		DATENAME(MONTH,GETDATE()), 
		DATENAME(DW,GETDATE())

After creating the package you can write Exec Sp_InsertInfo instead of Insert Statement in the Execute SQL Task Editor and click OK to save the editor. Now you are done with your Execute SQL Task with the help of Store Procedure now again repeat the package and check data in the table you will get another row in your table.

So in this way you can create a simple package using Execute SQL Task. You can execute any other SQL Query or Procedure with the help of Execute SQL Task. We will see many other examples and controls of SSIS in future posts.

System Procedure sp_configure

Ashish Gilhotra — Wed, 17 Mar 2010 16:04:00 GMT

After a long period of time i am again starting blogging and try to keep it continue.I like to start a series of blog post about system store procedures used in SQL Server.To kick this series out i am picking up sp_configure system procedure.

Use of sp_configure

In simple words sp_configure is a process to change or display global settings of current SQL Server.You can see the global settings and change them with the help of this procedure.

There are many settings of SQL Server which we can see and change. I will provide a details of settings and their uses in detail in later posts.First we see the columns returned by this procedure and their meanings.

When you run sp_configure you will get following columns:

Column        Description
========      ======================================
Name          Name of the configuration option. 
Minimum       Minimum allowed value for the particular 
	      configuration. 
Maximum       Maximum allowed value for the particular
	      configuration. 
config_value  Value which we set by using sp_configure. 
run_value     Value which on which the particular configuration 
	      is currently running(we will see it in detail also).

How to run sp_configure

Ok so there are many settings on SQL Server which you can change from this procedure so here is the syntax for change the setting.

USE master;
GO
EXECUTE sp_configure 'option name','value';

After running that you will get a message like

“Configuration option ‘option name’ changed from ‘old value’ to ‘new value’. Run the RECONFIGURE statement to install.”

So as message states we have to run reconfigure statement like this

USE master;
GO
RECONFIGURE;

So in this way you can change your global settings(server level settings) from sp_configure.

Now lets look in to the details of config_value and run_value. Config_value shows the value which is set by or configured by sp_configure and run_value shows the value on which the configuration is currently running. For making it further clear we look into sys.configurations system view.

System View sys.configurations:

sys.configuration is a system view which hold same info as sp_run_configure. SYS.Configurations view have following columns:

Column                  Description
================        =====================================
configuration_id        Unique ID for the configuration value.
name                    Name of the configuration option.
value                   Configured value for this option.
minimum                 Minimum allowed value for the configuration
			option.
maximum                 Maximum allowed value for the configuration
			option.
value_in_use            Running value currently in effect for this
			option.
description             Description of the configuration option.
is_dynamic              Decides whether there is need to restart
			services or not.
is_advanced             1 = The variable is displayed only when the
			show advanced option is set.

So here is_dynamic column decides whether there is need to restart SQL Server services or not. As if value of is_dynamic is 1 then you just have to run reconfigure statement and you are done there is no need to restart your SQL Server services for this but if your is_dynamic column value is 0 this means "NOT DYNAMIC" and you have to restart your services and after that your settings will take effect.

When you change any configuration setting so before running "reconfigure" or before starting SQL Server Services you can see the values of "config_value" and "run_value" is different so you can differentiate these two columns as "run_value" is showing the currently running configuration value and "config_value" is showing the value you configured by sp_configure and after restarting SQL Server Services or running "reconfigure" both values will be again same.

In later posts we will see all the configurations value one by one there uses and effects and many other uses of sp_configure.

Temporary Tables and Table Variables An Overview

Ashish Gilhotra — Fri, 31 Jul 2009 14:50:00 GMT

Some days back i was asked about Temp Tables and Table Variables the basic difference why one is better than another one and all, and i am not able to provide a satisfactory reason\answer at that time so this is the time when i studied about a lots about it and i go through several topics, it takes one whole day and night to me to understand some aspects of this and here i am writing a detailed post about Temp Tables and Table Variables which may help some of peoples like me to understand in a better way.

So going through very basic details let talk about Temporary Tables first. So there are two types of temporary tables

A.) Local declared with one # Sign (#)
B.) Global declared with two # Sign (##)

Both of these tables created by simple CREATE TABLE (BOL) syntax except that # is used for Local Temporary Tables declaration and ## is used for Global Temporary Tables.

The Major difference between these two are Local Temporary tables (#) are visible in current connection and Global Temporary Table (##) are visible to all sessions or to the entire instance. You should always check for existence of global temporary table before creating it.

Let see with an example..

Open SSMS connect it and now create a Local Temporary table like this.

-- Creating Local Temporary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

This will create a temporary table named LOCALTEST now open other SSMS connect it and again retry to create another table with same name but using the new connection and check if it created or not so here it is ….

-- Create Temporary Table with same name

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Try to find out table with 'LOCALTEST'  name in TempDb

USE TEMPDB

GO

SELECT Table_Catalog, Table_Name FROM information_schema.tables

WHERE table_name like '%LOCALTEST%'

GO

So here is the result set for the above query

So its very clear from the result set that there are two Local Temporary Tables with the same name.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number, you can see that in this image.

But when you try the same thing with Global Temporary Tables then it will result into an error.

So here is some points about Temporary tables..

Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.
Local temporary tables will be dropped at the end of current session but if it is created inside a store procedure, it will be dropped when store procedure is finished.
Its name is limited to 116 characters.
Foreign Key constraints can’t be applied to primary tables.

Now we know a little about Table Variables :

Table Variables (@) introduced first in SQL Server 2000 and onwards as name suggest its a variable of type table. Its definition’s include column definition, names, data type and constraint(Foreign Key Constraint are not allowed). They are more flexible and and always stay in memory well this is not true that table Variables stay always in memory this is a MYTH as table variables may be stored as in same way in TempDb as temporary tables we will look at some example for this. They are created with the Declare @variable syntax. There are some properties for Table Variables

They have well defined limited scope and are not part of persistent database.
Transactional rollbacks doesn’t affect on them.
They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.
You don’t use them in nested procedures.
You not able to truncate a table variable.

So these are the some of basics for Table Variables and Temporary Tables but our Aim is to be find out which one is best in terms of performance, Advantages & Disadvantages for each and some considerations so we try to understand things why one is better then another and why not so

A.) Table Variables have a limited scope and are not part of persistent database, transaction rollback do not affect them.

Let’s understand what does it mean by transaction rollback do not affect them just take a simple example for this..

-- Create Tempoarary Table

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))

-- Declare Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(50))

-- Insert Values into both

Insert into #LOCALTEST SELECT 1 ,'Ashish'

Insert into @LOCALTEST SELECT 1 ,'Ashish'

-- Begin Transaction (Update both Temp table & Table Var)

BEGIN TRAN

    UPDATE #LOCALTEST SET C2='ASHISH GILHOTRA'

    UPDATE @LOCALTEST SET C2='ASHISH GILHOTRA'

-- Rollback Tran

ROLLBACK TRAN

 -- Check data

SELECT * FROM #LOCALTEST

SELECT * FROM @LOCALTEST

and here is the result set

From this it’s very clear that Table Variables doesn’t take part in rollback transactions.So it may be or may not be useful for developers depends on the developers.

B.) There is less locking and logging on table variables then temporary tables so what does it means so we take an another example to understand this ..

USE tempdb

GO

-- Drop table #LOCALTEST

-- Drop table DUMMYDATA

-- Create a dummy table

CREATE TABLE DUMMYDATA (A int, B varchar(100))

-- Populate Data in it

Declare @i int

set @i=1

while @i<1001

BEGIN

INSERT into DUMMYDATA SELECT @i,REPLICATE('A',100)

SET @i=@i+1

END

--select * from DUMMYDATA

--Using #LOCALTEST

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

BEGIN TRAN

    INSERT INTO #LOCALTEST(C1,C2)

    SELECT A,B FROM DUMMYDATA

-- Using @LOCALTEST

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

BEGIN TRAN

    INSERT INTO @LOCALTEST(C1,C2)

    SELECT A,B FROM DUMMYDATA

-- Now check for locks

select request_session_id, resource_type, db_name(resource_database_id),(case resource_type

      WHEN 'OBJECT' then object_name(resource_associated_entity_id)

      WHEN 'DATABASE' then ' '

      ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

resource_description,

request_mode,

request_status

from sys.dm_tran_locks

--Rollback tran

So if you going to run this query batch you will see lock acquire by “#LOCALTEST” not by @LOCALTEST so yes that true that Table Variables takes less locking then temporary tables.

for logging purpose just populate temp Table and Table Variable with same data as shown above and now use fn_dblog(Un Documented) to see what happen like this..

So here is a way to find logging

First of all see logging for Table Variables

-- Table Variable

DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))

    INSERT INTO @LOCALTEST(C1,C2)

    SELECT A,B FROM DUMMYDATA

-- update all the rows

update @LOCALTEST set C2 = replicate ('AB', 50)

-- Look at the top 10 log records.

select top 10 operation, AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

For this i don’t get any records of update log in this and try same for Temporary Tables you will get update logs in this..

-- Temp Table

--drop table #localtest

CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))

GO

    INSERT INTO #LOCALTEST(C1,C2)

    SELECT A,B FROM DUMMYDATA

-- update all the rows

update #LOCALTEST set C2 = replicate ('ab', 50)

-- Look at the log records. Here you get log records for update

select  operation,AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%LOCALTEST%'

order by [Log Record Length] Desc

And at last about the performance and IO usage for temporary table and table variables i use that example so the values vary for user to user so you can try the same on your systems and see what results you have with this…i am still working on this and as soon as possible i will be back with some results valid and good results to this till then you can try this at your end…

USE AdventureWorks

-----------------------------------------------------------------------

DECLARE @TEMP TABLE

(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)

,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)

INSERT INTO @TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)

SELECT  TOP 100 ADDRESSID,ADDRESSLINE1

,ADDRESSLINE2,CITY,StateProvinceID  FROM PERSON.ADDRESS

--SELECT * FROM @TEMP

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT a.*

FROM @TEMP A INNER JOIN PERSON.STATEPROVINCE B

ON A.StateProvinceID=B.StateProvinceID

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

---------------------------------------------------------------------------------------------

CREATE TABLE #TEMP(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)

,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)

INSERT INTO #TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)

SELECT  TOP 100 ADDRESSID,ADDRESSLINE1

,ADDRESSLINE2,CITY,StateProvinceID  FROM PERSON.ADDRESS

--SELECT * FROM @TEMP

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT a.*

FROM #TEMP A INNER JOIN PERSON.STATEPROVINCE B

ON A.StateProvinceID=B.StateProvinceID

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

DROP TABLE #TEMP

----------------------------------------------------------------

There are some more things like You can’t Alter Table Variable definition no DDL is applied on Table Variables and no statistics is maintained on table variable and statistics are used by query optimizer so there is a performance issue with this.

So after that experiments i think that this is totally depends on the developer that what he wants to use and what are the requirements. i will made a follow up post on this topic again as i may miss many of things on this vast topic so i really appreciate your suggestions and reviews on this if you found anything which i miss or wrong in this then drop a comment here we will try to make it clear as soon as possible and soon as i got more information about this i will write a follow up post soon.

Thanks

BackUp Status Script

Ashish Gilhotra — Wed, 22 Jul 2009 19:17:00 GMT

This time its all about a script a backup script.Today early morning i asked to get BackUp details for some of DB’s we have, usually they ask me about the last BackUp taken so its easy for me to tell him. But this time he needs all the history of BackUp’s for some reason.So i know that iformation is stored somewhere is System databases so started goggling for it and i found it on mssqltips.com and the script is written by Thomas LaRock(aka SQLRockStar) and i modified this script according to my use by taking his prior permission i modified his script and add the details about the restoration of BackUp’s and location of BackUp’s basically i added information about the Restorations of BackUp’s, so here is my modified script about BackUp status which returns some very useful information about BackUp’s and restore.

-- Script Returns BackUp Status and corresponding Restore Status for all Databases BackUps

DECLARE @DBNAME VARCHAR(100)

SET @DBNAME=NULL  -- Default NULL(All Databses)

select 'BackUp Name'=BS.name,

'User Name'=BS.user_name,

'Start Date'=BS.backup_start_date,

'Finish Date'=BS.backup_finish_date,

'Backup Type'=Case when BS.type='D' then 'FULL Backup'

              when BS.type='L' then 'Transaction Log Backup'

              when BS.type='I' then 'Differential Backup' end

,'Backup Size MB'=floor(((BS.backup_size/1024)/1024))

,'DbName'=BS.database_name

,'Server Name'=BS.server_name

,MF.physical_device_name

,'IS Ever Restored'=case when BS.backup_set_id in

(select backup_set_id from msdb.dbo.restorehistory)

 then 'Yes' else 'No' end

,'Destination Db'

=isnull(RH.destination_database_name,'Yet Not Restored From This BackUpSet')

,'Restore Path'

=isnull(min(RF.destination_phys_name),'Yet Not Restored From This BackUpSet')

,'restore Type'=isnull(CASE WHEN RH.restore_type = 'D' THEN 'Database'

              WHEN RH.restore_type = 'F' THEN 'File'

              WHEN RH.restore_type = 'G' THEN 'Filegroup'

              WHEN RH.restore_type = 'I' THEN 'Differential'

              WHEN RH.restore_type = 'L' THEN 'Log'

              WHEN RH.restore_type = 'V' THEN 'Verifyonly'

              WHEN RH.restore_type = 'R' THEN 'Revert'

              ELSE RH.restore_type

             END ,'Yet Not')

,Rh.restore_date,'Restore By'=isnull(RH.user_name,'No One')

,'Time Taken'

=cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/3600 as varchar(10))

+' Hours, ' +

cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)/60 as varchar(10))

+ ' Minutes, ' +

cast(datediff(ss,BS.backup_start_date,BS.backup_finish_date)%60 as varchar(10))

+' Seconds'

from msdb..backupset BS

JOIN msdb..backupmediafamily MF

on BS.media_set_id=MF.media_set_id

left outer join msdb..restorehistory RH

on BS.backup_set_id =RH.backup_set_id

left outer join msdb..restorefile RF

on RF.restore_history_id=Rh.restore_history_id

where BS.database_name = isnull(@DBNAME,BS.database_name)

group by BS.name,BS.user_name,BS.backup_start_date,BS.backup_finish_date,

BS.TYPE,BS.backup_size,BS.database_name,BS.server_name

,MF.physical_device_name,BS.backup_set_id,RH.destination_database_name

,RH.restore_type,Rh.restore_date,RH.user_name

Here this scripts take a Database name as parameter and is you don’t supply that parameter that by default it shoes information about all databases

Here what this script returns :

BS_Name = Name of BackUp
BS_UserName = Name of User from which BackUp had taken.
backup_start_date = Start Date Time of BackUp
backup_end_date = End Date Time of BackUp
BackUp Type = Type of BackUp
BackUp Size = Size of BackUp File
Dbname = Name of database
ServerName = Name of Server
Physical_Device_Name = name of Drive or Device where backup stored
Is Ever Restored = is BackUp Ever Restore(Yes/No)
Destination Db = Name of Database Restored By BackUp
Restore Path = Path from database restored
Restore type = Type of restoration
restore_date = Date when Database Restored
restore by = User by which database in restored
Time Taken = Total time taken to take Database BackUp

So in this way this script return some useful information regarding BackUp’s and Restoration of backup’s its really make sense if you schedule this script to send through mail to get updates about your BackUp’s and Restore information so its really a good idea if we send this information in mail so in next blog post i will write how to send this information using
e-mail to direct in your inbox through Database nail in SQL Server.

SO if you have any suggestions regarding this script and do let me now or if you found any other method then do post here any kind of suggestions are welcomed

Thanks….

T-SQL Challenges

Ashish Gilhotra — Wed, 08 Jul 2009 09:05:00 GMT

Hi All …

You all must here about T-SQL Challenges, these challenges are ideal to learn Set Based Queries using TSql. The most beneficial part of these challenges is that, through these challenges , you get a chance to see better solution to solve a given problem, T-SQL Challenges is mainly focused on writing Set Based Queries using T SQL. You can find previous challenges, winner of all challenges, and the best possible for all solutions by T SQL Challenges team as well as all the solutions provided by the winners with explanation of their solution’s here (ie TSQL Challenge #8)on TSQL Challenges Blog at beyondrelational.com ; and TSQL challenge team also provided a winning certificate for TSQL Challenges winners.Here is my certificate for my winning entry of TSQL Challenge #1.

The most wonderful effort put in to these challenges by TSQL Team is that they provide a free training webcast for those peoples who interesting in challenges , but not able to solve it.The training webcast helps us to write complex set based queries in simple manner and enhance our T SQL Set Based Quering Skills. So if you able to solve the challenge then send your entries else join the training webcast and learn how to write/solve challenges.

So hope to see you all having fun solving these challenges.

Thanks

Who Created a Table – DDL Triggers in SQL SERVER 2008

Ashish Gilhotra — Tue, 30 Jun 2009 15:02:00 GMT

Hi all this post is about the new features in SQL Server 2008 .. SQL Server 2008 has a power full feature called DDL triggers which uses for Auditing changes in database and this is very useful too. You will find a blog entry about auditing using transaction...(read more)

Encryption & Decryption Part II

Ashish Gilhotra — Mon, 29 Jun 2009 14:34:00 GMT

Hi all me again here with a post about Encryption & Decryption in Sql Server 2005.I have received a couple of questions regarding my article Encryption & Decryption in Sql Server 2005 , so i started responding through mail but because i found...(read more)

Sparse Columns – Introduction

Ashish Gilhotra — Thu, 11 Jun 2009 16:13:00 GMT

So after a long i get a chance to work on SQL Server 2008 and understand it in better way. So today we look into a new storage feature in SQL Server 2008 as Sparse Column. Sparse column takes no physical usage for a column which may have “NULL”...(read more)

TechEd on Road – Ahmedabad June 20, 2009

Ashish Gilhotra — Thu, 11 Jun 2009 07:46:00 GMT

Hi all …

This time a great news for all the technology enthusiast and want to meet great peoples in technology and you missed TechEd India 2009 so you can attend now the same kind of event in Ahmedabad, India on June 20, 2009 Saturday(TechEd on Road) by 1:30 PM.

You will get a chance to meet Two MVP’S here Jacob Sebastian and Pinal Dave , who presents numerous technical sessions on SQL Server, Exchange Server 2010, Windows Server 2008 and Virtualization.

Anyone who is interested in technology can attend this event .This is free event no charges will be taken to attend this event. You can get detailed information about this event on Pinal’s Blog and Jacob’s Blog .

So mark your calendar for this grand event in Ahmedabad here are the details of this event ::

Location ::

Hotel Rock Regency
C.G. Road
Ahmedabad, India.

Date & Time::

June 20, 2009 Saturday
1:30 PM

Hope to see you there on time.

Encryption & Decryption in SQL SERVER 2005

Ashish Gilhotra — Sat, 30 May 2009 13:44:00 GMT

Hi all,

Here is a very powerful feature of SQL Server 2005 is Data Encryption & Decryption, in Sql Server 2000 if we like to Encrypt Data then we have to write our own functions to encrypt the data. In SQL Server 2005 and SQL Server 2008, these functions are available by default.

SQL Server provides the following method to encrypt your data….

ENCRYPTION by passphrase
ENCRYPTION by symmetric key
ENCRYPTION by asymmetric key
ENCRYPTION by certificate

So here we try to encrypt the data by using Symmetric keys.

So in this we firstly create a database and create master key ,certificate ,symmetric key table and do encryption so here we go…

Create Database ..

USE MASTER

GO

-- First Create a DATABASE

CREATE DATABASE ENCRYPTDECRYPTTEST ON PRIMARY

(NAME=N'ENCRYPTDECRYPTTEST' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.mdf')

LOG ON

(NAME=N'ENCRYPTDECRYPTTEST_LOG' ,FILENAME=N'D:\ENCRYPTDECRYPTTEST.ldf')

Use database and Create master key, certificate and symmetric key

-- Use database

USE ENCRYPTDECRYPTTEST

GO

-- Create Master Key

CREATE MASTER KEY ENCRYPTION BY

    PASSWORD ='ASHISH'

GO

-- Create Certificate

CREATE CERTIFICATE ENCDECTEST

    WITH SUBJECT='ASHISH'

GO

-- Check it

SELECT * FROM SYS.CERTIFICATES

-- Create Symmetric Key

CREATE SYMMETRIC KEY TESTKEY

    WITH ALGORITHM = TRIPLE_DES

    ENCRYPTION BY CERTIFICATE ENCDECTEST

GO

In the above create symmetric key T-Sql statement the algorithm parameter is the most important part…

SQL SERVER can use the following algorithms in encryption:

DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 AND AES_256.

Now create a table …

-- Create a Sample Table

CREATE TABLE USERS (UID INT IDENTITY(100,1),UFNAME VARCHAR(100),ULNAME VARCHAR(100),ULOGINDI VARCHAR(50),UPASSWORD VARBINARY(256))

So now its turn to insert data in USERS table and we encrypt the UPASSWORD field using our symmetric key this can be done by a Store Procedure so we create a Insert Procedure for this.. so here is the procedure….

-- Create a Procedure to Insert Data in Table

CREATE PROC [InsertUSER]

  @UFNAME VARCHAR(100),

  @ULNAME VARCHAR(100),

  @ULOGINDI VARCHAR(12),

  @UPASSWORD VARCHAR(20)

AS

BEGIN

-- you must open the key as it is not already

    OPEN SYMMETRIC KEY TESTKEY

        DECRYPTION BY CERTIFICATE ENCDECTEST;

-- Insert statement

    INSERT INTO [USERS]

    (UFNAME, ULNAME, ULOGINDI, UPASSWORD)

    VALUES

    (@UFNAME, @ULNAME, @ULOGINDI,

     EncryptByKey(Key_GUID('TESTKEY'), @UPASSWORD));

END;

So after creating procedure you can run these procedures by following parameters and check the data in table…

-- Run Insert Procedure

EXEC INSERTUSER 'ASHISH','GILHOTRA','ASHISH','MYPASSWORD'

-- Check the Data

SELECT * FROM USERS

and the results will be like this…

Now we create a Store Procedure to extract the decrypted data so here we go and create a store procedure to extract data in Decrypted form…

and run that procedure…

-- Procedure to Retrive Users

CREATE PROC [RetrieveUSER]

    @UID INT

AS

BEGIN

-- you must open the key as it is not already

    OPEN SYMMETRIC KEY TESTKEY

        DECRYPTION BY CERTIFICATE ENCDECTEST;

-- Select statement

  SELECT UID, UFNAME, ULNAME, ULOGINDI, CONVERT(VARCHAR(20),DECRYPTBYKEY(UPASSWORD)) AS PASSWORD

  FROM USERS

  WHERE UID = @UID;

END;

-- Execute Retrive Procedure

EXEC RetrieveUSER 100

and the results will be like this….

So in this way you can protect your sensitive data like passwords, credit cars numbers and many more,,,,
there also some others way to do encryption in SQL Server 2005 and SQL Server 2008 .

Thanks & Happy Querying…

Running Out of System Table

Ashish Gilhotra — Thu, 21 May 2009 13:23:00 GMT

Hi all,

Today i encounter a issue with my instance as i am using the table SPT_VALUES from the master database available in our Sql Server, so when i tried to fetch some records from master..spt_values then it shows me error…

Here is a example of query and the error thrown by SQL is..

SELECT * FROM orders WHERE order_id in (SELECT number FROM master..spt_values WHERE type='P')

and Error is..

Msg 208, Level 16, State 1, Line 1
Invalid object name 'master..spt_values'.

So it is very uncommon thing to me to loose table from Master Database in Sql Server 2005. But i found that it is not a very uncommon thing because if you can right click on the table present in Master Database and choose the ‘DELETE’ option then table would be deleted from your server.

Now what happen next when tables get deleted from your Master Database, there are some information which stored in Tables in Master Database…

So i googled a lot about this and here is the solution…

There is a script file named “u_tables.sql” in “installation directory >MSSQL>Install” folder .

Execute this script against the instance that has these missing tables, and you will get the tables again.

But this script has limitations because its only able to recreate /reproduce only two System Tables “dbo.spt_monitor and dbo.spt_values” .

So in this way you can get your tables back in Master Database..But it is highly recommended that you don’t delete tables present in Master Database.

Thanks.

Error/Exception Handling in SQL Server 2005

Ashish Gilhotra — Sat, 09 May 2009 16:16:00 GMT

Being a .Net developer, the most useful feature i found was the Error Handling or Exception Handling technique.When it comes to programming languages , all popular OOP languages have TRY()…Catch() method to handle an exception.

In Sql Server 2005, error handling was made easy . This article demonstrates various methods of handling errors using
Try()..Catch() in T-SQL.

Here you try something like that…

declare @i tinyint

select @i=count(*) from sys.sysobjects

select @i*999999999 as myval

When you try to run this you will get a error like this…

-- Error

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 1812.

(1 row(s) affected)

Now try to run it something like this….

begin try

    declare @i tinyint

    select @i=count(*) from sys.sysobjects

    select @i*999999999 as myval

end try

begin catch

    select

    error_line() as errornumber

end catch

you will get result like

errornumber
------------------
4

so now this time it throws the number of error.

You can also get more parameters like Error_Line from this like,,,

begin try

    declare @i tinyint

    select @i=count(*) from sys.sysobjects

    select @i*999999999 as myval

end try

begin catch

    select

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage;

end catch

like this……

Now you can also make a Store Procedure like this which throws error and use this in your try()..catch() statement like this…

CREATE PROCEDURE Sp_ThrowErrorInfo

AS

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage;

GO

then try like this..

BEGIN TRY

    declare @i tinyint

    select @i=count(*) from sys.sysobjects

    select @i*999999999 as myval

END TRY

BEGIN CATCH

   Exec Sp_ThrowErrorInfo

END CATCH;

This gives you same results as above.

You can also use it with a procedure like if you have some kind of procedure than you want to associate with it
Try()…Catch() block then do something like that

First create a Procedure which contains SQL statements to do a specific task, i made a Store procedure which select all the data from a table which doesn’t present in the database

CREATE PROCEDURE Sp_ErrorTest

AS

    SELECT * FROM NonexistentTable;

GO

then do like this….

BEGIN TRY

    EXECUTE usp_ExampleProc

END TRY

BEGIN CATCH

     EXECUTE Sp_ThrowErrorInfo

END CATCH;

This will show you error,, so in this way you can use Store Procedures in Error Handling

There are many kind of errors which you able to trace with Try()…Catch() statements in T-SQL

The following system functions can be used to obtain information about the error that CATCH block to be executed:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

If these functions are used outside the CATCH block, then all of them will return NULL value.

You can also use Try()..Catch() statement to handle errors that occur inside a transaction, by checking the value of XACT_STATE . The XACT_STATE function determines whether the transaction should be committed or rolled back.

You can use XACT_STATE_ABORT is on, this makes transaction uncommittable when error occurs..

If XACT_STATE =1 , transaction is committable.

If XACT_STATE=-1, the transaction is uncommittable and should be rolled back.

If XACT_STATE=0, means that there is no transaction and a commit or rollback operation would generate an error.

In this way there is a lot more to do in error handling, and there are commands which give you very fine-grained control over small set of errors.

Thanks..

Generating Unique Random Numeric and Alpha Numeric Values

Ashish Gilhotra — Wed, 06 May 2009 09:20:00 GMT

Hi…

In this post , I showed a way to generate 8 characters numeric and alpha numeric values.This can be useful in situations where one has to allocate passwords or unique id to users.

Here is a way in SQL to generate Random, Unique and Reliable Alpha Numeric Values..

SELECT  NEWID()

This always give you unique, random values but as the length of the data as a result of this is too large so its not easy to remember that.

So if you want to Generate 8 characters Numeric or Alpha Numeric values then here is the script for that.

By this script you generate 8 characters Numeric values

SELECT number/nullif(power(10,len(number)-8),0) AS number FROM

    SELECT TOP 1 ABS(CHECKSUM(NEWID())) AS number

    FROM sysobjects s1 cross join sysobjects s2

) AS T

and here is the Script for generating 8 characters Alpha Numeric values .

DECLARE @alpha_numeric VARCHAR(8)

SET @alpha_numeric=''

SELECT @alpha_numeric=@alpha_numeric+CHAR(n) FROM

    SELECT TOP 8 number AS n FROM master..spt_values

    WHERE TYPE='p' and (number between 48 and 57 or number between 65 and 90)

    ORDER BY NEWID()

) AS t

SELECT @alpha_numeric

So in this way you generate 8 characters Numeric and Alpha Numeric values…

You can also change the length of your String by Alter some values in above script.

Thanks…

Technorati Tags: SQL,T-SQL,Random Numbers