Stored Procedure Execution

[AndyKr]

Hi All

Can anyone suggest a reason why a block of code should execute normally when run from Query Analyzer/Management Studio (I have tried both versions) but hang indefinitely when called as a Stored procedure?

The code is complex but essentially does the following:

[1] Get a Date into a variable
[2] Select into three temporary tables various elements of data for the selected date
[3] Combine the data from the three tables into a single permanent table
[4] Drop the temporary tables

The code that is on the server is compiled but when scripted out and run directly executes in 2m 30s.

But when the SP was called I killed the process after 22 hours 30 minutes!

I am running out of ideas....anyone?





----
Andy Kramek
Visual FoxPro MVP

 

[AlexCuse]

Can you post the code for your SP?

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AndyKr]

>> Can you post the code for your SP?

No, sorry I can't. It is proprietary code and includes sensitive data. But the point here is that the CODE runs! It is only when the code is called as an SP that it doesn't.


----
Andy Kramek
Visual FoxPro MVP

 

[AlexCuse]

Well, OTTOMH, you probably have some kind of issue with object ownership or scope. But it could be something as simple as you didn't create the stored procedure correctly from your code that runs.

If you can't throw together a dummy proc that is doing the same things but omitting the sensitive data, then I suggest that you hire a consultant because you are not going to get much out of this forum (or any).

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[TysonLPrice]

If your on SQL 2000 and you have access you can step through the SP with the debugger.

 

[AndyKr]

>>But it could be something as simple as you didn't create the stored procedure correctly from your code that runs.

Hmm, considering that I already said that scripting the stored procedure from the server and then executing the code works. That doesn't seem very likely, now, does it?

>> If you can't throw together a dummy proc that is doing the same things but omitting the sensitive data, then I suggest that you hire a consultant because you are not going to get much out of this forum (or any).

Actually I AM the Consultant hired by the Client. Admittedly I have only about 10 years experience in SQL Server - going back to SQL Server 6.0 - but I have never seen anything like this.

I repeat, scripting the code from the server and then executing that code, runs in 2.5 minutes. Calling the stored procedure hangs indefinitely (I left it running for 22+ hours). It generates no errors, it simply does not complete.

There are no parameters passed in - it is a simple stored procedure call thus:

EXEC procedure_name

I have full rights (System Admin) on the server and there is no doubt that the SP is actually there.


----
Andy Kramek
Visual FoxPro MVP

 

[AndyKr]

>>If your on SQL 2000 and you have access you can step through the SP with the debugger.

SQL Server 2005 and the code on the server is encrypted

----
Andy Kramek
Visual FoxPro MVP

 

[AlexCuse]

I would try throwing some selects at various places in your proc, to ensure that the temp tables are actually created. Also include getdate() in these selects so that you can get some idea of where the lag is coming into the picture.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AndyKr]

>> I would try throwing some selects at various places in your proc, to ensure that the temp tables are actually created. Also include getdate() in these selects so that you can get some idea of where the lag is coming into the picture.

Yeah, I guess that's about all I can do. Thanks

----
Andy Kramek
Visual FoxPro MVP

 

[AlexCuse]

No problem. Good Luck

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom