how to get current connection properties in sql server 2005

• deba_20032004

  Default port

  Points: 1476

  More actions

  May 28, 2011 at 4:46 am

  #100382

  Dear Sir,

  I want to get all properties of current connection in sql server 2005 using sql command not winzard but how to get it. please help me

  thanking you

  debasis

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  May 28, 2011 at 5:59 am

  #1331342

  Suggest reading this:

  http://sankarreddy.com/2010/02/get-the-server-level-default-connection-properties-for-sql-server-using-t-sql/

  Above article contains:

  CREATE TABLE #ConnectionOptions (property VARCHAR(100), value INT)

  INSERT #ConnectionOptions

  SELECT 'implicit transactions', 2 UNION ALL

  SELECT 'cursor close on commit', 4 UNION ALL

  SELECT 'ansi warnings', 8 UNION ALL

  SELECT 'ansi padding', 16 UNION ALL

  SELECT 'ansi nulls', 32 UNION ALL

  SELECT 'arithmetic abort', 64 UNION ALL

  SELECT 'arithmetic ignore', 128 UNION ALL

  SELECT 'quoted identifier', 256 UNION ALL

  SELECT 'no count', 512 UNION ALL

  SELECT 'ansi null default on', 1024 UNION ALL

  SELECT 'ansi null default off', 2048 UNION ALL

  SELECT 'concat null yields null', 4096 UNION ALL

  SELECT 'numeric round abort', 8192 UNION ALL

  SELECT 'xact_abort on', 16384

  --define the table structure

  CREATE TABLE #UserOptions ( name VARCHAR(100), minimum INT, maximum INT, config_vale INT, run_value INT)

  --Retrieve the data

  INSERT #UserOptions

  EXEC sp_configure 'User options'

  DECLARE @currentvalue INT

  SELECT @currentvalue = run_value FROM #UserOptions

  SELECT property, CASE WHEN (@currentvalue & value) = value

  THEN 'has been SET'

  ELSE 'NOT SET'

  END CurrentValue

  FROM #ConnectionOptions

  To further understand connection properties read:

  http://www.connectionstrings.com/Articles/Show/all-sql-server-connection-string-keywords

  Besides the code the article is an excellent read with additional code, I strongly suggest reading it, and of course testing the code on a non production system before implimenting in production.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]