Article ID: 316889 - View products that this article applies to.
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:
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.
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.
When you are executing queries that do not return results, use the ExecuteNonQuery method because this method has a connection-wide scope.
The current connection would have yyy set.
Execute Command Active SET statements ---------- ------------------ --------------------- ExecuteReader Set xxx SELECT ... xxx ExecuteNonQuery Set yyy UPDATE ... yyy ExecuteReader Set zzz SELECT ... yyy zzz ExecuteNonQuery UPDATE ... yyy
Steps to reproduce the behavior
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/313590/ )Roadmap for ADO.NET
(https://support.microsoft.com/kb/313483/ )Roadmap for ADO.NET DataAdapter objects
Article ID: 316889 - Last Review: February 27, 2014 - Revision: 2.6