SQL Yoga

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

2010-03-07T21:04:00.001+05:30

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:

EXEC SP_PROCOPTION     
@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 (http://www.SQLYoga.com)

SQL SERVER: SSIS - Derived Column Data Flow Transformation

2010-01-10T10:55:00.000+05:30

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.

2. Configure Derived Column:

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:

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

Let me know your suggestions.

SQL SERVER: Presentation at Ahmedabad User Group Meeting

2009-12-20T17:56:00.000+05:30

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.

This user group is running by Jacob Sebastian, President of PASS regional committee for the Asia and Middle East region.

Last Saturday, I get a chance to present a session with Jacob Sebastian (SQL SERVER MVP) and Pinalkumar Dave (SQL SERVER MVP).

I am member of this User Group from last 1.5 years. and I found that it such a great event to learn something new from great leaders.

If you need PowerPoint Presentation and Demo script, send me an email at: tejasnshah.it@gmail.com.

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

Tejas Shah (me)

Tejas Shah (Me), Presenting session

Pinalkumar Dave (SQL SERVER MVP), Presenting session

User Group Members with President, Jacob Sebastian, Pinalkumar Dave

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

SQL SERVER: SSIS - Transfer Jobs Task

2009-12-07T10:00:00.000+05:30

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

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

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

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

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

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

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

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

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

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

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

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

TRUE: Which copy all jobs

FALSE: Which copy only selected jobs.

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

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

This property has the options listed in the following table:

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

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

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

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

TRUE: Enable jobs on destination server.

FALSE: Disable jobs on destination server.

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

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

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

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

SQL SERVER: How to Read Excel file by TSQL

2009-12-03T10:00:00.000+05:30

Many times developers asked that, they want to import data from Excel file.

We can do this by many ways with SQL SERVER.

1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL

Today, I am going to explain, How to import data from Excel file by TSQL.

To import Excel file by TSQL, we need to do following:

1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.

2. Write following TSQL, to read data from excel file

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

NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file

If you want to insert excel data into table,

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

That's it.

SQL SERVER: How to remove cursor

2009-12-02T10:00:00.000+05:30

Many times developer ask me that How can they remove Cursor?

They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same.

Please find this code to remove cursor with Table variable:

--declare table to keep records to be processed
DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))
--populate table variable with data that we want to process
INSERT INTO @Table(Column1, Column2)
SELECT    Column1, Column2
FROM    <Table>
WHERE    <Conditions>
--declare variables to process each record
DECLARE @inc INT, @cnt INT
--Assign increment counter
SELECT @inc = 1
--Get Number of records to be processed
SELECT @cnt = COUNT(*)
FROM @Table
WHILE @inc <= @cnt BEGIN
    --As we have AutoID declared as IDENTITY, it always get only one record.
    --Get values in Variable and process it as you want.
    SELECT    @Column1 = Column1,
            @Column2 = Column2
    FROM    @Table
    WHERE    AutoID = @inc        
    --do your calculation here
    ....
    ....
         
     --Select next record
     SET @inc = @inc = 1 
END

By this way, we can remove CURSOR by Table variable.

It is quite easy to implement.

One more benefit is: It will process one record at a time, so it locks only that record at a time.

Let me know if you have any questions.

SQL SERVER SSIS: How to assign Connection from variable

2009-11-28T05:04:00.000+05:30

Last Article, SSIS - Foreach Loop Container, We need to assign dynamic connection to file connection, so SSIS For each loop Task can take each file from folder.

Lets configure File connection from variable for SSIS - Foreach Loop Container.

What we need to do is, we need to process each file from folder, so we need to assign value from variable to File connection, so SSIS Task will read that file and process that file.

To assign FileConnection dynamicaly we need to do following.

1. Right click on File Connection, click Properties.

2. Set DelayValidation = "False", as we need to assign connection dynamically.

3. Click on "Expression", and enter variable name, which we used in SSIS - Foreach Loop Container.

That's it.

It will assign connection from variable and process that file.

Let me know if you have any question for the same.

