Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-07 : 08:03:47
|
| kitty writes "Hi,I'm running two stored procedures that are identical except that one is responsible for Adding and the other is responsible for Removing. Both stored procedures contain multiple SQL commands and access the same UDF Function which parses a comma delimited string. The Add SP contains INSERT and DELETE commands. The Remove SP contains UPDATE and DELETE commands as well as a COUNT variable.They are running on SQL Server 2000 (SP3).My problem is this: The Add SP works perfectly. The Remove SP times out.I've tested the Remove SP in Query Analyser both from the SQL Server box and remotely. It works without a timeout, and in under a second depending on the length of the "array" passed to the UDF. I've run them both through SQL Server Profiler to see if there was any difference in how they were actually processing.The Add SP registers the following Event Classes and works properly:SP:StmtStartingSP:ExecContextHitObject:CreatedThe Remove SP registers the following Event Classes twice when the exec occurs and when the first SQL statement to access the UDF and create the Temp Tables occurs.SP:CacheHitAudit Object Permission EventIt appears to repeat itself from the beginning, registers the following event classes SP:StmtStartingSP:ExecContextHitObject:Createdand then appears to complete properly in Profiler, but in fact times out when it's run through the program. I'm sure that if I increase the SQLCommand timeout this will probably work but will take a long time to execute. Doing this will defeat the purpose of this particular stored procedure which is supposed to optimize the speed of the program.I can send the full Profiler Tracer files and the full Stored Procedure(s) code if you want.It looks to me like the Remove SP isn't getting the correct permissions to perform it's tasks and the Add SP is???Both of the them are running on the same box, have the same owner and type, are accessing the same databases and UDF, were created and set up in exactly the same way, have been assigned exactly the same permissions, and are being executed by the same program. I've examined the data going into the Remove SP and it appears to be formatted correctly.I've searched your site, the Books On Line, and Googled this problem, but was unable to find a solution.Really hope you can help!" |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-07 : 09:12:41
|
| Just to make sure the app is passing what you expect to sql server: did you also capture the command text in profiler and run that (copy and paste it) in a QA window? When you do that does that still run in about a second?Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Stored Procedure Timeout "Audit Object Permission Event"