recordsets and sp_executesql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

recordsets and sp_executesql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 13:57:38

Matthew writes "I am having a problem here my vbs file is not detecting the eof of the recordset. I'm not sure whats wrong because the following code works with a different stored procedure..

subscriberssubject.open "getsubscriberssubjects " & emailid, adOpenKeyset,adLockReadOnly
do until subscriberssubject.eof
thenewbooks.open "exec emailsubject " & subscriberssubject.fields("subjectid"),cn,adOpenKeyset, adLockReadOnly
if not thenewbooks.eof then
bodystring = bodystring & vbcrlf & ucase(subscriberssubject.fields("subjectname")) & vbcrlf
do until thenewbooks.eof
sendmail = 1
bodystring = bodystring & "Title: " & thenewbooks.fields("title") & vbcrlf
bodystring = bodystring & "Author: " & thenewbooks.fields("author") & vbcrlf
bodystring=bodystring & vbcrlf
thenewbooks.movenext
loop
thenewbooks.close
end if
subscriberssubject.movenext
loop
subscriberssubject.close

the emailsubject SP uses a simple select statement with a parameter.

the other stored procedure i put into emailsubjects place is a bit more complicated and generated a dynamic where clause and uses sp_executesql...

does anyone have any idea why the sp_executesql query makes the recordsets almost ineffective. the recordset does not get anything from the database- and the query does work fine in query analyzer"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-03-05 : 09:51:27

quote:

does anyone have any idea why the sp_executesql query makes the recordsets almost
ineffective. the recordset does not get anything from the database- and the query does work
fine in query analyzer

Do you have SET NOCOUNT ON at the beginning of your stored procedure?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-05 : 17:08:06

quote:

Do you have SET NOCOUNT ON at the beginning of your stored procedure?

Alternatively, have you SET NOCOUNT OFF at the beginning of your stored procedure....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources