Temp tables and changing SPID 
Author Message
 Temp tables and changing SPID

Hello all,

I am having an issue with a VB DLL that I have written and the way it
interacts with the SQL Server database I am using.

The DLL uses a global connection for all database tasks. I also use temp
tables created by this global connection.

The problem is when I start using the DLL, it connects to the database,
creates the temp tables and then a few steps later I run a stored proc that
operates against the temp tables, but it can't find them.

I have run a trace against the database while all of this is happening and I
notice that the SPID for my DLL changes several times while trying to do all
of this. The Client Process ID stays the same. This explains why I can't
access the temp tables after they have been created, the SPID is different
by the time it get to running the store proc.

My question is, how can I prevent this from happening? Is there a way to
force my DLLs connection to use the same SPID all the time? Does this have
something to do with connection pooling and/or OLE DB?

I have tried adding "OLE DB Services=-2" to my connection string to disable
connection pooling but that doesn't seem to help.

Here's some info that may be if use:
Visual Basic 6.0 SP5
ADO 2.6
SQL Server 2000 (although the database is in 6.5 compat mode)
Windows 2000 Server SP2
OLE BD Provider = SQLOLEDB

Thanks in advance,
Richard



Tue, 07 Sep 2004 23:15:23 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. How to: temp = Image1, or temp.Picture = Image1.Picture

2. My app makes a temp file in win/temp dir upon execution

3. Temp Table

4. Locale temp tables in ADP

5. Delete Temp. Table

6. Deleting a temp table upon closing a report.

7. Disappearing SQL Server Temp Tables when ActiveX DLL

8. Getting data from #TEMP table using ADO

9. SQL Server & Temp tables

10. How do I Create Temp Tables

11. How can I copy an SQL selection to an empty temp table

12. help w/ ODBC Informix temp table problem

 

 
Powered by phpBB® Forum Software