• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

Problem with kill dts package.

I have a dts package which is erroring on one of the steps that excecutes the following code:

declare @cmd as char(100)
declare kill cursor for
select spID
FROM master..sysprocesses p join master..sysdatabases d on p.dbid = d.dbid
WHERE [name] = 'WorkMan'
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 [WorkMAN]
FROM DISK = N'Location
WITH RECOVERY
   MOVE 'WorkMan_data' TO location
   MOVE 'WorkMan_log' TO location



When I enter this line by line and try and parse it the error is found in the first occurance of exec ('kill ' + @cmd) which throws a syntax error near kill.

Does anyone have any idea what could be causing this. The package used to work fine and hasn't been changed, the only thing that has changed is that the SQL Server (2000) is now on a different domain to all the computers that connect to it. Previously all users accessing  the database were on the same domain the users were all moved to a new domain.
Unfortunately this package was written by a third party and the documentation is not very useful, i.e does not give any information whatsoever, I do not have very much knowledge at all on DTS packages so any help would be greatly appreciated.

Any ideas what could be causing the error?

Thanks in advance

Steph
0
Steffee
Asked:
Steffee
  • 6
  • 4
1 Solution
 
BillAn1Commented:
there is no way this code ever ran????
your cursor is called kill, which is a reserved word, so you cannot use that as a name for a cursor. Further throughout the code, you refer back to it as killcursor. So you should have

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] = 'WorkMan'
.......


the particular error you are getting is probably in the case where there are no sessions to kill. If this is the case, then @cmd will be NULL, but it will try to execute exec ('kill ' + @cmd) which will give a syntax error
instead, you should not have any exec outside the loop :


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] = 'WorkMan'
open killcursor
fetch next from killcursor into @cmd

while  @@fetch_status <> -1
   begin
      print @cmd
      exec ('kill ' + @cmd)
      fetch next from killcursor into @cmd
   end

close killcursor
deallocate killcursor
go


finally, you have missing quotes etc in the last bit, but this is probably not the original code is it?

0
 
SteffeeAuthor Commented:
Hi, thanks for your input, I have made the changes as you suggested, but I am still getting an error, in full it reads:

Microsoft OLE DB Provider for SQL Server
Error Description: Line 1: Incorrect syntax near kill

I think maybe my error is with the connection with the server not the actual step of the dts package...perhaps?


Thanks
Steph
0
 
BillAn1Commented:
can you post the actual code you ran?
that error is not to do with connections, it is because you try to execute the KILL command without any parameters, so there is a syntax error.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SteffeeAuthor Commented:
Here is the actual code that was run, thanks again for helping!

-- 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
while @@FETCH_STATUS <> -1
    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'
0
 
BillAn1Commented:
I can get the code to run fine on my computer (exect for the restore databse part, since I don;t ahve your DB etc)
when I run it as-is it runs fine, but it does nothing since I have no users connected to a database called 'NameMan'
when I change from 'NameMan' to a testDB I have, it works in that it kills all connections to that database.
Can you try running the code from within QueryAnalyser, and see what it prints before it executes?
inorder to distingush more clearly, try this extra printing :

-- 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
while @@FETCH_STATUS <> -1
    begin

        print @cmd
        exec ('kill  ' + @cmd)
        fetch next from killCursor into @cmd
    end
close killCursor
deallocate killCursor
0
 
BillAn1Commented:
sorry, hit submit too early - this is what I meant to propose :

-- 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
while @@FETCH_STATUS <> -1
    begin
       print '.........'
        print @cmd
       print '-------'
        exec ('kill  ' + @cmd)
        fetch next from killCursor into @cmd
    end
close killCursor
deallocate killCursor
0
 
SteffeeAuthor Commented:
Right, when I run this in query analyser I do not receive a syntax error, which baffles me slightly as it is exactly the same code.

Have also just realised that this is executing on our mirror server, which there would never be any users on, hence the reason I didn't get anything returned when I executed the code in query analyser.

Don't know if that helps at all.

STeph
0
 
BillAn1Commented:
are you SURE you are running the later version of the code?
I get the exact error you mention when I run your original code, but no problem when I run the later code. Perhaps you updated the DTS on one server, but actually executred the step on another?
I have built a DTS with this step in it to replicate and it runs fine.
0
 
SteffeeAuthor Commented:
Yes, you are right, this code does run, THANK YOU!
It still errors when I press parse in the step properties window but executes when I run it and succeeds. Thank you so much for you perseverence with me!
It would only have worked if someone was logged on to the mirror server when the package executed in it's previous form with the exec ('kill ' + @cmd) outside of the loop, I think?

Steph
0
 
BillAn1Commented:
yep, but also only if the cursor had the correct name (i.e. killcursor, not just kill) - perhaps that was a transposing error only?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now