May 7, 2008 at 6:10 am
I have a stored procedure that will fire 2 other stored procedures. When I run the 2 stored procedures one a a time in a query window everything is fine. When I run them in this stored procedure the job hangs. I think the second is firing before the first has finished. I've included the stored procedure that is giving me trouble below.
Thanks for your help, Ed
alter procedure usp_HH_Infosys_Timeclock_run
(@idb varchar(100))
as
declare @cmd1 nvarchar(4000)
Declare @udb varchar(100)
set @udb = @idb
if @udb = 'SouthPort' goto SouthPort
if @udb = 'LakePort' goto LakePort
if @udb = 'Regency' goto Regency
if @udb = 'ABC' goto Test
goto alldone
SouthPort:
exec usp_HH_Infosys_Timeclock_1 SouthPort,"To_AE_SPS_Time.txt","To_AE_SPS_Miles.txt"
exec usp_HH_Infosys_Timeclock_2 SouthPort,"To_AE_SPS_Time.txt","To_AE_SPS_Miles.txt"
goto alldone
LakePort:
goto alldone
Regency:
goto alldone
Test:
goto alldone
alldone:
return
May 7, 2008 at 6:34 am
No, each one is executed serially. Maybe the first one is holding a transaction open?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 7:17 am
If the first one is holding a transaction open, how come it works when I execute them in a query window. Below is the script that works in a query window.
/* run the stored procedure to update the HH Infosys Time Clock table
Parameters are:
@Idb = Infosys data base
@IAETime= Infosys ASCII export Time file name to Attendance Enterprise
@IAEMiles= Infosys ASCII export Miles file name to Attendance Enterprise
@Station= Attendance Enterprise (AE) Station Code
@Location= AE Location Code
@Supr= AE Supervisor code for the Miles File
@HHlocaluser= HH site user group
@HHhomeofficeadm= HH home office administrator (IT)
@TestOrProd= 'test' or 'production'
*/
print @@spid
go
sp_who @@spid
go
usp_HH_Infosys_Timeclock_1 SouthPort,"To_AE_SPS_Time.txt","To_AE_SPS_Miles.txt"
go
usp_HH_Infosys_Timeclock_2 SouthPort,"To_AE_SPS_Time.txt","To_AE_SPS_Miles.txt"
go
May 7, 2008 at 7:41 am
Well in your script being run in QA you are separating the procs with the GO batch separator so they are running in 2 batches. That is just one difference.
Secondly, have you sun sp_who2 when the job is hung and/or sp_lock? This might help tell you what is causing the hang.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2008 at 7:42 am
GO acts to close off open transactions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 7:42 am
OK. Jack's explanation is more clear. Go with that one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 7:48 am
I have not been able to put a "GO" in the stored procedure. What verb do I use in the stored procedure to get the same result?
Thanks
May 7, 2008 at 7:51 am
Nope. GO is an external construct for scripting within SSMS, not an actual part of TSQL as such. How about COMMIT TRAN?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply