Visual Studio 2012 Database Deployments with variable substitution -

Visual Studio 2012 Database Deployments with variable substitution -

i'm in process of setting deployments in visual studio several related databases first time. have 3 databases i'll phone call database1, database2, , database3. these databases have multiple levels of deployment (development, test, staging, , production).

so database1 have 4 publish configurations:

database1_todevelopment.publish.xml database1_totest.publish.xml database1_tostaging.publish.xml database1_toproduction.publish.xml

production housed on it's own server, , database called "database1_production". test , staging share server , databases called "database1_test" , "database1_staging". development on it's own server, , called "database1_development".

my problem this: synonyms.

this reply useful, not quite

database1_development may contain this:

create synonym [dbo].[syn_mytable] [database2_development].[dbo].[mytable]

i can't take synonym , move development test. need substitute "_development" part "_test". , that's based on deployment.

i guess want know how variable substitution based on deployment.

of course, partially answered own question. found image on wanted no explanation of why dialog has section (no explanation, no link). dialog looks familiar sqlcmd variable part isn't there in vs 2012.

turns out, portion of dialog won't show unless edit xml file publish profile add together this:

<itemgroup> <sqlcmdvariable include="dblevel"> <value>dev</value> </sqlcmdvariable> </itemgroup>

i found through this so question, , jamie thomson's blog post.

it magically appears when next time publish dialog opened again.

then in synonym definition can say:

create synonym [dbo].[syn_mytable] [database2_$(dblevel)].[dbo].[mytable]

which wanted, mostly.

a workaround solution utilize executesql or executeddl task msbuild community tasks lib or other task library create environment - specific aliases part of build process (e.g. post-build events). way can store db names msbuild properties.

visual-studio-2012 deployment database-project