stored procedure execution problem

[newdba]

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...

[Rawhide]

Correct. RPC:Completed means that the RPC event completed and does not reflect whether it was successful or not. Are you also seeing "SP:StmtCompleted" events?

Try creating a trace using the Execution Warnings and Errorlog events from the Errors and Warning event class. The Execution Warnings event will list any warning that occurs during the execution of a SQL statement or stored procedure. The Errorlog event lists error events logged in the SQL Server error log. SQL Server errors returned by the trace will appear in red. The TextData data column will display the full error or warning.


Off the top of my head, the first thing I would check would be to see if the account that the application is using has proper permissions to insert data into the table.

Login in with Query Analyzer using the account that the app uses and run the procedure.

[newdba]

I have the execution warnings,errorlog & eventlog events in the trace but i am not getting anything in the trace for it.
i need to add a little more detail...this problem is intermittent.the sp is able to run successfully sometimes & fails at other times…so there are no permissions issues. 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…
when i am seeing the attention come up, i dont see a stmt completed. i only see a rpc completed.. which as u explained only means that the event completed, but not necessarily successfully.
another thing is that this insert includes images. and the stored procedure is handling it with a straight INSERT (no writetext or textcopy).the images average from 25 KB in size to approx 1.5 MB. I am concerned that maybe INSERT is not able to handle these images?? what is the industry standard for transferring images from disk to sql server asnd also from table to table?