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.

Can anyone help me?
"Sunny" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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" wrote:
> 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.
>

It workd after I added SET NOCOUNT ON in stored procedure.
"Gary Walter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" wrote:
>> 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.
>>
>
>