Thursday, November 6, 2008

Columbus GA SQL Server Users Group Images

I am in the process of starting a users group in Columbus GA. I was playing around with a few images for the website and here is what I came up with. I am not sure which one I like better. I like the one with the database from a pure techie perspective, but I think the one with a star looks a little better. The actual quality of the images look better on my pc. I think the compression format changed when I uploaded them.


SQL .Net

When I started playing with Compound Operators and the new ability to assign a value in the variable declaration it brought me back to the days when I first started learning how to program in .Net. Honestly the first thing I wanted to type out of habit was .ToString(). Okay .ToString() may be a little far fetched, but there is some syntax in .Net that would make a some SQL code a lot cleaner. Take the following example.

Declare @i int = 1
Set @i += 1
Print @i.ToVarchar(4)

I guess Intellisence made me want to be able to do this even more. When I typed a dot after variable name, I almost expected a list of available methods.

The next thing I tried was #Region/#End Region to create code blocks. This does not work, but begin/end will create a region and keep the comments in the header.

Begin --Test Region 1
--Code Here
End

Then I tried adding --TODO: in a comment block to see if it would show up in the task list. No luck yet, but I am still playing with this one.

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)
)


Thursday, June 26, 2008

Disable/Enable All Constraints In A Database

Method 1

--Disable
DECLARE @SQL VARCHAR(MAX)
Select @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL;'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
EXEC(@SQL)

--Enable
DECLARE @SQL VARCHAR(MAX)
Select @SQL = ISNULL(@SQL,'') + 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL;'
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
EXEC(@SQL)

Method 2

--Disable
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Enable
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Wednesday, June 25, 2008

Select Into Exec - Create Table From Stored Procedure Results

Most of the time you have to create a temp table and then use the Insert Into Exec statement to load the table. By using Openrowset, you can perform the equivalant of an Exec Into statement to create the table on the fly. In this case, even if you did create the temp table and tried to do the Insert Into Exec for sp_help_job, you would get an error saying that you could not have nested Insert Into Exec statements. This does have limitations though. If you try this with sp_who2, you will get an error saying that you cannot have a duplicate column name spid, becuase sp_who2 returns the spid column twice.

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes' ,

'set fmtonly off exec msdb.dbo.sp_help_job')

SELECT * FROM #JobInfo

Tuesday, June 24, 2008

Currently Running Jobs

DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'

Select B.Name, A.Current_Step
from @xp_results A
Join sysjobs B
On A.Job_ID = B.Job_ID
where Running = 1

Monday, June 23, 2008

Import XML Data from File

--Create the table
CREATE TABLE [dbo].[XMLFeeds]( [XMLFeedID] [bigint] IDENTITY(1,1) NOT NULL, [TestXML] [xml] NOT NULL)

--Load the file
INSERT INTO XMLFeeds (TestXML)
SELECT TestXML
FROM ( SELECT * FROM OPENROWSET
(BULK 'C:\Test\Test.xml', SINGLE_CLOB) AS xmlData ) AS feed (TestXML)


--Now you can parse the file using xquery

Sunday, June 22, 2008

Script to Create SQL Agent Run Logs

--This is for creating run logs for servers.
--This requires the creation of a User Defined Function in the msdb Database called dbo.schedule_description
--1. Create the function.
CREATE FUNCTION [dbo].[schedule_description] (@freq_type INT ,
@freq_interval INT ,
@freq_subday_type INT ,
@freq_subday_interval INT ,
@freq_relative_interval INT ,
@freq_recurrence_factor INT ,
@active_start_date INT ,
@active_end_date INT,
@active_start_time INT ,
@active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
declare @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT
IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7) BEGIN IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1)) SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', ' SELECT @loop = @loop + 1 END IF (RIGHT(@schedule_description, 2) = N', ') SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' ' END IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < freq_interval =" 08)" freq_interval =" 09)" freq_interval =" 10)" freq_type =" 0x40)" color="#009900">-- AutoStart

BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description

END

--2. Run query to create run logs.
--SQL 2000 query
SELECT sysjobs.name, cast((sysjobschedules.active_start_time / 10000) as varchar(10)) + ':' +
right('00' + cast((sysjobschedules.active_start_time % 10000) / 100 as varchar(10)),2) active_start_time, dbo.schedule_description(sysjobschedules.freq_type, sysjobschedules.freq_interval,
sysjobschedules.freq_subday_type, sysjobschedules.freq_subday_interval, sysjobschedules.freq_relative_interval,
sysjobschedules.freq_recurrence_factor, sysjobschedules.active_start_date, sysjobschedules.active_end_date, sysjobschedules.active_start_time,
sysjobschedules.active_end_time) AS ScheduleDscr, sysjobs.enabled
FROM sysjobs INNER JOIN
sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id

--SQL 2005 Query
SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS varchar(10))
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS varchar(10)), 2) AS active_start_time,
dbo.schedule_description(dbo.sysschedules.freq_type, dbo.sysschedules.freq_interval, dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval, dbo.sysschedules.freq_relative_interval, dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date, dbo.sysschedules.active_end_date, dbo.sysschedules.active_start_time, dbo.sysschedules.active_end_time)
AS ScheduleDscr, dbo.sysjobs.enabled
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

Saturday, June 21, 2008

Write Query Results Directly to File


DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

--The character after -t notes the field delimeter. If -t is removed tab is the default.

SET @FileName = REPLACE('c:\Test_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM sysfiles" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -t,'

EXEC xp_cmdshell @bcpCommand

Friday, June 20, 2008

Kill all users in a Database

Here is a cool little script that will kill all spids in a specified database.

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('AdventureWorks')

PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute

Thursday, June 19, 2008

Script to setup Database Mail


--MAKE SURE TO STOP SQL SERVER AGENT BEFORE RUNNING THIS SCRIPT!!!!!!!
USE msdb
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
ALTER DATABASE [msdb] SET ENABLE_BROKER

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'pop.xxx.com';
SET @EmailAddress = 'email@adress.com';
SET @DisplayUser = (Select @@Servername);

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

EXEC msdb.dbo.sp_add_operator @name=N'DBAOperater',
@enabled=1,
@pager_days=0,
@email_address=N'email@adress.com'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email@adress.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName

SELECT * FROM sysmail_allitems
-----------------------------Email Samples------------------------------------
--Code to send an attachment
--EXEC msdb.dbo.sp_send_dbmail
--@recipients=N'email@adress.com', --[ ; ...n ]
--@body='Message Body',
--@subject ='Message Subject',
--@profile_name ='DBMailProfile',
--@file_attachments ='C:\FileAttachment.txt';

--Code to send query results
--EXEC msdb.dbo.sp_send_dbmail
--@recipients=N'email@adress.com', --[ ; ...n ]
--@body='Message Body',
--@subject ='Message Subject',
--@profile_name ='DBMailProfile',
--@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3',
--@attach_query_result_as_file = 1,
--@query_attachment_filename ='Results.txt'