Query Giving error when run as Maintenance plan

Question

Post reply

Query Giving error when run as Maintenance plan

Eswin

SSCrazy Eights

Points: 8320
More actions
November 17, 2009 at 10:08 pm

#136920

Hi experts,
The following query when run as a maintenance plan gives an error.
But it runs fine when run in query window.
--From error log file text.
Error number: 100
Error Message: 'An exception occurred while executing a Transact-SQL statement or batch.
--From job View History
Executed as user: AST\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:26:13 AM Progress: 2009-11-25 10:26:14.89 Source: {3600F480-5567-4008-9815-F630162490ED} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-11-25 10:26:17.26 Source: Execute T-SQL Statement Task Executing query "USE master; --[Close all user connections to db".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:26:13 AM Finished: 10:26:17 AM Elapsed: 3.953 seconds. The package execution failed. The step failed.--
--
USE master;
--[Close all user connections to db, so that process can start]
exec sp_KillAllUserSessions;
declare @FileExists int
exec master..xp_fileexist 'D:\dba\dba_110809.bak' ,@FileExists output
if (@FileExists = 1) --Backup File Exists
BEGIN;
declare @db_id int;
set @db_id = db_id('dba')
if @db_id is not null
BEGIN;
--Make database Single User
begin;
ALTER DATABASE dba
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
end;
--Set database Read_Write
BEGIN;
ALTER DATABASE dba
SET READ_WRITE;
END;
--Remove current database
Begin;
Drop DATABASE dba;
End;
--Restore Copy of Production as dba Database
RESTORE DATABASE [dba] FROM DISK = N'D:\dba\dba_110809.bak' WITH FILE = 1, MOVE N'dba_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dba.MDF', MOVE N'dba_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dba_Log.LDF', NOUNLOAD, REPLACE, STATS = 10
--Change recovery mode
ALTER DATABASE dba
SET RECOVERY SIMPLE
--
END;
ELSE
BEGIN;
--Restore Copy of Production as dba Database
RESTORE DATABASE [dba] FROM DISK = N'D:\dba\dba_110809.bak' WITH FILE = 1, MOVE N'dba_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dba.MDF', MOVE N'dba_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dba_Log.LDF', NOUNLOAD, REPLACE, STATS = 10
--Change recovery mode
ALTER DATABASE dba
SET RECOVERY SIMPLE
END;
END;
The code for sp_KillAllUserSessions
--
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_KillAllUserSessions] Script Date: 11/18/2009 10:29:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_KillAllUserSessions] as
Begin
declare @database sysname
,@Login sysname
,@Host sysname
,@OsUser sysname
,@SP_ID smallint
,@CRLF varchar(2)
,@SQL_Stmt nVarchar(500)
,@NotifyMsg nVarchar(500)
declare cr_processes cursor for
select spid,
db_name(dbid) as DbName,
loginame as Login,
hostname as Host,
nt_username as OSUser
from master..sysprocesses where db_name(dbid) = (select top 1 db_name(dbid)
from master..sysprocesses where spid=@@spid) and spid <> @@SPID and spid >=50
-- close/kill all connections for this database
open cr_processes
while 1 = 1
begin
fetch cr_processes INTO @SP_ID, @database, @login, @Host, @OSUser
if @@FETCH_STATUS <> 0 break
print 'Killing Process : ' + cast( @SP_ID as varchar(10))
select @SQL_Stmt = N'kill ' + cast( @SP_ID as varchar(10))
print @SQL_Stmt
execute sp_executesql @SQL_Stmt
end
close cr_processes
deallocate cr_processes
End
Please help...
I'm using MS SQL Server 2005 SP2.
Tanx 😀