SQL SERVER: SSIS - Foreach Loop Container

2009-11-09T08:53:00.000+05:30

Today, I am going to explain SSIS For Each Loop Task. I am sure that it will be easy to configure SSIS Package with Foreach Loop task, after you read this article.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Many times, user asks that I want to process all files from my folder to database. This is my answer to all of those users. SSIS comes up with For Each Loop which did the same.

Now I'm assuming that you're familiar with using the SQL Dev Studio tools and building basic packages. If this isn't the case, I recommend working your way through the Integration Services using my earlier posts.

1. SSIS Foreach Loop Container - General : Here we need to assign unique name to this container and also we can specify brief description, so we will get idea why we need to design this container.

2. SSIS Foreach Loop Container - Collection : Use the Collection page of the Foreach Loop Editor dialog box to specify the enumerator type and configure the enumerator.

Select the enumerator type from the list. This property has the options listed as follows:

Foreach File Enumerator: Enumerate files
Foreach Item Enumerator: Enumerate values in an item
Foreach ADO Enumerator: Enumerate tables or rows in tables
Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
Foreach From Variable Enumerator: Enumerate the value in a variable
Foreach Nodelist Enumerator: Enumerate nodes in an XML document
Foreach SMO Enumerator: Enumerate a SMO object

Let me explain with Foreach File Enumerator

We can use this Foreach File enumerator to enumerate files in a folder.

The content of the folders and subfolders that the Foreach File enumerator enumerates might change while the loop is executing because external processes or tasks in the loop add, rename, or delete files while the loop is executing. This means that a number of unexpected situations may occur:

In Folder option, provide the path of the root folder to enumerate. In Files option, specify the files to enumerate.

If wildcard characters are specified in the Files option, then the fully-qualified paths that are returned match the filter.

Fully qualified: Select to retrieve the fully qualified path of file names.
Name and extension: Select to retrieve the file names and their file name extensions.
Name only: Select to retrieve only the file names.

3. SSIS Foreach Loop Container - Variable Mappings : Use the Variables Mappings page of the Foreach Loop Editor dialog box to map variables to the collection value. The value of the variable is updated with the collection values on each iteration of the loop.

Variable: Select an existing variable, or click <New variable...> to create a new variable.

Index: If using the Foreach Item enumerator, specify the index of the column in the collection value to map to the variable. For other enumerator types, the index is read-only.

Once we configured, For Each Loop container, Lets add the process that we want. Here what I am going to do is:

1. Data Flow Task.

NOTE: We can add any other tasks to this container. I took Data flow process, as its already explained by this BLOG.

Here lets see the logical scenario of For Each Container Task.

First, From the selected folder, fetch all the files and assign file path to the variable.

Second, For Data Flow Process, process the file from the folder and execute data flow.

NOTE: For Data Flow Process, we need to assign Connection from variable. ( as we assigned variable after each loop, we need to process new file from the folder). So How can we configure File connection to use new file?

Please refer to my next article for How to assign Connection from variable.

That's it. This is what we need to design for "For Each Loop".

Let me know if there is any difficulties you have to design this package.

SQL SERVER SSIS - For Loop Container

2009-11-02T01:15:00.000+05:30

Today, I am going to explain SQL SERVER SSIS, For Loop Container.

The For Loop container defines a repeating control flow in a package. The loop implementation is the same concept of the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop container uses the following elements to define the loop:

An optional initialization expression that assigns values to the loop counters.
An evaluation expression that contains the expression used to test whether the loop should stop or continue.
An optional iteration expression that increments or decrements the loop counter.

Let's take an example to easily understand how to use For Loop Container with SSIS. Here I take example to iterate ActiveX Task.

1. Select and Drag, For Loop Container, from Container Flow Items to designer surface and add ActiveX Script Task to run inside the loop.

2. To configure this container, right click on this and click on 'edit'.

First we need to create variables to run this package based on variables. We can create variables by: View -> Other Windows -> Variables. Please find variable screen as below:

Here, I specified both variables. Count and Increment, that I am going to use for this example. I specified value Count = 20. So loop will be executed 20 times.

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

For Loop Properties:

1. InitExpression: Type an Initialization Expression in the given textbox. Initialization ensures that we are starting by setting out increment counter to 1. Here I specified variable to 1.

2.EvalExpression: Type an Evaluation Expression in the given textbox. For each iteration the evaluation expression checks to see if we have reached our maximum iteration count as set above when we defined @Counter. Here I specified that @Increment <= @Count, code inside the for loop will execute @Count times.

3. AssignExpression: Type an Assignment Expression in the given textbox. This is used to increment the counter by one for each iteration of the loop, otherwise the loop would never finish. Here I specified to increment variable by 1.

That's it. We have configured SSIS For Loop container.

Now when we execute this package, it will execute ActiveX Task, 20 times (as specified in variable count).

Let me know if you have any questions.

SQL SERVER: SSIS - ActiveX Task

2009-10-31T00:01:00.000+05:30

Today I am going to explain SSIS - ActiveX Task.

Here I am just explain that how to write VB Script in this task, which will execute at run time. This ActiveX Task is mostly used when we move SQL 2000 DTS package to SSIS. As I found that many developers had used ActiveX with DTS packages in SQL SERVER 2000.

The ActiveX Script task provides a way to continue to use custom code that was developed using ActiveX script, until such scripts can be upgraded to use the more advanced features provided by the Script task.

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

1. Select and Drag, ActiveX Script Task, from Container Flow Items to designer surface.

2. Now we need to write a script which will execute when SSIS package loads. To write a script, Right click on ActiveX Script Task, which we dragged to Design surface. Click on "Edit.", you will get page as:

3. SSIS ActiveX Script 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 design this task.

4. SSIS ActiveX Script Task - Script Task: This is main section where we need to write script.

Language: We need to specify which script language we are going to use for this task, either VB Script, JScript etc..Here we are going to use VB Script language for this demo

Script: Here we need to write a script. Click on ".." button located at right side. It will open a dialog to write a script, as shown in figure:

Let's write VB Script code inside to test this task. I write this script:

function Main()

Dim Stuff, myFSO, WriteStuff, dateStamp
dateStamp = Date()

Stuff = "SSIS Text ActiveX Task"

Set myFSO = CreateObject("Scripting.FileSystemObject")
Set WriteStuff=myFSO.OpenTextFile("D:\SQLYoga\SSIS ActiveX Task.txt", 8, True)
WriteStuff.WriteLine(Stuff)
WriteStuff.Close
SET WriteStuff = NOTHING
SET myFSO = NOTHING

End function

Save it and click on Parse, which will parse this script and let us know if there is any problem in this script. If It is on then its allowed to proceed with the next step.

Expression: Use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes.

That's it.

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

Once you run it and if logged in user has write access to the given path, SSIS package will create file at that place. Now go to that given path and see file is created or appended with content that we want to write.

That's simple to configure and use SSIS ActiveX Script Task within BIDS.

Let me know if you have any questions in designing SSIS Package

SQL SERVER SSIS: BULK INSERT Task

2009-10-29T00:17:00.000+05:30

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK.

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way.

We can move data to SQL SERVER Table or View by BULK INSERT Task.

Let see How to configure SSIS BULK INSERT Task:

1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface:

2. Configure connection: Right click on Task and click on Edit

Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted.

NOTE: The Bulk Insert task supports only OLE DB connections for the destination database.

Define Format: We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement.

Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step.

3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers.

That's it. Now execute the package, you find that your Table/view is populated with data from provided text file.

Why we have both BULK INSERT AND DATA FLOW TASK ?
We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable.

The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.

With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import.

So, we can use either of these as per our requirements.

Let me know if you have any questions.

Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)

SQL SERVER SSIS: Basic Example Of Data Flow Task

2009-10-24T00:00:00.000+05:30

Today I am going to give Basic example of Data Flow Task in SSIS, import CSV file to SQL SERVER Database

