June 1, 2005 at 6:55 pm
i have a stored procedure that builds a dynamic insert statement & inserts data into a table. Now when I execute the
sp manually with a 'exec sptest parm1,parm2', it runs fine & inserts the data in the table. But when this sp is called from within a .net application,it prepares the insert statement but does not actually insert the record in the table. It comes back with a RPC: Completed so it seems like it completed but it does not insert the record in the table. Also just after the RPC:Completed, it throws an ATTENTION with nothing in the text data. I am confused on whats going on here. The definition of ATtention
in the event class implies that the query has been cancelled or it timed out. But we have no timeout on the sql server side.
the application developer says there is no timeout on the application side (i dont totally believe that). so what else could
cause that Attention? There is nothing in the error log as well. Also why does the trace come back with a RPC:Completed when the stored procedure did NOT insert any data? Does the RPC:Complete only mean that the RPC completed - irrespective of success or failure? If the sp failed or had an error will it still come back with a rpc:completed?
Any thoughts are appreciated...
June 2, 2005 at 6:42 am
Two things come to mind :
1 - Is there a trigger that can rollback the transaction without throwing an error?
2 - Is it possible that the application is using a different user to connect on the db, and that this user doesn't have the permissions required to do the insert?
June 2, 2005 at 8:16 am
Sorry I should have given some more details… the sp is able to run successfully sometimes & fails at other times…so there are no permissions issues & there are also no triggers on the table. When I look at the duration in the trace it shows that when the sp runs successfully it completes in less than 10 seconds, but whenever it fails & gets an exception , it has been running for about 30 seconds…
June 2, 2005 at 8:19 am
Can you rerun the proc in query analyser with the parameters that fail?
June 2, 2005 at 8:24 am
Yes I can rerun the same proc with the same parms in QA and it runs successfully.
This problem is random. The last time it started & we ran the same proc again on QA it took a long time to finish about 1.5 mins but it did finish & insert the data in the table.
At normal times this sp would complete in 5 seconds.
The last 2 times we had this problem, we restarted the app server & the problem disappeared but it happens again in a few days. I have checked that there are no blocking processes or deadlocks at that time as well…
June 2, 2005 at 8:28 am
Do you know which statement in the proc causes the delay?
You could print out the time needed to executed each part of the proc and then find out which query is giving you a headache... then it's gonna be easier to find a solution,
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply