INFO: Turn Off Temporary Stored Procedures with SQL Server

ID: Q197809

The information in this article applies to:

• Microsoft Transaction Server versions 1.0, 2.0
• Microsoft SQL Server version 6.5

SUMMARY

When using SQL Server with Microsoft Transaction Server (MTS), it is important to turn off the use of temporary stored procedures for prepared SQL statements.

Prepared SQL statements are executed using the SQLPrepare and SQLExecute ODBC API functions. ADO and RDO make extensive use of prepared statements in certain circumstances. SQL Server will create temporary stored procedures when executing prepared SQL statements if the database connection is configured to do so. This can cause undesirable side effects when SQL Server is used with MTS. To prevent this you must either:

• Use a DSN with the "Create temporary stored procedures for prepared SQL statements" option turned off.

  -or-

• Include the "UseProcForPrepare=No" in your connection string for DSNless connections as in the following example:

        driver={sql server}; server=myserver; UseProcForPrepare=No;
        database=pubs,UID=sa,PWD="
  

        Note that the above string should be included on one line; it has
        been wrapped for readability.
  

MORE INFORMATION

The use of temporary stored procedures for prepared SQL statements may have the following unwanted effects:

• The temporary stored procedures can accumulate on the server because they are often not destroyed until a connection is closed. Transaction Server uses ODBC connection pooling and connections can remain open for an extended period of time. Temporary stored procedures are stored in SQL Server's tempdb database. Over time, these temporary stored procedures can fill up tempdb and cause queries to fail. When tempdb is filled up you will receive a SQL Server error 1105.
• The creation of stored procedures within transactions may cause blocking problems that can seriously affect concurrency. The creation of stored procedures causes SQL Server to lock data pages in several system tables for the duration of the transaction. Other transactions that try to create stored procedures will then be blocked until the first one has completed. If you have composed your MTS activity to include more than one transaction, these transactions may block each other until they time out.

Keywords          : TSrvGen TSrvODBC TSrvProg 
Version           : WINNT:1.0,2.0,6.5
Platform          : winnt
Issue type        : kbinfo

Last Reviewed: December 29, 1998