Calling Stored Proc from within Trigger Hangs

Question

Calling Stored Proc from within Trigger Hangs

MostInterestingMan

SSCertifiable

Points: 6291
More actions
February 4, 2011 at 9:46 am

#142288

Hi,
I am having trouble calling a stored procedure from within a trigger. I am not getting any errors however the query just hangs when it gets to the stored procedure call. I've searched the web but can't find any instances of this issue. Any help is appreciated.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the stored procedure you are calling...does it references the table that the trigger is on?

the trigger has got a lock on any rows that are being inserted/updated/deleted...so if the stored procedure is trying to access the same table (and the same rows)...then the proc is waiting on the trigger...and the trigger is waiting on the proc...

that ends up being a locked out condition.

depending on what the procedure does, you might need to do something like one of the following:

1. move the call to the procedure to a service broker, so after the trigger executes, the proc gets called.

or

2. move the code that is in the procedure inside the trigger.

if you can provide a few more details, we might have some better suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 2

Thanks for the response. Basically it's an INSERT TRIGGER that eventually creates a .csv file. On your advise I added the stored procedure code directly in the TRIGGER but it hangs at the xp_cmdshell call as shown below:

exec master..xp_cmdshell bcp "select 'col1,col2,col3,...'" queryout "\\UNC_path\shared\file_name.csv" -c -C RAW -t "," -r

This is the point in the code where I query the column listings and BCP the data out. Some other stuff happens before and after this point but everything is working up to this point and it hangs.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

Lowell (2/4/2011)
the stored procedure you are calling...does it references the table that the trigger is on?
the trigger has got a lock on any rows that are being inserted/updated/deleted...so if the stored procedure is trying to access the same table (and the same rows)...then the proc is waiting on the trigger...and the trigger is waiting on the proc...
that ends up being a locked out condition.
depending on what the procedure does, you might need to do something like one of the following:
1. move the call to the procedure to a service broker, so after the trigger executes, the proc gets called.
or
2. move the code that is in the procedure inside the trigger.
if you can provide a few more details, we might have some better suggestions.

Also the table the TRIGGER is on is not the table being queried...

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 4

I have this resolved. I added the stored procedure to a SQL job and called the job from within the TRIGGER:

msdb.dbo.sp_start_job N'job_name'

Thanks for your input.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends