CODEWITHSUNDEEP
.netsql-server
harley.333
harley.333

Reputation: 3704

In SQL Server what's the difference between SESSIONPROPERTY and @@Options?

In my .Net application, if I execute the following, the values don't match:

SELECT 
    SESSIONPROPERTY('ARITHABORT'), 
    CASE WHEN (@@OPTIONS & 64) = 64 THEN 1 ELSE 0 END

I was under the impression that SESSIONPROPERTY and @@OPTIONS would generate the same results. In my case, SESSIONPROPERTY is returning 0 and @@OPTIONS is returning 1.

Which is more accurate, and why?

Upvotes: 3

Views: 177

Answers (1)

Clay
Clay

Reputation: 5084

I agree with Jeroen...it's not .Net specific. The @@OPTIONS are reflecting what comes in on the connection, while SESSIONPROPERTY does not. Open a trace to see what's happening on the connection, and you'll see what a .Net default connection does:

enter image description here

I've done the same set ahead of running the commands:

alter database [thedatabase] set arithabort on with no_wait

...and my test harness is:

using ( var conn = new SqlConnection( "Data Source=.;Initial Catalog=thedatabase;Integrated Security=True" ) )
{
  conn.Open( );
  using ( var cmd = conn.CreateCommand( ) )
  {
    cmd.CommandText = "SELECT SESSIONPROPERTY( 'ARITHABORT' ), CASE WHEN(@@OPTIONS &64) = 64 THEN 1 ELSE 0 END";
    cmd.CommandType = System.Data.CommandType.Text;
    using ( var reader = cmd.ExecuteReader( ) )
    {
      while ( reader.Read( ) )
      {
        Console.WriteLine( "{0},{1}", reader[ 0 ], reader[ 1 ] );
      }
    }
  }
}

One wonders if it's a bug...or just squirrely doc.

Upvotes: 1

Related Questions