Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    199

    Unanswered: SQL 2005, sp recompilation problem

    I have an Application using MS Access ADP linked to SQL 2005.
    The problem appears when SP that is supposet to called from MS Access via RPC needs to be recalculated (usually if I've modified it).
    At that time, If I run the SP from SQL Server Management Studio Queri Window, it runs normally (recompilation succeeds), but the same time I can't run the same SP from Access ADP Command object, execute method causes timeout (regardless timeout parameter).
    If I modify the SP and simplify it (removing some high cost statements) than Command exec runs without problems, after that I undo the SP to it's begining code and Command exec runs again without problems.
    I wonder why executing of SP from SQL Management Studio (that recompilates the SP) doesn't helps MS Access ADP Command to run that SP without recompilation.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    The query cache can contain more than one execution plan for the same SP, if the connection properties are different. Usually the execution plan is the same, but not always.

    Example: Lets take two connections, with all properties being the same except Connection X Has ANSI NULLS ON, and Connection Y has ANSI NULLS OFF. Connection X executes a SP for the first time, and it is compiled. Connection Y will look in the query cache for an entry for the SP with the same connection properties. Since it doesn't find one, it will recompile and make a second entry in the query cache for the SP.

    In Your case, the connection properties from MS Access will not have the same properties as SSMS, so each will have it's own execution plan compiled.

    You can Google and find some articles about this.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you rummage about in the system tables you can decode the bitmap for each plan that indicates what options it uses:
    http://www.sqldev.net/misc/fn_setopts.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Posts
    199
    cascred, thanks for the reply.
    I guess you are right, I'll try to run the SP from SSMS with same connection options as ADP connection has.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •