Store procedure runs on SQL Server and does not in VB

[andrey]

There is a simple store procedure like:
create procedure MyProc
as
create table #temp_table
(id int,
name varchar(50))
insert into #temp_table select * from other_table
select * from #temp_table
And there is no problem when it runs on SQL Server, but when I try to do in VB the following:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
...
...
Set rs=cn.Execute("MyProc&quot
Debug.Print rs.Fields(0).Value
an error occures because the recordset I recieve is absolutely closed. I tried to trace what happens on SQL Server when I use VB and saw that it produce additional connection to the temporary table and locks what ever it can to lock. I think it's just any bug of ADO, but what can I do with it?

 

[chiph]

Andrey -

Does the userid you're using to connect to the MS SQLServer have the CreateTable privledge?

Chip H.

 

[andrey]

Yes, it does. It has all permissions.

 

[andrey]

Yes! We've done it!
Before INSERT - SET NOCOUNT ON and at the end of procedure - SET NOCOUNT OFF.
It works!