Many times user asked to import their data from their files. They might have data in Microsoft Access, Microsoft Excel, CSV File, Text Tile etc..

Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That is the power of SSIS.

1. Drag Control: Drag Data Flow Task from Toolbox, to Design interface

2. Configure Data Flow Task: Once you have Data Flow Task on Design surface, Double click on it, or Right click on task and click on "Edit", it will redirect you to Data Flow Tab, as shown in Figure 2:

As shown in above figure, SQL SERVER SSIS give features to import/export data from/to many formats. You can find the different formats that is supported by SSIS to import data on LEFT PANE.

3. Configure Source Connection: Here, I have selected "Flat File Source", as we need to import data from CSV File. Drag "Flat File Source" to design surface and right click on edit and select "Edit", you will get screen like:

As shown in figure, Click on "New", It will open a form which allows us to select file which we want to import to our Database.

First, Enter connection manager name and description.
Then, select a file which you want to import to database.
Then, Select Locale and Code page as per your requirement, For this demo, I don't need to change anything.
Then, we need to select Format of the file. SSIS provide us three formats: Delimited, Fixed Width, Ragged Right. We need to import CSV file (Comma Separated file), so I have selected "Delimited".
Then, We need to specify Text Qualifier, Select based upon your requirements.
Then, we need to specify delimiter for Header row, as we have CSV file, I have selected CR LF, means new row for header.
Then, SSIS also give us features to skip rows. By Header Rows to skip, it allows us to skip n number of rows.
At Last, If there is Header in column names, we just need to select this checkbox, "Column names in the data row". So SSIS understands to set the first row as Header row.

That's it for configure file, Now we need to specify delimiter for column, which comes on next section called "Columns"

As shown in figure, Now we need to specify delimiter for Rows and Columns.
As we are going to import CSV file (comma separated file), Row Delimiter is "{CR}{LF}" and column delimiter is ",".

NOTE: Also notice that, it display Column Names in Header, as we have checked, "Column Names in the First data row".

This is the data that we are going to import to SQL Database.

You can use Advanced and Preview tab, to configure each column and preview, respectively.

Now once, configuration is done, we need to select OLE DB Destination, as we need to import it to SQL SERVER database.

4. Configure Destination Connection: Here, I have selected "OLE DB Destination", as we need to import data from CSV File to SQL SERVER Database. Drag "OLE DB Destination" to design surface.

Now, we need to design data flow. Here we need to import data from Flat File connection, I have dragged "Success" (Green Arrow), link to OLE Db Destination for column Mapping.

Now, we need to configure OLE DB destination, to insert data to SQL Database. Right click on it and click on "EDIT", it will open screen like this:

I have entered SQL server Login information to connect to SQL SERVER where I need to import data from CSV file.

Once, SQL SERVER is configured, we need to select Table Name to which we need to insert data from CSV file. See the following screen, I have selected "Employee" Table: Please find script to create "Employee Table"

CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [varchar](max) NULL,
    [EmployeeCode] [varchar](max) NULL,
    [EmployeeDesignation] [varchar](max) NULL,
    [CreatedDate] [datetime] DEFAULT GETDATE()
)

Now we have selected SQL SERVER table to which we need to import data, Now we need to map columns names, so SSIS will insert data accordingly.

By default, SSIS provides mapping for columns which have same name, In this case Column Mapping is ok, so we don't need to do anything. If you want to change mapping, you can do it by just changing links between Input Columns and Destination Columns.

NOTE: Here Destination table has two more columns, EmpID and CreatedDate, which is not available in source file, CSV file. We can just ignore it, as EmpID is Identity column while CreatedDate is GETDATE(). We have such columns in SQL database to keep track when this record is added.

That's it. We have configured Data Flow Task.

Now just right click on package and "Execute Package". You will find that Employee table will be populated with information from CSV file.

Let me know if you have any questions in designing SSIS Package.

SQL SERVER: What is SSIS?

2009-10-22T00:00:00.000+05:30

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server

