Stored procedure must be re-created

  • Thread starter Thread starter Ilia
  • Start date Start date
I

Ilia

Hello,

we have a strange problem with a stored procedure on MS SQL Server 2000 SP3.

The procedure is pretty complex and use temporary tables to create XML with

FOR XML EXPLICIT. We read the XML data with ExecuteXmlReader in our ASP.NET

application (.NET Framework 1.1).

Most of time everything works well, but sometimes we get errors as no data

are read. We have to re-create the procedure on the SQL Server (ALTER

PROCEDURE with the original code) to make our program run again. It could be

enough sometimes to run the procedure in Query Analyzer.

Have somebody any idea, what we have to check or to change?

Thanks

Ilia
 
Consider that SQL Server stored procedures use a "cached" query plan
whenever possible. This plan is built the first time the SP is called and
left in the procedure cache. If the server needs the space and the SP has
not been called lately, the query plan can be overlaid. In this case, the
next time the SP is called the plan is rebuilt and cached. The problem is,
the plan is based on the parameters passed when compiled. If the parameters
change, the plan might not be appropriate. To see if this is the problem,
try adding "WITH RECOMPILE" to the SP creation script. This forces the SP to
recompile on each execution. Sure, it will be somewhat slower, but if the
problem goes away, you need to figure out how to make your SP simpler and
make the inbound parameters work regardless of the plan.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top