fischermx
asked on
Accesing temporary tables in SQL Server using Ado components
Hi,
I'm using Delphi 7 and Ado components to connect to a SQL Server 2000 server.
I have a T-SQL routine that creates serveral #Temporary tables.
So, I want to first, call this routine from my form, then call individidual TAdoQuerys to retrieve each individual of the resultsets.
Since all the components share the same connection (I have only one TAdoConnection in my app), this seems to be supposed to work, since temporary tables, belong to the current user's connection.
However, it doesn't work so. After I call the routine that creates the temporary tables (using either a TAdoCommand or a TAdoQuery), I have the temporary tables created, but my calls to retrieve the tables (Select * From #Temp1), return me the error "Invalid object name #Temp1"
Regards,
I'm using Delphi 7 and Ado components to connect to a SQL Server 2000 server.
I have a T-SQL routine that creates serveral #Temporary tables.
So, I want to first, call this routine from my form, then call individidual TAdoQuerys to retrieve each individual of the resultsets.
Since all the components share the same connection (I have only one TAdoConnection in my app), this seems to be supposed to work, since temporary tables, belong to the current user's connection.
However, it doesn't work so. After I call the routine that creates the temporary tables (using either a TAdoCommand or a TAdoQuery), I have the temporary tables created, but my calls to retrieve the tables (Select * From #Temp1), return me the error "Invalid object name #Temp1"
Regards,
Hi
When you create the temp tables - make sure the queries are server side.
I am not sure how you do that with Delphi (I dont use ADo with Delphi - only VB)
Then do a simple test on the connection by executing a query like
conn.execute('Select into #temp from Customers');
and then
But it goes something like MyDs=conn.open('Select * from #Temp');
That should work (excuse my Delphi here but like I said I dont use ADO with Delphi)
Voodooman
ASKER
The solution was to create the temporary tables in one command, then in a second command fill them and then perform as many individual commands as temporaty tables to retrieve each results sets.
Sorry, for post this too late.
Sorry, for post this too late.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1)may be your KeepConnection is false, and connection is closed?
2)try to check the same with global temporary table ##temp1
so you will know if a problem is with a connection