Post reply

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

  • May 24, 2010 at 9:59 am

    #224941

    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' :ermm:), 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. :crazy:

    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

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

    Thank you.

    🙁

  • May 25, 2010 at 12:19 pm

    #1172277

    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.

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

    http://technet.microsoft.com/en-us/library/ms130779%28SQL.100%29.aspx

    Just FYI.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply