Newbie SQL question - script fails when using SQL Scripts in install designer

[peakbagger66]

I have a sql script that creates a database. I imported it into SQL Scripts. SQL I have the script written like this:

/*
Deployment script for Con.ContentServer.DB
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF;


GO

:setvar DatabaseName "Con.ContentServer.DB"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10.CON\MSSQL\DATA"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL10.CON\MSSQL\DATA"

GO
USE [master]

GO
:on error exit

When I run the installer, it fails:

Error 27506. Error executing SQL Script con.contentserver.db.sql. Line 15. Incorrect syntax near ':'. (102)

I am not sure exactly where it is failing - Line 15 takes me to the GO directive after the :setvars. if I ignore the comments, it takes me to the GO directive just before the ':on error exit'. Am I better off running this another way?

Any help would be appreciated!

[KrisVanherck]

GO is the line where all the script before is actually executed so errors will always come on those lines. Comments are also counted in the line numbers so it's the GO after the setvar.

Also if i'm not mistaken (anyone can correct me here) the :setvar is actually a sqlcmd command and not a T-SQL command, it will not be accepted by installshield as it only implements T-SQL. Try using DECLARE instead.

[nmsams]

When you paste your sql code into SQL Management Studio and run it there, does it run without error?

[peakbagger66]

Thanks guys, I made the script t-sql friendly and all is well.

thanks again!