SQL Yoga: July 2009

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)