TempTable in SP -> VB

[ChrisMan]

I have a stored procedure in MSSQL 7 populate a local temporary table (#ttemp). The SP ends with SELECT * FROM #ttemp.
How can I get this selected data into VB6? If I execute the SP from VB no recordset is kept, I guess because the temporary table is dropped.

Anybody have a solution to how I can get this data from the SP into VB?

TIA
ChrisMan

[Cakkie]

you can just get them in VB using a recordset obtained by using the execute method of the connection.
However, you won't be able to update the data back to the server automatically, you will have to write that yourself

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[ChrisMan]

The problem is that I'm not able to obtain any recordset. It seems to be lost immediately after the execute is done. The difference from my other SP's returning data via select (working OK) is that this one is returning data from a temporary table. And, as I say, there is no recordset. Please help!

[Cakkie]

No recordset, or an empty recordset? Big difference.
Try running it from Query Analyzer.
If all goes well, you shouldn't have a recordset either (if you do, you have a serious problem).
Also, there's this great tool for that, the profiler (which come with SQL server), which enables you to monitor the activity on your server. You can filter this so that it shows every command executed in a stored procedure. this way, you can tell exactly where he does what.

If that doesn't trap the error, post the code, so we can have a look on what exatly should be happening.


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[ChrisMan]

Test case:

SP's on server:
(all are returning OK from Query Analyzer)

CREATE PROCEDURE spTest0 AS
select * from tTest

CREATE PROCEDURE spTest1 AS
if exists (select * from sysobjects where id = object_id(N'tTest') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tTest
CREATE TABLE tTest (
tid int IDENTITY (1, 1) NOT NULL ,
title varchar(10) NOT NULL )
insert into tTest (title) values ('a')
insert into tTest (title) values ('b')
insert into tTest (title) values ('c')
select * from tTest

CREATE PROCEDURE spTest2 AS
CREATE TABLE #tTest (
tid int IDENTITY (1, 1) NOT NULL ,
title varchar(10) NOT NULL )
insert into #tTest (title) values ('a')
insert into #tTest (title) values ('b')
insert into #tTest (title) values ('c')
select * from #tTest

Code in VB6:

'de1 is DataEnvironment
'cn1 is Connection to SQL7 DB where SP's are created
'run with spTest1 first time to create tTemp table on server

Private Sub Command1_Click()
Dim rs As Recordset
de1.cn1.Open
'replace sp and get different results
Set rs = de1.cn1.Execute("spTest1")
'I get ...object is closed on the next line except for spTest0
rs.MoveFirst
End Sub

I thought it was the temp table that was the problem, but now I see I get the same error even for a "real" table created in an SP.

What am I doing wrong?

[Cakkie]

The reason you get the error even with the real table is because you didn't specify the cursor type, which by default is forward only, so the movefirst will definitly cause an error.
To overcome this, you must set the cursortype of the recordset to adKeySet or adDynamic.


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[ChrisMan]

Sorry, but the cursortype doesn't help. MoveFirst isn't the problem, the rs is open after exec sp0, but closed after exec sp1 and 2. Any other ideas, please?

[Cakkie]

In the SPs, set the option nocount on, until you start selecting the data. This prevents SQL server from sending '1 row affected', when you don't need to know.

set NOCOUNT on -- Don't show messages

CREATE PROCEDURE spTest2

as

CREATE TABLE #tTest (
tid int IDENTITY (1, 1) NOT null ,
title varchar(10) NOT null )

insert into #tTest (title) values ('a')
insert into #tTest (title) values ('b')
insert into #tTest (title) values ('c')

set NOCOUNT on -- show messages

select * from #tTest





Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cakkie]

Correction, the SET NOCOUNT ON should be inside the procedure, and the second SET NOCOUNT should be SET NOCOUNT OFF, instead of ON

CREATE PROCEDURE spTest2

as

set NOCOUNT on -- Don't show messages

CREATE TABLE #tTest (
tid int IDENTITY (1, 1) NOT null ,
title varchar(10) NOT null )

insert into #tTest (title) values ('a')
insert into #tTest (title) values ('b')
insert into #tTest (title) values ('c')

set NOCOUNT OFF -- show messages

select * from #tTest





Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[ChrisMan]

Thanks a lot!

The nocount option was the answer, I had forgotten about that one. It was long time ago I wrote SP returning records using select. Thank you!