none
Forcing sp_unprepare through ADO RRS feed

  • Question

  • As part of my current project, I have the ...um... enviable task of emulating data playback derived from SQL Traces, the source of which is the Java Hibernate tool (with which I have admitedly limited familiarity). The data going into the Trace files contains a familiar pattern of sp_prepexec, followed - in every case - immediately - by an sp_unprepare statement for the same handle. So, yes, it could be more efficient, but that is what this existing application is generating.

    Anyway, my task is to enable the playback of this workflow for stress-testing purposes. While leveraging OStress is part of the longer plan, in the short term my goal is to recreate the current workflow pattern on a stress server, preserving the sequence of events, but varying the data. I've got the basics working in C#, but I've run into one thing I can't seem to recreate.

    I'm using the Prepare() method of the SqlCommand object to successfully get the sp_prepexec statements just as seen in the original trace. But no matter what I have tried, I can't seem to push an sp_unprepare to the server. I've tried terminating the connection, disabled connection pooling, even tried re-using the same command object with different SQL text. Nothing gets me that pattern I'm looking for - of prepexec followed by unprepare.

    Since the sp_unprepare takes the statement handle generated by sp_prepexec as a parameter, and I'm unaware of any way to capture that at the client level, I can't just send an sp_unprepare directly. I'm using the SqlConnection object in a using statement, so it should Close-and-Dispose properly. I'm currently at a point where I've run out of ideas of what else to try.

    Any suggestions?


    duncan davenport . data engineer and architect
    Wednesday, June 15, 2011 10:46 PM

All replies

  • Hello,

    Thank you for posting.

    From your description, I'm not very sure what goal you want to. Here's my understanding and question which need to clarify with you. If I misunderstood, please feel free to elaborate your question kindly.

    From your post, sp_prepexec generates some results which were passed into sp_unprepare. Your concern was that you didn't know how to capture these results and passed them into second store procedure. If yes, there are many ways to help you solve this concern.

    First, you can save these results into a temporary space of memory and read these value from block of this memory and re-use it.

    Second, I think you can use TransactionScope class to create multiple SqlConnection in your code. Please check this document for more information. http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

    Third, I think you can try to close connection string by manually instead of using USING keyword. But I didn't very suggest this workaround in your case. The second will be better for you.

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 20, 2011 10:38 AM
  • No, I don't think you have grasped what I am asking.

    The sp_prepexec and sp_unprepare are not invoked directly from my application. They are internal to the ADO.NET client, whenever a SqlDataCommand.Prepare() is issued. Well, the sp_prepexec is, anyway. This internal (out of my control) routine creates a statement handle, which it returns to the driver, and the driver (theorectically) uses it to sp_unprepare the statement.

    In JDBC - or the Hibernate tool, which is a Java object model over SQL - (which I am attempting to roughly emulate), it appears that each time sp_prepexec is invoked, it is immediately followed by an sp_unprepare. The driver is doing this, not the application (as far as I can tell). What causes this is JDBC is really not my question. My question is: how to I make the ADO.Net client behave this way. What ADO activity will cause an sp_unprepare to be sent to SQL. I can see that closing the connection does not do this successfully. What does?

    Thanks for your attention.


    duncan davenport . data engineer and architect
    Wednesday, June 29, 2011 8:23 PM
  • It's been 4.5 years so I doubt you are still waiting for an answer, but I do have a suggestion if you or anyone else is interested. I have not tried this but it should work: manually execute sp_prepexec instead of using SqlCommand.Prepare() to do it. The overall flow would look similar to:

    using (SqlConnection _Connection = new SqlConnection(_ConnectionString)) { int _SqlHandle; // might not be needed if re-using SqlCommand and SqlParameter retains value using (SqlCommand _Command = _Connection.CreateCommand()) { _Command.CommandText = "sp_prepexec"; _Command.CommandType = CommandType.StoredProcedure; SqlParameter _Handle = new SqlParameter("Handle", ...); _Handle.Direction = ParameterDirection.Output; _Command.Parameters.Add(_Handle); SqlParameter _Query = new SqlParameter("query", ...); _Query.Value = @"SQL query text"; _Command.Parameters.Add(_Query); _Command.ExecuteNonQuery(); // if results are needed, call ExecuteReader() instead _SqlHandle = (int)(_Handle.Value); // might not be needed _Command.CommandText = "sp_unprepare"; _Handle.Direction = ParameterDirection.Input; _Command.ExecuteNonQuery(); } }






    Saturday, February 20, 2016 6:30 PM