ODBC & SQL Server

[Jian Yi]

I made a connection to SQL Server through ODBC by using CDatabase class. However, each new SQL transaction ( even including using "SELECT" to open table etc ) always occupy new space in tempDB. It should be dead in short time if tempDB is too small. So I have to disconnect from database and SQL Server will automatically clean tempDB, and then I can re-connect and reuse Database.

Question : How to clean up tempDB without disconnection or not use any space resource on SQL Server ?

[brevert]

What version of MFC are you using? You are probably using DAO and we all should use the OLEDB-based ADO objects. In any event, tempDB on the server is where all dynamic-SQL results are generated, regardless of how you connect to the database. The SQL Server needs somewhere to prepare the resultset for you, but tempDB should clean itself up when you are done with the result and release the cursor (or recordset) upon which the query is based. You can change the device SQL Server uses for its working storage, but most people just increase the size of tempDB and ensure the device is on a separate drive for a production environment.

[Jian Yi]

I am using CDatabase in VC++ 6.0. Is it related to "not commit tranction" ? or Bulkrow Exchange ( I use Bulkrow Exchange ONE time when initializing my application ) ? However, Even if I use "SELECT" later time, it also reduces some space from TempDB.

[Torben Worm]

I once had a simular problem - I solved it by clearing the 'Generate Stored Procedures for SQL statements' option in the ODBC driver setup.

Torben Worm