Microsoft KB Archive/316889

= SET statements do not have connection-wide ccope with ExecuteReader of SQLCommand class =

Article ID: 316889

Article Last Modified on 12/6/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 2.0
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic 2005

-



This article was previously published under Q316889



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.SqlClient



SYMPTOMS
When you are using SET statements that are followed by resultsets that are returning statements such as SELECT, existing Microsoft SQL tools return non-resultset queries. This is not true when you are using the SqlCommand.ExecuteReader method. When SET statements are used with SqlCommand.ExecuteReader, the SQL statements that contain SET statements such as &quot;SET statistics profile on&quot; return results from the set statements. The preceding SQL statements (such as SELECT * FROM TableA) that are executed in the same connection do not see results from the &quot;SET statistics profile on&quot; statement. &quot;SET statistics profile on&quot; is expected to be scoped to the connection. The real scope that is seen here (&quot;SET statistics profile on&quot;) is scoped to the statement.



CAUSE
The ExecuteReader method of the SqlCommand object does not support the use of SET statements. When you are using SET statements, they are only scoped to the statement and not to the connection.



RESOLUTION
When you are executing queries that do not return results, use the ExecuteNonQuery method because this method has a connection-wide scope.   Execute             Command                      Active SET statements --         --           -   ExecuteReader       Set xxx SELECT ... xxx ExecuteNonQuery    Set yyy UPDATE ... yyy ExecuteReader      Set zzz SELECT ... yyy zzz ExecuteNonQuery    UPDATE ... yyy The current connection would have yyy set.



STATUS
This behavior is by design.



Steps to reproduce the behavior
  Create a Visual Basic .NET Windows Application project, and then add the following namespaces to the very top of the code window: Imports System Imports System.Data Imports System.Data.SqlClient   Add the following segment of code inside of &quot;Public Class Form1&quot; (just below &quot;Inherits System.Windows.Forms.Form&quot;), and note that you must modify the connection string appropriately for your environment: Dim myConnection As New SqlConnection(&quot;server=(local);database=pubs;Integrated Security=SSPI;&quot;) Dim myReader As SqlDataReader Dim myCommand As New SqlCommand Dim i As Integer   Double-click the form, and then add the following code to the form Load event: Try myConnection.Open myCommand.CommandText = &quot;SET statistics profile on &quot; myCommand.Connection = myConnection myReader = myCommand.ExecuteReader 'use ExecuteNonQuery as a workaround to set statistics profile on for the connection 'myCommand.ExecuteNonQuery myReader.Close

myCommand.CommandText = &quot;SELECT * FROM Titles&quot; myCommand.Connection = myConnection myReader = myCommand.ExecuteReader

i = 0 Debug.WriteLine(&quot;====Looping through the first resultset on MyCommand=====&quot;) Do While (myReader.Read) For i = 0 To myReader.FieldCount - 1 Debug.WriteLine(myReader(i).ToString) Next i           Loop myReader.NextResult

i = 0 'set statistics profile will not be set here with ExecuteReader Debug.WriteLine(&quot;=====Looping through the second resultset on MyCommand to get statistics======&quot;) Do While (myReader.Read) For i = 0 To myReader.FieldCount - 1 Debug.WriteLine(myReader(i).ToString) Next i           Loop myReader.Close

myCommand.CommandText = &quot;select * from authors&quot; myCommand.Connection = myConnection myReader = myCommand.ExecuteReader

i = 0 Debug.WriteLine(&quot;====Looping through the first resultset on MyCommand2=====&quot;) Do While (myReader.Read) For i = 0 To myReader.FieldCount - 1 Debug.WriteLine(myReader(i).ToString) Next i           Loop myReader.NextResult

'set statistic will not be set here with ExecuteReader 'when using ExecuteNonQuery you will see that statistics profile is set within the connection scope Debug.WriteLine(&quot;=====Looping through the second resultset on MyCommand2 to get statistics======&quot;) i = 0 Do While (myReader.Read) For i = 0 To myReader.FieldCount - 1 Debug.WriteLine(myReader(i).ToString) Next i           Loop myReader.Close

Catch e1 As Exception Dim errmessage = e1.ToString MessageBox.Show(errmessage) Finally myConnection.Close End Try  Press F5 to run the application.</li> On the View menu, point to Other Windows, and then click Output to examine the results.</li>  To see the workaround, comment or uncomment the following in the first block of code. Comment out: myReader = myCommand.ExecuteReader myReader.Close Uncomment: 'myCommand.ExecuteNonQuery </li></ol>

<div class="references_section">