ODBC Stored Procedure Locks in TEMPDB ??? 
Author Message
 ODBC Stored Procedure Locks in TEMPDB ???

Hi Gang;

Am noticing a LARGE # (1000+) of ODBC Stored Procedure locks in the
Tempdb...

In the form of   "#odbc#sad4e3cf________0000009A" ..

Trying to "sp_help  #odbc#sad4e3cf________0000009A " in the ISQL
returns " Select permission denied on the object
#odbc#sad4e3cf________0000009A,etc.."

Of course this would be the case since it is in the Data buffer Cache..

But how can I eliminate or minimize the number of locks ?

I am using the MDAC 2.5 ODBC shell with the option,

"disconnect SP when necessary"  turned "ON" ..

Should I scream at the Application Vendor to better code their ODBC API
function calls or what ??

Any info appreciated !!

J.P.





Fri, 04 Jan 2002 03:00:00 GMT
 ODBC Stored Procedure Locks in TEMPDB ???

<<I am using the MDAC 2.5 ODBC shell with the option,

"disconnect SP when necessary"  turned "ON" ..>>

I'm not familiar with the above option, usually the default "Create
temporary stored procedure for prepared statement" is the culprit with this
problem in SQL 6.5 using ODBC.

The "UseProcForPrepare" argument can be used in the connection string to set
this as well as the ODBC Administrator.

--
Bob Pfeiff
MCSD, MCT, SQL Server MVP



Sat, 05 Jan 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Stored Procedure locks tempdb

2. tempdb filling with odbc stored procedures

3. tempdb and odbc stored procedure

4. SQLServer 2000, ODBC, locking, tempdb - What changes Microsoft made that cause

5. SQLServer 2000, ODBC, locking, tempdb

6. Stored Procedures in tempdb

7. Stored Procedures in tempdb

8. Performance of SQL 7.0 : Stored procedure involving excessive usage of tempdb

9. Sue: Store Procedure in Master to create TempDB Users

10. Accessing tempdb from a stored procedure

11. VB execution of Stored Procedures fills TempDB

12. Accessing tempdb from stored procedure


 
Powered by phpBB® Forum Software