Reset connection level settings to DB defaults

Question

Reset connection level settings to DB defaults

Chris-410445

Newbie

Points: 7
More actions
May 7, 2007 at 5:08 pm

#172356

A little background… we have a tool that combines all the change scripts when we get ready to update our staging and production databases to one file.
The problem I'm having is that one script might change an option, say ANSI_NULLS, while a later script expects the database defaults. The change script files are created by developers, so please no responses about how to use 3rd party scripting tools. Getting all database default and appending commands to reset them one by one is not an option because the tool that combines the scripts doesn't know which DB the scripts are for and different scripts may USE different DBs anyway.
What I'm looking for is a command to call that will restore the connection level settings back to the default database settings. Any ideas?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

Try reading a little on sp_configure and command RECONFIGURE.

You may want to considere adding a USE [db_name] at the top of each script if the are db specific, if they are not then create a mechanism to loop thru all the db's involved.

Chris-410445 Newbie Points: 7 More actions · Answer 2

Thanks for the response.

As far as I understand sp_configure is used for changing server level settings, I just want to restore the connection level settings (ANSI_NULLS etc) to the defaults set for the current database. If there is a way to use sp_configure to do this could you please provide a link or a brief example?

Camilo Torres SSCrazy Points: 2034 More actions · Answer 3

Your other option is ALTER DATABASE database_name (a lot to read here)

The sys.databases catalog has all the options values. What about getting a snap-shot of those values... and use them later for restore.

Chris-410445 Newbie Points: 7 More actions · Answer 4

I don't want to make any database level changes, just reset the options for the connection so I don't think any ALTER DATABASE commands would help.

I'll take a look at the sys tables to get the db defaults, I think this is going to be the best way to go about it. I was really holding out for a command that would set the session level options back to the database settings.

Thanks