Stored Procedure Execution Hangs from DTS

Question

Post reply

Stored Procedure Execution Hangs from DTS

Edwin Frydendall

SSCrazy

Points: 2361
More actions
May 4, 2005 at 12:56 pm

#74922

We have a stored procedure which hangs when called from DTS. If the stored procedure is called from Query Analyzer, it runs just fine. This does not happen with any other stored procedure. The logins for DTS and Query Analyzer are identical, so it doesn't look as though permissions are an issue. The statement the stored procedure is hanging on is an Insert into a table variable, and yet there are other table variable inserts which take place in the stored procedure BEFORE this particular insert, and they work great.
We've had three DBA's look at this so far, and nobody can figure it out.
Does anyone have any ideas why the stored procedure execution will hang when called from DTS, but finish normally when called from Query Analyzer? If you need more specific info to help answer this question (which you probably will), please ask.
Thanks.

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

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

aMSDeveloper Right there with Babe Points: 718 More actions · Answer 1

I dont know if you can share the code, but if you could or that code snippet, would probably help.

philcart SSC-Forever Points: 47796 More actions · Answer 2

Have tried looking at the execution from Profiler? It might uncover whats going wrong?

--------------------
Colt 45 - the original point and click interface

Dan Dougherty SSC Enthusiast Points: 155 More actions · Answer 3

Problem: ...the stored procedure is hanging on is an Insert into a table variable...

Does it work when this particular Insert is the ONLY statement in the SP?

If you change this table variable's name and value variables to obviously legal ones (not a key word, etc.), does it work?

If you change the value variables to literals (50, 'Dan', etc.), does it work?

Perhaps strip the SP to nothing and then add statements back in until it hangs, or start whole and remove statements until it works.

-Dan

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 4

I have experienced behavior like this when tryin to execute queries via the dts steps/tasks. I only use activeX scripts in DTS now because they are more reliable and don't seem to suffer from strange anomolies like this one. I would recomend moving your sql call to activeX. If you want to consider this option but aren't sure about the syntax let me know and I will provide you with syntax. Alternatively you can go to http://www.w3schools.com/ado/default.asp for an excellent source of ADO scripting.

mike gercevich SSCrazy Points: 2961 More actions · Answer 5

Also, you may want to try and set your Command-Timeout to a value of 20 rather than 0. If that does not help, look at your locks on the database. As Phil stated, profiler is the best way to figure out what is going on if these don't yield a solution for you.

-Mike Gercevich