Solved

Problem with kill dts package.

Posted on 2004-10-08
10
637 Views
Last Modified: 2013-11-30
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
Comment
Question by:Steffee
  • 6
  • 4
10 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
ID: 12257614
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
 

Author Comment

by:Steffee
ID: 12257712
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12257818
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
 

Author Comment

by:Steffee
ID: 12257833
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12257918
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:BillAn1
ID: 12257926
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
 

Author Comment

by:Steffee
ID: 12258330
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12258413
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
 

Author Comment

by:Steffee
ID: 12258792
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12259386
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now