Stored Procedure Text

A quick script to search the text of current stored procedures in the current database.

SELECT
[name] as SpName
, obj.id as SpId
, com.[text] as SpText
, crDate as SpCreateDate
, *
FROM sys.sysobjects obj
LEFT OUTER JOIN sys.syscomments com
ON obj.id = com.id
WHERE type IN ('P', 'TF') -- P = User Stored Proc TF = User Defined Function
AND com.[text] LIKE '%' + '[TABLE NAME]' + '%'

Searching for table inforation in SQL jobs

I'm putting this here to make it easier to look at job and job step information quickly.


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
Script Date: 06/24/2009 13:32:21 ******

This script is used to return every job step where a string occurs

*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
@fileName as varchar(125)
AS

/*
declare @fileName as varchar(125)
set @fileName = 'Order Invoice Import Data.dstConfig'
*/

select
[name] as JobName
, step.step_id as JobStepNbr
, step.step_name as JobStepName
, subsystem as JobStepType
, command as JobStepCommand
, database_name AS JobStepDbName
, last_run_date AS JobStepLastRunDate
, last_run_time AS JobStepLastRunTime
, next_run_date AS JobStepNextRunDate
, next_run_time AS JobStepNextRunTime
, run_status AS JobStepLastRunStatus
--, hist.*
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
INNER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
LEFT OUTER JOIN dbo.sysjobhistory hist
ON job.job_id = hist.job_id
AND step.step_id = hist.step_id
AND step.last_run_date = hist.run_date
AND step.last_run_time = hist.run_time
WHERE command like '%' + @fileName + '%';



----------------
Now playing: NPR - 0906171: NPR: 06-20-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL [dbo].[usp_send_cdosysmail]

After my last post I realized I should just post [dbo].[usp_send_cdosysmail].

USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_send_cdosysmail] Script Date: 06/17/2009 11:59:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE procedure [dbo].[usp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10)
as

declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)

exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @smtpserver
exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null

-- sample error handling.
if @hr <>0
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg

SQL 2000 Sending Email using cdos usp_send_cdosysmail

This wonderful utility provided by Microsoft to not use SQL Mail with SQL Server 2000 to send SMTP email. I usually compile this into master database so it can be used by all databases and agent jobs. This is a sample of how to send and email because I am forever looking for an example.

USE [master]
GO

DECLARE @return_value int

DECLARE @body_text varchar(3000);
SET @body_text = 'Body Header' + char(13)

SET @body_text = @body_text + 'Message line one
'
SET @body_text = @body_text + 'Message line two
'
print @body_text;

EXEC @return_value = [dbo].[usp_send_cdosysmail]
@from = N'noreply.server_name@generac.com',
@to = N'user_name@server_name.com',
@subject = N'Success Email',
@body = @body_text,
@smtpserver = N'mail.server.com',
@bodytype = N'htmlbody'

SELECT 'Return Value' = @return_value

GO

Sql Auditing Changes Stored Procedure Design

/*
I will publish this as I don't have time to finish the documentation but this is the stored procedure that goes with the table below

All users who can drop tables need to be able to send and email or EXECUTE AS need to be applied to allow the stored procedure to both send the email and write the record.
*/

USE [Common]
GO
/****** Object: DdlTrigger [Event_SP_changes] Script Date: 06/02/2009 16:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger [Event_SP_changes]
on database --WITH EXECUTE AS 'UserName'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command
INSERT INTO [AuditDB].dbo.Common_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile



GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Event_SP_changes] ON DATABASE



----------------
Now playing: NPR - 0905150: NPR: 05-30-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

Sql Auditing Changes Table Design

/*
This is the start of documenting the process by which I collect information about changes in the database. This is done through ddl triggers and I keep a copy of all of the changes. This step is creating an audit table in the AuditDB. I always give myself one database on every server to collect this kind of information. The later posts will have the stored procedure to populate this table
*/

USE [AuditDB]
GO
/****** Object: Table [dbo].[Common_SP_Events] Script Date: 06/02/2009 16:48:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_SP_Events](
[EventType] [nvarchar](25) NULL,
[EventTime] [datetime] NULL CONSTRAINT [DF_Common_SP_Events_EventTime] DEFAULT (getdate()),
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](25) NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectType] [nvarchar](25) NULL,
[Command] [nvarchar](max) NULL,
[FullFile] [xml] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [cdx_Common_SP_Events_EventTime] ON [dbo].[Common_SP_Events]
(
[EventTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

SQL 2005 Database Mail

I put this here so I have a standard template for sending mail. This script assumes there is a public email account with the name [Instance Name]Admin to send the email. The coalesce statement first checks if there is an instance name. The function will return NULL is it is the default. The second value will then insert the machine name to generate the user name. This is included to allow the same email script to be used on multiple servers especially test and production with no changes to the script.

There are many more options i

DECLARE @profile varchar(100)
DECLARE @EmailTo varchar(128)
DECLARE @Subjectvarchar(128)
DECLARE @Body varchar(max)
DECLARE @BodyType varchar(25)

SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName' ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
SET @EmailTo = 'address@domain.com'
SET @Subject = 'test'
SET @Body = 'test'
SET @BodyType = 'HTML' --TEXT

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @EmailTo ,
@subject = @Subject,
@body = @Body ,
@body_format = @BodyType


----------------
Now playing: NPR - 0905136: NPR: 05-16-2009 Wait Wait... Don't Tell Me!

SP2 Upgrade Issue

Had an issue while updating SQL Server 2005 RTM to SP2. The SISS packages would start and then go into a weird suspended mode.

The history error message was:
Step 1 of job 'Inventory Reconciliation Job' (0x2070EF085C1D99489E13145AC7D55282) cannot be run because the SSIS subsystem failed to load. The job has been suspended.

The answer was :

http://support.microsoft.com/?kbid=914171

The SQL that fixed it was:
use msdb
go

select * from msdb.dbo.syssubsystems;
/*
--useful bit commented out here
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
*/
go

SQL Check DB

/*
This is the routine I use for running check DB. It simply loops through the master miles and runs check DB. There is an optional parameter to suppress info messages.
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
FROM sys.databases s_mf
WHERE s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT [NAME] IN ('tempdb', 'master', 'model', 'msdb' )--eliminate temp db
;

DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId INT;

OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @dbname nvarchar(50)
SET @dbname = db_name(@dbId);
DBCC CHECKDB (@dbName);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SQL User Backup Scipt Update and Fixed

/*
Fixed Backup script
The root path was not set causing the directories to not be set correctly.

Again the master.dbo.xp_create_subdir is only available in 2005 and 2008

*/

USE [master]
GO

DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\[Your backup locationand a]\';

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT
s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model'); --eliminate temp db


DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;
OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @Filepath NVARCHAR(500);
declare @dbname nvarchar(50)
set @dbname = db_name(@dbId);

SET @FilePath = @RootPath + @dbName;
PRINT @FilePath

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
select @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;


--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null
begin
declare @errMsg nvarchar(128)
select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SSIS Transfer Database task

Just a few notes about the use of the transfer database control task in SSIS 2005.

We had several problems with the logins being corrupted when the database was placed in the new instances. It appears that the task attempts to match the users to the instances users as one of its' steps. If it cannot find the user names in the new instance it corrupts the dbo users and schema.

The simple solution is to make sure that all users exist in the new instance. The transfer database task will then match the DBID of the instance users. This saves you from having to run sp_change_users_login to match the DBID from the new database to the instance

Thanks you Sam for this one.