[Ed. - Question resolved. See Update at the end of the post]
I am using Visual Studio Team Suite with the Database Professionals add on (”Data Dude”) - with SR1 installed. However, when I build the database project, I am not getting the expected results in the resulting omnibus .SQL script. As a result, I have to build the project, then manually edit the .SQL file, and deploy the script from the command line using SQLCMD.EXE — all of which is preventing me from automatically building & deploying with a single-click from the VS IDE (which I want to do).
Here are how I’ve set up the variables in my database project (click the image to enlarge):
Building the project creates a script with no “setvar: ” statements for the variables I’ve added to the project:
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName "SIDW_LOCALDEV"
:setvar PrimaryFilePhysicalName "F:\SQL2005 DATA\SIDW_LOCALDEV.mdf"
:setvar PrimaryLogFilePhysicalName "F:\SQL2005 DATA\SIDW_LOCALDEV_log.ldf"
I expected (but did not see) the following lines as well:
:setvar SQLUserDWETL "DevDWETL"
:setvar SQLUserDWAudit "DevDWAudit"
:setvar SQLUserDWReader "DevDWReader"
These values are used in other scripts, such as this line in the “Post-Deployment” script “Permissions.sql”
GRANT SELECT ON OBJECT::[DW].[vDailyCalendar_dim] TO [$(SQLUserDWReader)]
GO
Are my expectations out of line, or should the “Build” command for the database project output a SQL file that contains the setvar statements?
Note - I’ve created a thread on microsoft’s forums to help track down a resolution. If one surfaces, I’ll be sure to note it here.
UPDATED - Turns out my expectations were incorrect. See the thread for the details. Basically if you deploy from the IDE, DataDude will pass the variable values to the deployment and you’re good to go. If you want to use the SQL script generated by a build, you’ll have to provide them there (basically doing what Data Dude does within the IDE). Many thanks to Gert Drapes for the prompt response.
