Hi,
I am experiencing a problem with a dts package, unfortuantely these were written by a third party and the documentation is somewhat sparse. I have very little knowledge on dts packages. The package is failing on one step only, the code is as follows:
-- Kill all users in database before restoring
declare @cmd AS char(100)
declare killCursor cursor for
select spid
from master..sysprocesses p join master..sysdatabases d on p.dbid = d.dbid
where [name] = 'NameMan'
open killCursor
fetch next from killCursor into @cmd
print @cmd
exec ('kill ' + @cmd)
while @@FETCH_STATUS = 0
begin
print @cmd
exec ('kill ' + @cmd)
fetch next from killCursor into @cmd
end
close killCursor
deallocate killCursor
go
RESTORE DATABASE [NameMan]
FROM DISK = N'E:\SQLBackups\NameMan_FromServerName\NameMan.BAK'
WITH RECOVERY,
MOVE 'PODMan_data' TO 'D:\SQLData\MSSQL$Data\Data\NameMan_dat.mdf',
MOVE 'PODMan_log' TO 'D:\SQLData\MSSQL$Data\Data\NameMan_log.ldf'
When I parse this in the dts step properties window, it errors giving me a syntax error near kill on Line 1, however when I remove line 1 it still gives the same error.
Also confusing me is that fact that if I copy and paste this into query analyser window and parse it, it has no problems what so ever.
Does anyone have any ideas on this?
Thank you in advance,
Steph
I am experiencing a problem with a dts package, unfortuantely these were written by a third party and the documentation is somewhat sparse. I have very little knowledge on dts packages. The package is failing on one step only, the code is as follows:
-- Kill all users in database before restoring
declare @cmd AS char(100)
declare killCursor cursor for
select spid
from master..sysprocesses p join master..sysdatabases d on p.dbid = d.dbid
where [name] = 'NameMan'
open killCursor
fetch next from killCursor into @cmd
print @cmd
exec ('kill ' + @cmd)
while @@FETCH_STATUS = 0
begin
print @cmd
exec ('kill ' + @cmd)
fetch next from killCursor into @cmd
end
close killCursor
deallocate killCursor
go
RESTORE DATABASE [NameMan]
FROM DISK = N'E:\SQLBackups\NameMan_FromServerName\NameMan.BAK'
WITH RECOVERY,
MOVE 'PODMan_data' TO 'D:\SQLData\MSSQL$Data\Data\NameMan_dat.mdf',
MOVE 'PODMan_log' TO 'D:\SQLData\MSSQL$Data\Data\NameMan_log.ldf'
When I parse this in the dts step properties window, it errors giving me a syntax error near kill on Line 1, however when I remove line 1 it still gives the same error.
Also confusing me is that fact that if I copy and paste this into query analyser window and parse it, it has no problems what so ever.
Does anyone have any ideas on this?
Thank you in advance,
Steph