using temporary tables

[Bert]

When I execute the following query in Query Analyzer, it will display
a result (2 rows with table contents)

However, when I use it in a stored procedure and execute this (in
MS-Access adp) 2 empty rows are shown.

Anybody know how to use such a structure in a sp ?

SET NOCOUNT ON
CREATE TABLE #MyTable (id int, name nvarchar(50))
INSERT INTO #MyTable (id, naam) VALUES (1, 'name1')
INSERT INTO #MyTable (id, naam) VALUES (2, 'name2')
SELECT id,naam FROM #MyTable
DROP TABLE #MyTable

 

[Sylvain Lafontaine]

Remove the line DROP TABLE #MyTable and you should be OK to use this stored
procedure (SP) as the source of a report. When ADP fill be finished with
the display of the report, it will close the connection; hence deleting the
temporary table.

If you want to use it as the source of a form, you still will have trouble
because temporary tables are strongly associated with connection and ADP can
use two - or in some case, three - connections to manipulate the data of the
form. Because of the multiple connections nature of ADP, the use of a
temporary table with ADP can be tricky.

 

[Bert]

Thanks for your answer.

The actual query that I want to use is more complex and requires the
use of 2 temporary tables (although I use the TABLE variable for one)

I managed to get the stored procedure working as the source of a form
and everything seems to be working OK.

What kind of troubles could I expect with this? I did notice the
multiple connections that Access uses, but don't know about the
purpose of them.

Bert

 

[Sylvain Lafontaine]

Connections are used to communicate between ADP and the SQL-Server, for
example when ADP wants to retrieve a set of data. Until it's closed, one
set of data can only by active for each connection; hence the requirement
for more than one connection on most forms/reports. (This has changed with
SQL-Server 2005 but ADP are built on the requirements of SQL-2000).

Temporary tables are strongly associated with their connections, hence the
potential for trouble.