cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
peakbagger66
Level 4

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

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!
Labels (1)
0 Kudos
(3) Replies
KrisVanherck
Level 6

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.
0 Kudos
nmsams
Level 7

When you paste your sql code into SQL Management Studio and run it there, does it run without error?
0 Kudos
peakbagger66
Level 4

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

thanks again!
0 Kudos