Tuesday, July 22, 2008

Using XML to Pivot

Here is a simple example that shows how to use XML to pivot data.

--Create Temp Table and Insert some sample data
DECLARE @Table TABLE (tst VARCHAR(10))
INSERT INTO @Table VALUES(1)
INSERT INTO @Table VALUES(2)
INSERT INTO @Table VALUES(3)
INSERT INTO @Table VALUES(4)

--Select the original data set
SELECT * FROM @Table

--Now pivot the data using XML
SELECT STUFF(( SELECT ',' + tst
FROM @Table
FOR XML PATH('')
),
1, 1, '')


Thursday, July 10, 2008

VBScript To Find All SQL Services And Their State

Save the following script as a .vbs file to display the SQL Services and their current state.

strComputer = "."
strServices = ""
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRunningServices = objWMIService.ExecQuery _
("Select * from Win32_Service WHERE DisplayName Like '%sql%'")
For Each objService in colRunningServices
strServices = strServices + objService.DisplayName & VbTab & objService.State & vbcr
Next
Wscript.Echo strServices

Ping All Servers In A File

Create a text file such as C:\ServerList.txt and place one server on each line. Then create a batch file with the following code that references the list.

:: multiIP.bat
@echo off
for /f "tokens=*" %%I in (C:\ServerList.txt) do call :pinger %%I

goto :eof
:pinger
echo %TIME% >> pingLOG.txt
ping %1 >> pingLOG.txt
:: DONE

Monday, July 7, 2008

Dynamic IN Clause using XML (no dynamic sql)

A lot of times people want to pass a variable to a stored procedure that contains a list of delimited values and use it in the IN clause of a query. For example...


DECLARE @String VARCHAR(MAX)

SET @String = '1,2'

SELECT FROM WHERE IN (@String)


The problem is you normally can't do this unless you use dynamic SQL to build the string first and then execute the code like this...


DECLARE @String VARCHAR(MAX)
SET @String = '1,2'
DECLARE @SQL VARCHAR(1000)

SET @SQL = 'SELECT * FROM TableName WHERE ColumnName IN (' + @String + ')'
EXEC (@SQL)


Many companies have policies against dynamic SQL, so you can actually use XML to accomplish the same task.



DECLARE @t TABLE(
col1 INT IDENTITY(1,1),
col2 CHAR(1))

INSERT INTO @t (col2) VALUES ('a')
INSERT INTO @t (col2) VALUES ('b')
INSERT INTO @t (col2) VALUES ('c')

DECLARE @String VARCHAR(MAX)

--This would be the variable passed to a sp for example
SET @String = '1,2'



--THIS WILL PARSE AN XML STRING WITH A COMMA DELMITER
DECLARE @x XML
SET @x = '<j>' + REPLACE( @String, ',', '</j><j>') + '</j>'
--Select @x


--Now you can use the xml to Join on a talbe as in EXAMPLE 1

--EXAMPLE 1
SELECT *
FROM @t t1
INNER JOIN(
SELECT x.j.value('.', 'INT') AS col1
FROM @x.nodes('//j') x(j)
)
AS t2
ON t1.col1 = t2.col1




--Or you can use the xml in an in clause as in EXAMPLE 2
--EXAMPLE 2
SELECT *
FROM @t t1
WHERE col1 IN(
SELECT x.j.value('.', 'INT') AS col1
FROM @x.nodes('//j') x(j)
)