Posts Tagged ‘SQL Tips’

h1

SQL SERVER: Check if Node exists in XML or not

08/12/2009

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)

Advertisements
h1

SQL SERVER: Use CONTEXT_INFO

07/31/2009

Recently I have situation where I have following situation:

I don’t need to execute of trigger’s code, if it is called from particular stored procedure. If data is being updated by from any other places, like application, or any other stored procedures, trigger code should be executed.

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

We can use value stored in CONTEXT_INFO 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.

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

h1

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

07/15/2009
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)
h1

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

03/26/2009

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.

h1

SQL SERVER: How to enable ‘Ad Hoc Distributed Queries’ SQL SERVER 2005

03/19/2009
Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.

We can Enable this feature by two ways:

1. SQL Server Surface Area Configuration.

2. by sp_configure option.

Lets check with first way, by SQL Server Surface Area Configuration.

Open surface Area configuration, you will get this screen:

131

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

132

Lets see second option to enable this feature with sp_configure option:

sp_configure

If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which ‘Ad Hoc Distributed Queries’ is not exist. To see this, we need to enable the ‘show advanced options’ configuration parameter.

You can enable advance options by:

sp_configure ‘show advanced options’,1

When we run this command we will get this message:

“Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”

so we need to execute reconfigure command as:

reconfigure

so now if we run sp_configure again, we will get result set as follows:

133

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:

sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure

so, now if we run sp_configure, we will get result as follows:

134

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.

That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.

Let me know if it helps you in any way.

h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List

Get Comma separated List

I want output like:

Get Comma separated List

Get Comma separated List

I come up with very good solution. Let me share with all of you:

Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

h1

SQL SERVER: REPLICATE function

02/26/2009

Today i read SQLAuthority.com, and I found that one developer
has this issue. Lets see that problem as well as the solution
for the same.

There is one numeric column. User needs to make
sure that all data should be of same size.

We need to display as:

17.00 TO 17.00
2.00 TO 02.00
8.17 TO 08.17

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE (“string that you want TO append” ,”INTEGER
VALUE” )

FIRST parameter, i need TO
SET the CHARACTER, which
will append it BEFORE the NUMBER.

SECOND parameter, how many
times this CHARACTER should be ADD TO the NUMBER.

Example:
DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one “0” to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions