Multiple Active Result Sets
December 31, 2012 Leave a comment
Multiple Active Result Sets (MARS) is a feature introduced in SQL Server 2005. This feature works with SQL Server 2005 or later to allow the execution of multiple batches on a single connection. To access multiple result sets on previous versions of SQL Server using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object. However, when MARS is enabled for use with SQL Server 2005, each command object used adds a session to the connection.
The MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string. “True” is the only valid value for enabling MARS. The following is an example of the connection string
Data Source=MSSQL1;Initial Catalog=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=True
The above connection string uses the sample AdventureWorks database. The connection string above assume that the database is installed on a server named MSSQL1. You can modify the connection string as necessary for your environment.