Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.

When I publish my VS project and generate the script to run against my database, VS includes the following in the script. I really don't want the script to go changing database properties, particularly the PAGE_VERIFY setting. I notice that the script doesn't set the properties back the way it found them. How do I turn this off? I have spent time in the Tools->Options dialog and I just don't see the setting.

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL 
        WITH ROLLBACK IMMEDIATE;

END
GO

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET PAGE_VERIFY NONE 
        WITH ROLLBACK IMMEDIATE;
END
GO
share|improve this question
add comment

2 Answers

I am using Visual Studio 2010 and SQL 2008 database projects, but the idea remains the same. In your solution go to the project and do the following steps:

  • right click the project -> Properties
  • Project Settings (1st tab in this setting)
  • go to section Catalog Properties file: make sure a settings file is already selected (by default there's a file \Properties\Database.sqlsettings if it wasn't previously changed)
  • click Edit for the sqlsettings file: there you'll see all the options that are deployed each time to your target database. It should look like the following snap: enter image description here

If you'd like to NOT deploy any of the project default settings, you should do the following steps:

  • go to tab Deploy
  • go to section Deployment configuration file (default it is Properties\Database.sqldeployment)
  • deselect the option "Deploy database properties". It should look something like the following snap: enter image description here

That should help you change/remove the current project settings that you're now deploying.

share|improve this answer
add comment

Most of the setting set with SET statement are limited to the connection. They affect behaviour of SQL Server for current connection only. Another connection will be opened with same values as previous one. If you need to change server settings permanently use sp_configure.

You can check every setting definition on MSDN and make sure they are connection specific.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.