Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

August 14, 2015

SQL Server: Use of Recursive Query with example

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them.
 
Recursive query is the efficient way to display hierarchy setup (Parent child relation ship in the same table). e.g. Grand Parent, Parent, Child. This is common requirement. To display the parent child relationship in efficient manner, we can use Recursive CTE. Let me explain it by example.
Create Temporary Table:
DECLARE @Images TABLE (
 [ResourceID] [int] NOT NULL,
 [ChildOf] [int] NOT NULL,
 [ResourceName] [varchar](250) NOT NULL
 )
Lets populate some data into this table:
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (5, 0, N'Mammal')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (6, 5, N'Carnivore')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (14, 6, N'Cannine')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (15, 14, N'Dog')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (16, 15, N'Dog1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (17, 14, N'Monkey')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (18, 17, N'Monkey1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (19, 16, N'dog1.01')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (21, 18, N'monkey1.02')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (23, 17, N'test')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (25, 17, N'Monkey 2')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (31, 0, N'Fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (32, 31, N'cartilaginous fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (33, 32, N'dogfish')
That is how data is stored in the table:
 
 
This is not the way that we can understand the hierarchy. Recursive query will help us to display result like this:
This is much efficient way to list out, as we understand that how the hierarchy is being maintained. Recursive CTE helps us to generate this kind efficient output. Let me share the t-sql script which generates the output which we are looking for:
;WITH images
AS (
 SELECT *
  ,ROW_NUMBER() OVER (
   ORDER BY ResourceID
   ) AS RowID
 FROM @images
 )
 ,cte
AS (
 SELECT 1 AS [Level]
  ,[ResourceID]
  ,[ResourceName]
  ,ChildOf
  ,cast(cast(RowID AS VARBINARY(4)) AS VARBINARY(max)) AS sort
 FROM images
 WHERE ChildOf = 0
 --where [ResourceID] = 5
 
 UNION ALL
 
 SELECT p.[Level] + 1
  ,c.[ResourceID]
  ,c.[ResourceName]
  ,c.ChildOf
  ,cast(p.sort + cast(c.RowID AS VARBINARY(4)) AS VARBINARY(max))
 FROM images c
 INNER JOIN cte p ON p.[ResourceID] = c.ChildOf
  --WHERE c.ChildOF=5
 )
SELECT ResourceID
 ,ChildOf
 ,REPLICATE('.', ([Level] - 1) * 4) + [ResourceName] AS menu
FROM cte
ORDER BY sort
 
That's it. Here, I have used first CTE, to assign the Unique numbers. Then just iterate thru each record and find their related children records. Hope this helps you.
Reference : Tejas Shah http://www.SQLYoga.com

March 31, 2014

SQL SERVER: SSIS - Look Up Transformation Task

Today, I am going to give basic example of Lookup Transformation Task in SSIS.

Lookup transformation performs lookup operation by joining data in input columns with reference table dataset columns.Lookup can be used to access addition information from the reference dataset based on the matching criteria Reference dataset can be OLEDB table, Excel file or cache file, or SQL query result.

Use of Look up Transformation:

In my source system (table), I have all the product with their details. Somehow I have products which belongs to the country which doesn’t exist in my reference (master) table. I assigned a job to rectify those products. I need to design ETL which gives me those records whenever we import products to our target database (table). So here, I am going to use “Lookup no match output” to capture those records by following steps:

Let’s take an example to easily understand how to use Lookup Transformation in SSIS.

1. Create Source Connection:

Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection.

2. Create sample tables

Now we will create tables named ‘LKP_Countries_Source’ and ‘LKP_Countries’ into Test Database from the given script.

  1: CREATE TABLE [LKP_Countries_Source]
  2: (
  3: 	[CountryCode] [int] NULL,
  4: 	[CountryName] [varchar](100) NULL
  5: )
  6: GO 
  7: 
  8: INSERT INTO [LKP_Countries_Source] 
  9: (	[CountryCode]
 10: 	,[CountryName])
 11: VALUES 
 12: 	(91, N'India')
 13: 	,(92, N'Pakistan')
 14: 	,(93, N'Afghanistan')
 15: 	,(94, N'Sri Lanka')
 16: 	,(95, N'Myanmar')
 17: 	,(960, N'Maldives')
 18: 	,(961, N'Lebanon')
 19: 	,(962, N'Jordan')
 20: 	,(963, N'Syrian Arab Republic')
 21: 	,(964, N'Iraq')
 22: 	,(965, N'Kuwait')
 23: 	,(966, N'Saudi Arabia')
 24: 	,(967, N'Yemen')
 25: 	,(968, N'Oman')
 26: 	,(971, N'United Arab Emirates')
 27: 	,(972, N'Israel')
 28: 	,(1, N'USA')
 29: 	,(65, N'Singapore')
 30: GO
  1: CREATE TABLE [LKP_Countries]
  2: (
  3: 	[Country] [varchar](100) NULL
  4: 	,[Code] [int] NULL
  5: )
  6: GO
  7: 
  8: INSERT [LKP_Countries] 
  9: (
 10: 	[Country]
 11: 	,[Code]
 12: ) 
 13: VALUES 
 14: 	(N'INDIA', 91)
 15: 	,(N'SINGAPORE', 65)
 16: 	,(N'USA', 1)
 17: 	,(N'PAKISTAN', 92)
 18: GO
3. Create Lookup connection:

Now you Need to select the proper OLEDB connection in “Connection Manager” tab and the source table for lookup task.

SQLYoga - Lookup Transformation Task #2  
4. Columns Selection from Source Table:

Select the columns to use as output columns.
 
SQLYoga - Lookup Transformation Task #3
5. Lookup Transformation Editor:

Here, I have added “Lookup Data Transformation” Task to designer tab and click on edit to configure the Lookup transformation.
SQLYoga - Lookup Transformation Task #4
6. Handle No Match output:

Now we need to configure various sections in the “Lookup Transformation Editor”.
In General section, select “Redirect rows to no match output” to handle the unmatched data from the lookup task.
 
SQLYoga - Lookup Transformation Task #5

Here we will need to select Cache mode as “Full Cache”. This option is used to improve the performance while handling large scale of data.
Keep connection type as “OLEDB Connection Manager”, as we are using OLEDB source. When you use Cache File as data source then you will need to select “Cache Connection Manager”
Last option provide various ways in which not matched data can be handled.

  • Ignore failure – ignores the failure and executes the next task.
  • Redirect rows to error output – moves the not matched rows to red output to handle them separately.
  • Fail component – throws an exception and stops processing further tasks.
  • Redirect rows to no match output – switches rows to the secondary output, and user can handle it differently to matching data

7. Set Connection Manager for Lookup table:

In Connection section select the reference table with proper connection. This list will get compared with source dataset for matching the data.

SQLYoga - Lookup Transformation Task #6

8. Column mapping for Lookup table:

In Columns section, select the available input columns and map it with the available lookup columns. This will create a join between 2 source datasets.We have used “Full Cache Mode”, so Advanced section will be disabled and in “Error Output” keep fields as it is.SQLYoga - Lookup Transformation Task #7

9. Input Output Selection Setup:

Select new “OLEDB Destination” transformation and drag it to the designer surface. Drag green arrow from “Lookup Transformation” Task to “OLEDB Destination” and provide “Lookup Match Output” as Output and click OK. SQLYoga - Lookup Transformation Task #8

10. Create Output table for Match and Not Matched Data:

Now we will need to create output tables to store the matched as well as not matched result.

  1: CREATE TABLE [LKP_Output_Match] 
  2: (
  3:     [CountryCode] INT,
  4:     [CountryName] VARCHAR(100),
  5:     [Country_Calling_Code] INT
  6: )
  7: GO
  8: 
  9: CREATE TABLE [LKP_Output_NO_Match]
 10: (
 11: 	[CountryCode] INT NULL,
 12: 	[CountryName] VARCHAR(100) NULL,
 13: )
 14: GO
11. Data Mapping for Result table:
Now I need to provide mapping for the Output Table to store the Matched Data.

SQLYoga - Lookup Transformation Task #9
12. Complete Data Flow for Lookup Transformation:

In order to handle the “Not Matched data”, provide the link of Not Matched data to OLEDB Destination table “LKP_Output_No_Match”. It will store the not matched results.

SQLYoga - Lookup Transformation Task #10 
13. Package Execution and Result:

Now let’s execute the Package and check with the output inside the tables we have created to store the result as in
LKP_Output_Match” table for Matched Data and “LKP_Output_NO_Match” table for Not Matched Data.


  1: SELECT * FROM LKP_Output_Match
  2: 
  3: SELECT * FROM LKP_Output_NO_Match
  4: GO
Result :
 
SQLYoga - Lookup Transformation Task #11Lookup Transformation can be used in various ways according to the requirements and can be implemented accordingly. This was just an understanding document for Lookup Transformation.

Reference: Tejas Shah (www.SQLYoga.com)

October 16, 2012

SQL SERVER: T-SQL to find out when database was restored

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database.

We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found:

SELECT    *
FROM    msdb.dbo.RestoreHistory WITH (nolock)
WHERE    destination_database_name = '<Database Name>'
ORDER BY restore_date DESC

SQL Yoga T-SQL to find out when database was restored

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

January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

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

Let me share that problem with all readers.

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

This is very practical problem that we need to fix.

To solve this, I come up with following solution.

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


 SSIS For Each Loop Container II 

2. Configure Derived Column:

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

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

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

SSIS For Each Loop Container II Advanced Editor

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

Let me know your suggestions.


 

October 22, 2009

SQL SERVER: What is SSIS?

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

October 7, 2009

Community TechDays at Ahmedabad - Great Event

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.

VinodKumar
VinodKumar2
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.

Pinal Dave
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.

Jacob Sebastian
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.

Prabhjot Singh
Click with Vinod kumar and Jacob Sebastian:

Vinodkumar TejasShah JacobSebastian

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

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

September 16, 2009

September 15, 2009

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

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.

September 5, 2009

SQL SERVER: Difference between DELETE and TRUNCATE commands

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 )

August 12, 2009

SQL SERVER: Check if Node exists in XML or not






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)

July 31, 2009

SQL SERVER: Use CONTEXT_INFO

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

July 15, 2009

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



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)

May 12, 2009

SQL SERVER: Configure Database Mail with SQL SERVER 2005

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:

Configure Database Mail Step 1

Step 2:

Configure Database Mail Step 2

Step 3:

Configure Database Mail Step 3

Step 4: You might get this message:

Configure Database Mail Step 4

Step 5: Create Profile

Configure Database Mail Step 5

Step 6 : Create Account

Configure Database Mail Step 6

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.


May 4, 2009

SQL SERVER: Read values from Comma Separated variable

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 created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
 
Proposed Solution:
Convert VARCHAR variable to XML as follows:
SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''
So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as: ( as separate table)
image
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 =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

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.

April 20, 2009

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


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:

Get Result Order BY Time regardless Date on DateTime column


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.

April 15, 2009

SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

March 26, 2009

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

March 22, 2009

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151


Let me know it it helps you.