SET NOCOUNT ON; Multiple Recordsets; ADO Command Text

[Lyle Fairfield]

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?

[Terry Kreft]

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?