stored proc failed to kill user connections?

[David Spaisman]

Hello:

I am working with an application unde mssql 6.5 ,sp4. We have a separate
database for reporting on a different box. Each night we load a backup into
the database from the production database.

I set up a stored procedure to kill all users connected to this reporting
database just before the load.

THe job looks like this under scheduled tasks as a cmdexec:

isql -U sa -P -d master -Q"exec killusersall_sp 7" -o
F:MSSQLatchjobs
evdb.log


This job died with the following message:


Process Exit Code 1. ...[-b On error batch abort] [-O use Old ISQL
behavior disables the following]<EOF> batch processingAuto console width
scalingWide messagesdefault errorlevel is -1 vs 1 [-? show syntax summary
(this screen)]


The killusersall is paramaterize sp that I pass the dbid of the database
for whom I want all users killed. I need to put the stored procedure in the
master because I egt the spid from sysprocesses.


I do not understand why the job died. Does it matter if I named the stored
procedure as sp_killusersall instead of killusersall_sp which I used.

Can any one advise me about how to get the job to run? Any assistance will
be greatly appreciated. Thanks you.

David Spaisman

[Andrew Wiegand]

Hi,
Try running your isql command in a dos window and see if it works. Do you still get an error message?:
isql -U sa -P -d master -Q&#34;exec killusersall_sp 7&#34; -o F:MSSQLatchjobs
evdb.log

If you do get an error message what is it? If you still get an error message in dos try opening up ISQL/W and executing just your sql:
use master
exec killusersall_sp 7

Did that work? If not whats the error? The name of the sproc should not matter. You are getting an interesting error message.

hth,
Andrew

------------
David Spaisman at 3/27/00 8:50:19 PM

Hello:

I am working with an application unde mssql 6.5 ,sp4. We have a separate
database for reporting on a different box. Each night we load a backup into
the database from the production database.

I set up a stored procedure to kill all users connected to this reporting
database just before the load.

THe job looks like this under scheduled tasks as a cmdexec:

isql -U sa -P -d master -Q&#34;exec killusersall_sp 7&#34; -o
F:MSSQLatchjobs
evdb.log


This job died with the following message:


Process Exit Code 1. ...[-b On error batch abort] [-O use Old ISQL
behavior disables the following]<EOF> batch processingAuto console width
scalingWide messagesdefault errorlevel is -1 vs 1 [-? show syntax summary
(this screen)]


The killusersall is paramaterize sp that I pass the dbid of the database
for whom I want all users killed. I need to put the stored procedure in the
master because I egt the spid from sysprocesses.


I do not understand why the job died. Does it matter if I named the stored
procedure as sp_killusersall instead of killusersall_sp which I used.

Can any one advise me about how to get the job to run? Any assistance will
be greatly appreciated. Thanks you.

David Spaisman

[Deha Senbay]

There should be a script in this site which disconnects the users from the SQL. Check the SQL Script Library( I think sp_axeusers) and modify it.

The other option is to put your database in dbo mode during the load process and take the dbo option off.

------------
Andrew Wiegand at 3/27/00 10:13:21 PM

Hi,
Try running your isql command in a dos window and see if it works. Do you still get an error message?:
isql -U sa -P -d master -Q&#34;exec killusersall_sp 7&#34; -o F:MSSQLatchjobs
evdb.log

If you do get an error message what is it? If you still get an error message in dos try opening up ISQL/W and executing just your sql:
use master
exec killusersall_sp 7

Did that work? If not whats the error? The name of the sproc should not matter. You are getting an interesting error message.

hth,
Andrew

------------
David Spaisman at 3/27/00 8:50:19 PM

Hello:

I am working with an application unde mssql 6.5 ,sp4. We have a separate
database for reporting on a different box. Each night we load a backup into
the database from the production database.

I set up a stored procedure to kill all users connected to this reporting
database just before the load.

THe job looks like this under scheduled tasks as a cmdexec:

isql -U sa -P -d master -Q&#34;exec killusersall_sp 7&#34; -o
F:MSSQLatchjobs
evdb.log


This job died with the following message:


Process Exit Code 1. ...[-b On error batch abort] [-O use Old ISQL
behavior disables the following]<EOF> batch processingAuto console width
scalingWide messagesdefault errorlevel is -1 vs 1 [-? show syntax summary
(this screen)]


The killusersall is paramaterize sp that I pass the dbid of the database
for whom I want all users killed. I need to put the stored procedure in the
master because I egt the spid from sysprocesses.


I do not understand why the job died. Does it matter if I named the stored
procedure as sp_killusersall instead of killusersall_sp which I used.

Can any one advise me about how to get the job to run? Any assistance will
be greatly appreciated. Thanks you.

David Spaisman