INFO: Turn Off the Use of 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:
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.
Additional query words:
Keywords : kbMTS kbMTS100 kbMTS200 kbSQL kbGrpCom kbDSupport
Version : winnt:1.0,2.0,6.5
Platform : winnt
Issue type : kbinfo