Extremely odd remote stored procedure behavior.....Please Help!

[gam101]

Greetings!

Please help me with the following extremely odd stored procedure behavior on MS. SQL Server 2000.

I have a stored procedure that is executed remotely. When it is executed in our office test environment, the Profiler shows a perfect sequence of execution & return of results.

However (yes I know, there is always a 'however'), when the same process is executed in our client's environment, the Profiler displays a log with with an incomplete sequence of execution & there are no results returned. What makes the matter even more confusing is that there are no errors captured by the Profiler. Now, sometimes at the client site, the store procedure does execute successfully, but failure is far more common.

Just to clarify; Yes, the two stored procedures called remotely are identical. Yes, the parameters passed into the stored procedures are also identical.

Please see screen-shots below of the SQL Profiler...

In-House Microsoft SQL Server 2000 test environment;

Client's Microsoft SQL Server 2000 test environment;

What is apparent from the two Trace logs is that the key execute statement "exec sp_prepexecrpc @P1 output,....." is absent in the client's Trace log. So, something is preventing this statement from executing & I do not know what that is.

Here is a link to sp_prepexecrpc;

http://207.46.16.252/en-us/library/ff487116(SQL.105).aspx

I appreciate any input/advices/suggestions as I am at a complete loss.

Thank you.

 

[Crowley16]

have you checked permissions against remote calls on the client system?
what are you actually firing off? the permissions or security of the client may not allow the sproc, e.g. xp_cmdshell needs to be explicitly allowed...

have you tried running the exec section by itself directly from the client server? does that throw up any errors?

--------------------
Procrastinate Now!

 

[gam101]

@ Crowley16

Thank you for the prompt reply.

No, I have not checked permissions because as I mentioned in the post, sometimes the remote procedure does execute successfully. And when I execute it using the Query Analyzer from a Server's client with the "EXEC" statement, it is always successfully.

Hence the reason I am stumped.

Any ideas?

 

[gam101]

Just wanted to update this thread about a possible solution. It turns out that the "sp_prepexecrpc" statement that "prepares" the SQL command for execution was actually being sent by my remote app.

So, essentially, any SQL preparation is now disabled & the Profiler does not show any trace of "sp_prepexecrpc". I have noticed however, that the results returned by the stored procedure are now
delayed.

Just FYI.

 

[gam101]

Here is some more info. on SQL command preparation by Microsoft.....

http://technet.microsoft.com/en-us/library/ms130779(SQL.100).aspx