July 16, 2004 at 2:13 pm
Folks,
When I make a stored procedure that looks like this...
create procedure spTest
AS
select top 10 *
from users
And then call it from VBscript or VB, in the typical way...
Dim conn, rs
' ...set up conn to talk to database...
set rs = conn.Execute(SQL)
MsgBox rs.EOF 'result is "False"
...this works fine.
But if I introduce a temporary table to the SQL, like so...
create procedure spTest
AS
select top 10 *
into #tmp
from users
select * from #tmp
...then no recordset is returned to my VB code. Or rather, one is returned, but it is closed. When the MsgBox rs.EOF command above is fired, I get an error message saying the recordset is closed.
I'm sure I'm missing something obvious. Thanks in advance for any help understanding what I'm doing wrong...
July 16, 2004 at 2:34 pm
Further clues...
It doesn't seem to be a temp-table issue. If I save the data to a physical table rather than to a temp table, the same problem occurs.
Weird...
July 16, 2004 at 2:37 pm
And...
I tried to open it with an ADO Command object, but that didnt help...
I tried to issue a set rs = rs.NextRecordset, but no better...
- Tom
July 16, 2004 at 2:46 pm
I don't think your VB code is correct on the last post. You would first have to create the command object and then execute it. At that time you should have the recordset. Then if your SP has a 2nd recordset then you would use the NextRecordset method.
IE: set rs = cmd.Execute(SQL)
WHERE cmd is your command object.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 17, 2004 at 8:26 am
Gary,
Thanks, but I'm confident my code is good. I didn't list it all here, but I'm using well-tested routines that our team uses all the time. And as I said, when I change the SP the same VB code works just fine. So the critical difference is in the SQL, not the VB.
- Tom
July 17, 2004 at 5:53 pm
Hi,
At the beginning of the proc use:
SET NOCOUNT ON
You must unlearn what You have learnt
July 17, 2004 at 7:49 pm
rockmoose,
Well, I can unlearn what I've learnt but I can't unburn what I've burnt...which is several hours of futzing around trying to figure out the cause of this problem, BUT...
You are right, sir! For some reason, putting that command at the top of the SQL did the trick. I can carry on to victory with my original project now! Many thanks.
But teach a man to fish, etc. etc. Can anyone (rockmoose?) explain exactly what is at work here that putting SET NOCOUNT ON into the query has this effect?
- Tom
July 17, 2004 at 8:42 pm
Ok,
When you insert into the temporary table you will get something like:
(42 row(s) affected)
Familiar right ?
This is an informational PRINT statement that Sql Server does.
When the ODBC driver recieves this PRINT statement it will issue a SQL_SUCCESS_WITH_INFO message
and this will screw things up.
I had this issue a long time ago, I started by ripping my hair out ;-)...
Some Info can be found in BOL.
Look up "SET NOCOUNT" - Processing Results.
/rockmoose
You must unlearn what You have learnt
July 19, 2004 at 8:27 am
rockmoose, thanks for sharing the wealth! You saved my own thinning pate from horrible fate...
And folks -- for the record, apparently this behavior that rockmoose pointed out is also true for non-ODBC connections. We use OLE-DB strings to do our connections, but the result is the same. If I use SET NOCOUNT ON then this works, and if I don't, it doesn't.
Many thanks.
- Tom
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply