SQL temp tables not working with ADO recordset

[Guest]

My stored proc works fine without the temp table code.
INTO #temp
from #temp
drop table #temp

When the temp table code is in place, Excel throws out the
error message at this point

--If Not rsData.EOF Then

Run time error 3704
Operation is not allowed when object is closed

It would be ideal to use temp tables for this project.
any advice ?

 

[Rob Bovey]

I've never had trouble with temp tables in stored procs, so I'd suspect
something you're doing is causing the problem. Here's a simple example that
runs fine here. If it runs fine for you also, I'd start looking for other
reasons that your recordset object is getting closed.

-------------
Stored Proc:
-------------
USE Pubs
GO

CREATE PROC spTest
AS
SET NOCOUNT ON

SELECT *
INTO #TEMP
FROM authors

SELECT *
FROM #TEMP

DROP TABLE #TEMP

------------
VBA Code
------------
Public Const gszCONNECTION As String = _
"Provider=SQLOLEDB;Data Source=P2800\P2800;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI"

Sub CallStoredProc()
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.Open "spTest", gszCONNECTION, adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData
rsData.Close
Set rsData = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

Thanks
I got it working by adding SET NOCOUNT ON.

-----Original Message-----

I've never had trouble with temp tables in stored procs, so I'd suspect
something you're doing is causing the problem. Here's a simple example that
runs fine here. If it runs fine for you also, I'd start looking for other
reasons that your recordset object is getting closed.

-------------
Stored Proc:
-------------
USE Pubs
GO

CREATE PROC spTest
AS
SET NOCOUNT ON

SELECT *
INTO #TEMP
FROM authors

SELECT *
FROM #TEMP

DROP TABLE #TEMP

------------
VBA Code
------------
Public Const gszCONNECTION As String = _
"Provider=SQLOLEDB;Data Source=P2800\P2800;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI"

Sub CallStoredProc()
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.Open "spTest", gszCONNECTION, adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
If Not rsData.EOF Then Sheet1.Range

("A1").CopyFromRecordset rsData