SSIS (SQL Server Integration Services) is introduced in SQL SERVER 2005. It is totally new transformation of data. Previously we have DTS package in SQL SERVER 2000. Now they introduced SSIS with many features.

SSIS is an extensive data integration platform which is used to transfer and merge the information from various sources and load to multiple systems. It contains wide range of capabilities.

Business Intelligence is used for decision making, future trends based on collected data. I can find very good article written by Microsoft SQL SERVER MVP, Pinalkumar Dave. He write about BI on his one of the best article: http://blog.sqlauthority.com/2009/10/21/sql-server-introduction-to-business-intelligence-important-terms-definitions/.

SSIS packages can be created in BIDS (Business Intelligence Development Studio).

I am going to explain all features of SSIS for easy and better understanding by following articles.

Reference: Tejas Shah (http://www.SQLYoga.com )

Top features of Windows 7 and Office 2010

2009-10-08T23:08:00.000+05:30

We have Community TechDays on 3rd October 2009, Saturday at Hotel Anmol, Ahmedabad. This was excellent event, you can find out more details here.

I like these features of Windows 7 and Office 2010 which was presented by Vinod kumar, Microsoft Evangelist.

1. I like the Recorder Tool provided by Windows 7. This is very handy tool which will make our (IT professional) life easy. Many times we find that users complaint that application raise an error. What we need is, what user did so error occurs. By this tool, user can record all of the steps. This tool will capture all screen shots and description what user did. So user just need to send this file to us and we can easily come to know that why this happen. One more thing is that, File size is also not much though all screen shots are there. We can also have option to reduce size, by reducing quality of images.

2. I like the option of Jump List, We will access applications like windows media player just from Task Bar. We don't need to even open that Application. This is very nice feature as everything is just from task bar.

3. There is a feature to Magnify the screen and also point out the location. This we really need when we are giving presentation or in meetings. By this feature we can present things in easy way so all persons can understand easily.

4. Feature: Trigger Start Service. This is really good feature, as previously it was programmed as System keeps check periodically about the new hardware is attached or not. That was really resource consuming. Microsoft resolved this in Windows 7. As Vinod Kumar explained with USB drive at Community TechDays, Event is only fired when we attach any new hardware else it is not in even memory.

5. Now we can also Re-Arrange open applications in Task manager. As we used Tabbed browser and like to move important tabs first. This is the same concept developed in Windows 7 to arrange icons in Task Manager.

6. Show Desktop feature, Windows 7 introduced the "Show Desktop" button at right bottom side. We just need to click on right bottom which will minimize all applications which will make developers life simple :)

7. Windows 7 also comes with good display options. We can customize the color of Taskbar icon's color.

8. Virtulization, The most common reason is that you want to run Virtual PC on Windows 7 on a system that does not have hardware virtualization support.

9. In Office 2010, feature to notify icon which indicates that recipient is available or not at that time. By this feature, at the time of sending an email come to know that recipient is available or not.

10. Also in Office 2010, Grouping follow up mails. This is feature by which we can find whole list mails, received and sent. So we come to know about the discussion from first email to last email.

11. In Office 2010, You can not copy the copyright contents. I can not get correct name to explain this.

There are many more featured explained by Vinod Kumar @ Community TechDays. Here I just mentioned features those are still on my mind and I like this in real life.

