Using variables in Script (.sql) under Database Project

Question

Hi

Below is the scripting lines I am using in my FreshData.sql which is then used from Script.PostDeployment.sql on SQLExpress

FreshData.sql

-- =============================================
-- Script Template
-- =============================================
--:setvar MediaFilesId NEWID()
:setvar MediaFilesId "3C2C6620-84FF-4e22-82CE-C0F91A5AECE5"

INSERT INTO zroort_Categories(Id, ParentId, Category) VALUES('$(MediaFilesId)', NULL, 'Media Files')
INSERT INTO zroort_Categories(Id, ParentId, Category) VALUES(NEWID(), '$(MediaFilesId)', 'Audio Files')
INSERT INTO zroort_Categories(Id, ParentId, Category) VALUES(NEWID(), '$(MediaFilesId)', 'Video Files')

Script.PostDeployment.sql

:r .\Permissions.sql

:r .\RoleMemberships.sql

:r .\RulesAndDefaults.sql

:r .\DatabaseObjectOptions.sql

:r .\Signatures.sql

:r .\FreshData.sql

Following is the error statement when viewed in the output window:

C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\DatabaseSolutions\Zroort\Scripts\Post-Deployment\FreshData.sql(5,0): Error TSD164: Syntax checking failed : Incorrect syntax near :.

Please let me know , where I am making the mistake. And also let me know if the following statement permissible in the script file

:setvar MediaFilesId NEWID()

I'll be very much thankful for an early response.

Answer 1

I have found the reason.

In the visual studio, make sure that Transact SQL Editor tool bar is loaded. Click the SQLCMD MODE button on that tool bar. The script lines will be highlighted with dark gray color and errors will be removed.