How to generate a clean sql script using visual studio database project?

user_1281730 0
Viewed 3326

I have created an SQL Server Database Project using Visual Studio 2013. The build and Publish work fine (all changes from project are executed on database) but when I choose 'Generate Script' (instead of Publish) an .sql file is generated but it cannot be run directly (without modifying it) in SQL Server Management Studio because some of the following lines are not part of SQL language (those which starts with ":"):

GO
:setvar DatabaseName "Test_Project"
:setvar DefaultFilePrefix "Test_Project"
:setvar DefaultDataPath "<default path>"
:setvar DefaultLogPath "<default log path>"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];

How can generate a "clean" SQL file when I press Generate Script button? If it is not possible how can I transform the result into a SQL file which can be interpreted by SQL Server Management Studio?

visual-studio-2013 sql-server
1 Answers

It's possible to pass the sqlcmd script into sqlcmd.exe with parameters. There's an example of this in this Microsoft blog post.

As far as I'm aware there's no way of outputting the sql file with the supplied parameters substituted in.

user_1673640
Related