How can I test if MARS is enabeled from code?

[Gaute]

I have a C++/ADO program using SQL Server 2005.

I want my program to behave differently when MARS is enabled (decided by
connection string/provider).
Is there some query I can do to find out if MARS is enabled or not?

MARS: http://technet.microsoft.com/en-us/library/ms345109.aspx

Best regards from Gaute

[Anith Sen]

Since the MARS enabling is usually done via connect strings, you cannot
directly check it via the same SQL query. Simply use two different connect
strings one with MultipleActiveResultSets options turned on.

Since you are using a .NET language you might be able to trap the exception
while running the sqlreader on the command object. If MARS is enabled, you
might get an exception stating "There is already an open DataReader
associated with this command.... ". that you can capture using a try..catch
block.

--
Anith

[Gaute]

Thank you, but I was hoping for a solution like:
select xxx from sys.dm_exec_connections or
select yyy from sys.dm_exec_sessions or
sp_zzz

Is there really no such way to check this connection parameter without
having access to the connection string?

Best regards from Gaute

[Charles Wang[MSFT]]

Hi Gaute,
Per my test, if MARS is enabled, when you open a connection, you can find
an additional row in sys.dm_exec_connections. The additional row is easily
identified since it has special values in net_transport, protocol_version,
endpoint_id, and parent_connection_id. You can use the following query to
check if MARS is enabled after you open a connection:
IF EXISTS(SELECT * FROM sys.dm_exec_connections WHERE
net_transport='Session' and parent_connection_id is not null and
connect_time>@specific_time)
PRINT 'MARS is enabled!'
ELSE
PRINT 'MARS is not enabled!'

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Gaute]

Thank you very much!