Reference: Tejas Shah (http://www.SQLYoga.Com)

Community TechDays at Ahmedabad - Great Event

2009-10-07T11:33:00.000+05:30

There was a great event held by Microsoft and PASS (Professional Association of SQL Server) in Ahmedabad on 3rd October 2009. This was very big event where 250+ attendees attend the excellent session of Vinod Kumar, Pinal Dave, Jacob Sebastian and Prabhjot Singh Bakshi. There were Four technical sessions by these excellent speakers.

First session is of Vinod Kumar. He is Microsoft Evangelist. There was excellent session of Three hours where he give features of Windows 7 and Office 2010.

Second session is of Pinal Dave, SQL MVP. He presented session on the subject - "SQL Server - The Other Side of Index". This was also excellent way to know more about SQL Index. and How to use index. That is really nice session for SQL Developers to know, How to optimize query. It gives proper understanding of indexes.

Third session is of Jacob Sebastian, SQL MVP. He presented session on '"The Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server". This was also very great session where developer can get more idea about When exception occur and how to handle that exception. It was presented in a way so all attendees get clear idea about it and understand it properly.

Forth and last session is of Prabhjot Singh Bakshi, MCT(Microsoft Certified Trainer). He presented session on "NET Framework 4.0". He also presented very nice session about featured of .Net Framework 4.0. He developed .Net code to give clear understanding of new features.

Click with Vinod kumar and Jacob Sebastian:

Overall, that was excellent day for Ahmedabad. I hope this type of TechDays scheduled in near future.

Reference: Tejas Shah ( http://www.SQLYoga.com)

SQL SERVER: Agenda of Community TechDays at Ahmedabad

2009-09-16T12:25:00.000+05:30

SQL SERVER: Community Tech Days in our City at Ahmedabad on 3rd October 2009

2009-09-15T15:22:00.000+05:30

Microsoft Community Tech Days are in 11 cities in INDIA with 19 insightful Technical Sessions.

These insightful Technical sessions are available in our city "Ahmedabad", Gujarat on 3rd October 2009.

So book your calendar for this day and be a part of this TechDays.

Limited seats are available , so please register yourself with this event:

Let me know if you have any problem in registrations.

SQL SERVER: Difference between DELETE and TRUNCATE commands

2009-09-05T12:30:00.000+05:30

We interviewed many people my company as recruitments are going on for developers. When I asked this SQL SERVER question to person "What is difference between DELETE and TRUNCATE in SQL SERVER?".

I got the following answers from most of them are, which are incorrect:

1. I can not use WHERE condition with TRUNCATE command
2. I can not use TRUNCATE command if foreign key is there on table.
3. TRUNCATE is faster than the DELETE, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files. etc..

These answers are correct. I also got this answer, which is Incorrect:

"DELETE can be rolled back while TRUNCATE can not be rolled back"

I asked them what does it mean?, give me an example. See what they say, if I have Transaction and if I have used DELETE then ROLLBACK will let them back to original state. In case of TRUNCATE within Transaction, will not allow me to original state.

This is incorrect. We can ROLLBACK changes made by DELETE and TRUNCATE if the it is used in Transaction. Lets see this in detail by example:

Create one table table and insert some data in it like:

CREATE Test TABLE(
ID INT IDENTITY
Text VARCHAR(5)
)
GO
INSERT INTO Test(Text) VALUES('A')
INSERT INTO Test(Text) VALUES('B')
INSERT INTO Test(Text) VALUES('C')
INSERT INTO Test(Text) VALUES('D')
GO

Lets try to use TRUNCATE within Transaction as follows:

BEGIN TRAN --Start Transaction
TRUNCATE TABLE Test --Use this command
--Check the data from table. See transaction is still in progress not commit/Rollback yet
--There is no row in table, as all rows were deleted from above TRUNCATE statement
SELECT * FROM Test 
ROLLBACK --Rollback this Transaction
--Lets see now what values should be there in table after ROLLBACK
SELECT * FROM Test

So, we found that we can found all values in the table. We can ROLLBACK the TRUNCATE command as same as other commands.
This is place where most of the developers can not give accurate answer. I hope by this article they can have clear idea.

Let me know if there is any confusion.

CONCLUSION:

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Reference: Tejas Shah( http://www.SQLYoga.com )

SQL SERVER: Check if Node exists in XML or not

2009-08-12T12:50:00.000+05:30

Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
  <BulkData>
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
  </BulkData>
</Data>'

Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')

This will return "1" if node is exists else return "0".

That's it. I can write based on the return result by this statement.

Let me know if it helps you.

Reference : Tejas Shah(http://www.SQLYoga.com)

SQL SERVER: Use CONTEXT_INFO

2009-07-31T12:24:00.000+05:30

Recently I have following situation:

I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so.

I search and I got the option CONTEXT_INFO with SQL SERVER. Let me share this with all of you.

NOTE: We can use CONTEXT_INFO value in that session only. If there is new session we cannot use that value stored in CONTEXT_INFO.

We can set CONTEXT_INFO up to 128 bytes of binary information with the current session or connection. Reference: http://msdn.microsoft.com/en-us/library/ms180125.aspx

Let see an example, for better understanding:

CREATE TABLE tblA(
ID INT IDENTITY,
ColVal VARCHAR(100)
)
Let's create two SPs, which will insert a record into tblA, which is just created

CREATE PROC TestA
AS
BEGIN 
    INSERT INTO tblA(ColVal)
    SELECT 'Allow To insert'
END

CREATE PROC TestB
AS
BEGIN 

    DECLARE @UID VARBINARY(128)
    SELECT @UID = CAST('TestB' AS VARBINARY(128))
    SET CONTEXT_INFO 0x5465737442
    
    
    INSERT INTO tblA(ColVal)
    SELECT 'Not Allow To insert'

END

In this example, when we execute SP: TestA, it should allow to insert record in the table, while we execute SP: TestB, it should restrict. Here I set CONTEXT_INFO with SPName. I can use the same whatever is stored in CONTEXT_INFO during the current session.

To make validation as defined above, lets create one trigger to restrict/allow user to proceed.

CREATE TRIGGER trg_TblA
   ON  tblA
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Message varbinary(128)
    SELECT @Message = cast('TestB' as varbinary(128))
    
    IF    @Message = CONTEXT_INFO() BEGIN
        RAISERROR('Not Allowed to Insert/Update/Delete from SP: TestB',15,1)
        ROLLBACK TRAN
    END
        
END
GO

In Trigger, as you can see, I again converted the SP name and compare it with CONTEXT_INFO.

When Stored Procedure TestA is being executed, CONTEXT_INFO is not set, so trigger will not find comparison and allows user to insert record.

While we execute TestB, as we have set CONTEXT_INFO, system will find comparison in trigger and restrict us to complete transaction. So system will give error message:

So, by this way we can validate value in trigger, to make such decision

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted

2009-07-15T18:47:00.000+05:30

We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table.

I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you.

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 use this SP:

EXEC SP_PROCOPTION 
    @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, lets take an example.

I have one Database called Test, I have created setup table:

CREATE TABLE SetupTable(
Seq INT IDENTITY,
Code VARCHAR(100)
)

Lets insert some default values to this table:

INSERT INTO SetupTable VALUES('A')
INSERT INTO SetupTable VALUES('B')
INSERT INTO SetupTable VALUES('C')
INSERT INTO SetupTable VALUES('D')

What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named,

CREATE PROC ClearAllData
AS

DELETE
FROM    Test.dbo.SetupTable

INSERT INTO SetupTable VALUES('X')
INSERT INTO SetupTable VALUES('Y')
INSERT INTO SetupTable VALUES('Z')

and set up this stored procedure as auto executed every time when SQL SERVER is started as:

EXEC SP_PROCOPTION 
    @ProcName = 'ClearAllData',
    @OptionName = 'startup',
    @OptionValue = 'true'

Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically.

If now you want to stop it to execute automatically, we just need to execute this with “false” as:

EXEC SP_PROCOPTION 
    @ProcName = 'ClearAllData',
    @OptionName = 'startup',
    @OptionValue = 'false'

I hope this is very clear to use this feature.

Reference : Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Configure Database Mail with SQL SERVER 2005

2009-05-12T10:42:00.000+05:30

We used Database mail to send mail to client on each updates.

This is a very simple process to configure. Let me share how to configure Database mail with sql server 2005 with all of you.

After setting up Profile and Account properly, you just need to write following code to send a mail to client:

Step 1:

Step 2:

Step 3:

Step 4: You might get this message:

Step 5: Create Profile

Step 6 : Create Account

That's it.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html

Let me know if you have any complexity or comments in setting up Database mail.

SQL SERVER: Read values from Comma Separated variable

2009-05-04T17:45:00.000+05:30

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column.

Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this.

Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

Example:

I have one procedure which has one parameter VARCHAR(100), which might contains value like '1,5,6,20'. What I need to do is: I need to update rows contains these ID(1,5,6,20). So We need to make query which will update status of these IDs.

Solution:

I converted this VARCHAR Variable to XML by following way:

SET @xmlIDs =    '<IDs>
                    <ID>' + REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
                '</IDs>'

So this statement will generate XML from VARCHAR value as follows:

<IDs>
  <ID>1</ID>
  <ID>6</ID>
  <ID>7</ID>
  <ID>8</ID>
  <ID>20</ID>
</IDs>

So, Now this is the XML, which can be easily read with SQL SERVER 2005 as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '<IDs>
                    <ID>' + REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
                '</IDs>'

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )

So, it is very easy to read values from Comma separated value.

Let me know if it helps you.

SQL SERVER - Query to compare number of Rows between different Databases

2009-04-26T12:23:00.000+05:30

We have two databases in the same SQL server instance. Both of the databases are copy of the production database, so both contains same tables. We added some records in Test Database’s some tables to update some features. We have added data in many tables of test database. Now we need to also update Production DB with the updated data. To update Production Database, we need to make sure in which tables we have updated data and then we will check and update production database accordingly. We have many numbers of tables, and we have updated many tables, so its not possible for us to check it manually.

So, we need to make query to compare rows of each table with the another database tables, to find out which tables has different rows then the original database.

Solution:

As I need to solve this problem, I write a query which will give me Rows of each table in one Database. As I need to compare it with another database I write the following query to come out with the solution.
Example:

I have two databases. Database A and Database B.

I need to make a report, which will give me details of each table and rows.

I made this Stored Procedure in Database A.

CREATE PROC CompareRowsBetweenDatabas
AS
WITH A AS(
SELECT 
    sch.name AS SchemaName,
    st.Name AS TableName,
    SUM(  
        CASE  
            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
            ELSE 0  
        END  
       ) AS Rows
     FROM sys.partitions p
     INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
     INNER JOIN sys.tables st ON st.object_id = p.Object_ID
     INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
     GROUP BY st.name, sch.name

),
b as(
SELECT 
    sch.name AS SchemaName,
    st.Name AS TableName,
    SUM(  
        CASE  
            WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
            ELSE 0  
        END  
       ) AS Rows
     FROM B.sys.partitions p
     INNER JOIN B.sys.allocation_units a ON p.partition_id = a.container_id
     INNER JOIN B.sys.tables st ON st.object_id = p.Object_ID
     INNER JOIN B.sys.schemas sch ON sch.schema_id = st.schema_id
     GROUP BY st.name, sch.name
)
SELECT    a.SchemaName, a.TableName, a.Rows,
        b.SchemaName As BSchemaName, b.TableName AS BTableName, B.Rows AS BRows
FROM a 
INNER JOIN b ON a.TableName = b.TableName
    AND a.SchemaName = b.SchemaName

EXEC CompareRowsBetweenDatabase

This will give me results as I need.

Let me know if it helps you.

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column

2009-04-20T15:39:00.000+05:30

Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.

Let's see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)
INSERT INTO @Data(dt) 
SELECT '2008-12-05 04:00:00.000' 
UNION ALL 
SELECT '2008-12-10 10:00:00.000' 
UNION ALL 
SELECT '2009-03-01 08:00:00.000' 
UNION ALL 
SELECT '2009-03-02 07:15:00.000' 
UNION ALL 
SELECT '2009-03-10 08:50:00.000' 
UNION ALL 
SELECT '2008-12-31 23:00:00.000' 
UNION ALL 
SELECT '2009-05-01 21:10:00.000'

SELECT * FROM @Data

Now we need output like this:

I found very quick solution for this. You can create query as follows:

SELECT * 
FROM @Data 
ORDER BY Convert(VARCHAR, dt,108)

Let me know if it helps you in any way.