snippetsqlMinor
How do I turn off ALTER DATABASE script output in VS 2012?
Viewed 0 times
turnscript2012outputdatabasehowalteroff
Problem
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.
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
GOSolution
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:
If you'd like to NOT deploy any of the project default settings, you should do the following steps:
That should help you change/remove the current project settings that you're now deploying.
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:
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:
That should help you change/remove the current project settings that you're now deploying.
Context
StackExchange Database Administrators Q#42805, answer score: 5
Revisions (0)
No revisions yet.