SQLYoga

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

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

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

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:

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)

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 26, 2009

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

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.

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.

April 14, 2009

SQL SERVER: SQL Query To Find Most used Tables

We have very large database and today we want to search the tables which are used mostly.

Means tables which are used in Procedures, Constraints, Views, Triggers etc.

I know this is very strange requirement, but we need to do this.

So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned

Let me share that sp with all of you:

SELECT TableName, COUNT(*) 
FROM ( 
Select Distinct 
o.Name 'TableName', 
op.Name 'DependentObject' 
From SysObjects o 
INNER Join SysDepends d ON d.DepId = o.Id 
INNER Join SysObjects op on op.Id = d.Id 
Where o.XType = 'U' 
Group by o.Name, o.Id, op.Name 
) x 
GROUP BY TableName 
ORDER BY 2 desc

So, I made my life easy, by using this. I can get the list if Tables which are used most.

Let me know if it helps you in any way.

April 2, 2009

SQL SEVER: How to make an Entry in Event Viewer using SQL SERVER

Today I came across the situation where I need to following scenario.

I need to make entry in Event Viewer when there is an error in Stored Procedure.

I wondered to do this, but finally I come up with the solution. SQL is much powerful.

SQL provides us to make an entry in Event Viewer by two ways:
1. using XP_LogEvent

2. By Raiserror WITH LOG

Lets first see the way using XP_LogEvent:

Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT 10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

EXEC xp_logevent 60000, @msg, informational

END CATCH

Lets Execute this SP:

EXEC TestEventViewer

This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer.

You will get en entry of Error there.

So we can do this by Extended Stored Procedure: “xp_logevent”.

Let see the parameters of this Procedure.

First Parameter: “60000” is the Error Number

Second Parameter: “@msg” is the message to be displayed in Event Viewer.

Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”.

Now, Lets see by another way By Raiserror WITH LOG:

It is the same way as we used Raiserror to Raise an Error.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT 10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

RAISERROR(@msg, 11, 1) WITH LOG

END CATCH

Lets Execute this SP:

EXEC TestEventViewer.

So By these ways we can make an entry to Event Viewer.

Let me know if it helps you in any way