469,934 Members | 1,581 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

SET NOCOUNT ON; Multiple Recordsets; ADO Command Text

MS-SQL 2000, Access 2003, ADO 2.8

Please, consider the SQL string in the following code:

Dim r As ADODB.Recordset
Dim r2 As ADODB.Recordset
Set r = CurrentProject.Connection.Execute( _
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactions" _
& "; SET NOCOUNT ON " _
& "SELECT * FROM [4060148Transactions]")
Set r2 = r.NextRecordset
Debug.Print r.GetString(adClipString, 1, vbTab, vbNewLine)
Debug.Print r2.GetString(adClipString, 1, vbTab, vbNewLine)
Set r = Nothing

that is:
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactions" _
& "; SET NOCOUNT ON " _
& "SELECT * FROM [4060148Transactions]"

As I understand NOCOUNT, if we used this in a stored procedure the
first SET NOCOUNT ON would be valid for the whole stored procedure and
the second SET NOCOUNT ON would be redundant.

But what about using command text?
They are both useful?
Neither is useful?
The first is, but the second isn't?

Dec 9 '05 #1
1 11029

In both cases the first SET NOCOUNT ON is all that is required.

You should really have a SET NOCOUNT OFF at the end as well in order to
reset to the default behaviour.

It is still useful to have the SET NOCOUNT ON to prevent the generation of
the row count that sql reports, especially in a multiple recordset situation
as this row count is interpreted by ADO as another recordset.
--
Terry Kreft

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
MS-SQL 2000, Access 2003, ADO 2.8

Please, consider the SQL string in the following code:

Dim r As ADODB.Recordset
Dim r2 As ADODB.Recordset
Set r = CurrentProject.Connection.Execute( _
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactions" _
& "; SET NOCOUNT ON " _
& "SELECT * FROM [4060148Transactions]")
Set r2 = r.NextRecordset
Debug.Print r.GetString(adClipString, 1, vbTab, vbNewLine)
Debug.Print r2.GetString(adClipString, 1, vbTab, vbNewLine)
Set r = Nothing

that is:
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactions" _
& "; SET NOCOUNT ON " _
& "SELECT * FROM [4060148Transactions]"

As I understand NOCOUNT, if we used this in a stored procedure the
first SET NOCOUNT ON would be valid for the whole stored procedure and
the second SET NOCOUNT ON would be redundant.

But what about using command text?
They are both useful?
Neither is useful?
The first is, but the second isn't?

Dec 9 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by James | last post: by
7 posts views Thread by Leon | last post: by
16 posts views Thread by Randy Harris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.