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'