Cant call stored procedure which uses table variables?

[Sunny]

Hi,

I am using SQL Server 2000 and Access 2002. I am trying to use one of my
stored procedure in vba code. Following is the code and sql script.

VBA Code:
Sub DisplayClientId
Dim adoCommand As ADODB.Command
Dim rs As New ADODB.Recordset
Dim i As Integer
Set adoCommand = New Command
adoCommand.CommandText = "spGetClientId"
adoCommand.CommandType = adCmdStoredProc
Set adoCommand.ActiveConnection = cnObject -- Defined globally

Set rs = adoCommand.Execute

Set adoCommand = Nothing
i = 0
Do While Not rs.EOF
i = i + 1
MsgBox (i & " " & rs!ClientId)
rs.MoveNext
Loop
Set rs = Nothing
End Sub

StoredProcedure
CREATE PROCEDURE spGetClientId AS
DECLARE @tmpClients TABLE (ClientId char(5))
INSERT INTO @tmpClients
select ClientId from clients
select * from @tmpClients
GO

Above stored procedure does not return any record, and gives me error:
"Operation is not allowed when the object is closed"

But when I change stored procedure as follow, just works fine.

Alternate StoredProcedure
CREATE PROCEDURE spGetClientId AS
select ClientId from clients
GO

Both procedure works fine in query analyzer. I guess there is a problem
using table variable. Can anyone explain this behaviour? What is alternate
solution, I have to use intermidiate results to produce final results thats
why I have used table variable.

Thanks.

 

[Sunny]

Can anyone help me?

 

[Gary Walter]

I have no experience with this error,
but did you try setting cmd to nothing
at end of sub?

Sub DisplayClientId
Dim adoCommand As ADODB.Command
Dim rs As ADODB.Recordset
Dim i As Integer

Set adoCommand = New ADODB.Command
adoCommand.CommandText = "spGetClientId"
adoCommand.CommandType = adCmdStoredProc
adoCommand.ActiveConnection = cnObject -- Defined globally

Set rs = adoCommand.Execute

i = 0
Do While Not rs.EOF
i = i + 1
MsgBox (i & " " & rs!ClientId)
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set adoCommand = Nothing

End Sub

Is there any reason you decided not to
use temp table in stored proc?

(untested)

CREATE PROCEDURE spGetClientId
AS
CREATE TABLE #tmpClients ( ClientId char(5))
INSERT INTO #tmpClients
select ClientId from clients
select * from #tmpClients
DROP #tmpClients
GO

 

[Sunny]

It workd after I added SET NOCOUNT ON in stored procedure.