INFO: Turn Off the Use of Temporary Stored Procedures with SQL Server (197809)

MORE INFORMATION

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:
  
  Note You must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

        driver={sql server}; server=myserver; UseProcForPrepare=No;
        database=pubs,UID=<user name>,PWD=<strong password>"
  						

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

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.