You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q316889
This article has been archived. It is offered "as is" and will no longer be updated.
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 "SET statistics profile on" 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 "SET statistics profile on" statement. "SET statistics profile on" is expected to be scoped to the connection. The real scope that is seen here ("SET statistics profile on") 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.
MORE INFORMATION

Steps to reproduce the behavior

  1. Create a Visual Basic .NET Windows Application project, and then add the following namespaces to the very top of the code window:
    Imports SystemImports System.DataImports System.Data.SqlClient					
  2. Add the following segment of code inside of "Public Class Form1" (just below "Inherits System.Windows.Forms.Form"), and note that you must modify the connection string appropriately for your environment:
            Dim myConnection As New SqlConnection("server=(local);database=pubs;Integrated Security=SSPI;")        Dim myReader As SqlDataReader        Dim myCommand As New SqlCommand()        Dim i As Integer					
  3. Double-click the form, and then add the following code to the form Load event:
            Try            myConnection.Open()            myCommand.CommandText = "SET statistics profile on "            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 = "SELECT * FROM Titles"            myCommand.Connection = myConnection            myReader = myCommand.ExecuteReader()            i = 0            Debug.WriteLine("====Looping through the first resultset on MyCommand=====")            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("=====Looping through the second resultset on MyCommand to get statistics======")            Do While (myReader.Read())                For i = 0 To myReader.FieldCount - 1                    Debug.WriteLine(myReader(i).ToString())                Next i            Loop            myReader.Close()            myCommand.CommandText = "select * from authors"            myCommand.Connection = myConnection            myReader = myCommand.ExecuteReader()            i = 0            Debug.WriteLine("====Looping through the first resultset on MyCommand2=====")            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("=====Looping through the second resultset on MyCommand2 to get statistics======")            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					
  4. Press F5 to run the application.
  5. On the View menu, point to Other Windows, and then click Output to examine the results.
  6. 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()					
REFERENCES
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
313590 Roadmap for ADO.NET
313483 Roadmap for ADO.NET DataAdapter objects
Properties

Article ID: 316889 - Last Review: 02/27/2014 21:11:26 - Revision: 2.6

Microsoft ADO.NET (included with the .NET Framework), Microsoft ADO.NET 2.0, Microsoft Visual Basic .NET 2002 Standard Edition, Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft Visual Basic 2005

  • kbnosurvey kbarchive kbenv kbnofix kbprb kbsqlclient kbsystemdata KB316889
Feedback