New fixed system file full backup

The file creation is not as good as it should have been. Fixed now

USE [master]
GO


DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\\wkfile03\DiskBackup\wkshptdbtest\';

DECLARE @tDatabases TABLE
( databaseId int
--, DATABASE_NAME varchar(25)
);

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 [NAME] IN ('master', 'model', 'msdb' );

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(100)
set @dbname = db_name(@dbId);

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

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(500);
declare @fullPath nvarchar(1000);

--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 @filePath;

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;

Moving users and schemas

I am in the process of getting rid of universal users for all applications and have to create and move a great number of users from test to production and was looking for easiest way. This is the best I have found so far.

/* This script is used for moving a login and schema from test to
production. This includes creating a new schema and read and write access
The steps are:
1. Go to the test enviroment and select User Name -> CREATE to cliboard
2. Paste the new user information in the block below
3. Select the new user name
4. Highlight the [NewUser] name
5. Run a find and replace  on the new user name (There should be 5)
6. Copy the schema name [NewUserSchema]
7. Replace schema name in the last block
8. Set the password in the first line
9. Run on the correct server
*/


CREATE LOGIN [NewUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[AuditDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- START paste new user info here
USE [AuditDB]
GO

/****** Object:  User [NewUser]    Script Date: 01/10/2011 10:46:51 ******/
GO

CREATE USER [NewUser] FOR LOGIN [NewUser] WITH DEFAULT_SCHEMA=[NewUserSchema]
GO

-- END Paste new user

EXEC sp_addrolemember 'db_datawriter', [NewUser] ;
EXEC sp_addrolemember 'db_datareader', [NewUser] ;

GO


CREATE SCHEMA [NewUserSchema] AUTHORIZATION [NewUser]
GO

Reporting Services and xml

I am currently struggling to create method of scheduling Reporting service subscriptions without having that stupid GUID as the job name. First I needed to learn more about querying the dubiously formatted Reporting services xml.

USE [ReportServer];

/****** Script for SelectTopNRows command from SSMS  ******/

WITH cteSubs (SubscriptionId, Params) AS
(
SELECT
SubscriptionID
,  CAST(CAST([ExtensionSettings] AS NVARCHAR(max)) AS XML) AS params
  FROM [dbo].[Subscriptions] subs
  INNER JOIN [dbo].[Schedule] sch
  ON subs.SubscriptionID = sch.[EventData]
 )
SELECT
SubscriptionID
,(SELECT nref.value('Value[1]', 'nvarchar(50)') Comment FROM Params.nodes('/ParameterValues/ParameterValue') AS R(nref) WHERE  nref.exist('.[Name = "Comment"]') = 1) AS Comment
 FROM cteSubs

This extracts the value of the comment so I can use it for the Job Name later

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