By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

SET NOCOUNT ON; Multiple Recordsets; ADO Command Text

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a

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.