ADP stored procedures and #Tables

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

Im downsizing a project from SQLServer to MSDE using Access 2000.

I hoped that my stored procedures would just run without changes but
Access stored procedure dont like #Tables.
Is there a way of creating a temporary table in memory that only
exists for the duration of the sp in Access or will I need to have
lots of little real tables that get truncated every time I want to use
them. This would be a pain because there'll be about 200 of them to
cater for all my stored pocedures.

Cheers
Glenn
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MSDE is the same as SQL Server, except it doesn't allow more than 5
simultaneous connections (or so it's told), which means you can create
temp tables.

I just created an SP in MSDE. Saved it & got some ADOX error. Tried to
save again & the SP saved OK. Ran SP & it worked as expected.

Here is the SP as finally saved:

ALTER PROCEDURE dbo.usp_testing
AS SET NOCOUNT ON CREATE TABLE #temp(col1 integer, col2
integer)
INSERT
INTO #temp(col1, col2)
VALUES (1, 2)
INSERT
INTO #temp(col1, col2)
VALUES (3, 4)
SELECT *
FROM #temp

Don't know why it was formatted like this: one of my peeves about .ADPs
and my SPs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQYHsXYechKqOuFEgEQItAwCggyOK59ix+NRmZWCDzZCirgl+3ycAoMKQ
cilkRyzgU0QawOKASBh84E/i
=1YHf
-----END PGP SIGNATURE-----
 
Back
Top