When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed. These stored procedures are deleted only when the connection is closed. My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.

My questions: Why it occur ? Wich have created this temporary stored procedure ? How to avoid it occur ?

The versions are: SQL Server 6.5 Visual Basic 5.0 SQL Server ODBC Driver 2.65.0240

-- Anonymous, September 03, 1998

Answers

Re: creating temporary stored procedures fills tempdb

Misael,

You probably have the 'Generate Stored Procedure for Prepared Statement' option set on your ODBC connection. You need to clear this option.

You can see this option when you press the options button when you configure the data source. SQL Server generates a stored procedure for each prepared statement if this option is checked (which it is, by default). The stored procedure is stored in tempdb and will not be deleted even when the statement is dropped. It will only be deleted when the connection is deleted. So, eventually, theses stored procedures will fill the tempdb.

Eric

-- Anonymous, September 09, 1998