Tilfried Weissenberger

Maybe I'm asking for too much, or trying to leverage too much abstraction and cleaningness from SQLCMD-Variables, but here are my findings so far. Maybe someone can shed some light on this:

  1. MSBuild vars in SQLCMD-Vars are NOT resolved, if they are placed in the (local) .user file.
  2. Having multiple <SetVariables> blocks within .user and .dbproj file seems to cause problems to team-build. The same goes for comments between <Variable> items. I've set the value to a couple of test-variables in various places to try to understand what TeamBuild/MSBuild and VS does, but I can't seem to get a consistent result. I then created multiple <SetVariables> blocks with a comment above it to organize them a bit (yes, I use quite some, like 30-40), but then TeamBuild used the defaults from the PreDeploy-script!!

ad 1) Is this a bug or feature ;)

ad 2) what's the recommendation on using the <SetVariables> blocks I would like to set all vars in Script.PreDeployment.sql (sort of as a reference with inline-docs), then override different values in various build-flavors - but only those which differ.




Re: Visual Studio Team System - Database Professionals Problems using <SetVariables> in various places

Gert Drapers - MSFT

I am glade you are asking these question, please do not hold back.

1) This is most likely a problem on our side, at least that is not the design, but we added the variable block very late at the end, this might have been missed. Regardsless is am really favorable of .user files I would rather use configurations, this also works better in the second environment, Team Build.

2a) user vs. dbproj: my advice its to use always place them in the dbproj file, and if needed use configurations to create seperate identities

2b) TeamBuild should be using the dbproj defined variables in the same fashion the IDE does and not rely on the PreDeploy definition which is only a default value in case the variable is not defined in the project.

Let me know if this answers the question.

-GertD





Re: Visual Studio Team System - Database Professionals Problems using <SetVariables> in various places

Gert Drapers - MSFT

BTW: we will be adding a variable editior soon to help you not having to edit the XML of the project directly.

-GertD





Re: Visual Studio Team System - Database Professionals Problems using <SetVariables> in various places

Jamie Laflen MSFT

Try putting the setvar property after the import of our .targets file. If the MSBuild property is not defined when the setvar property is processed the variable is resolved as an empty string. Since the .user file is imported as part of the processing of our .targets file putting the setvar property after the import will ensure that the variables are defined.

We do not merge setvar properties C the last one defined wins. Although these look more complex than regular MSBuild properties they really just regular properties (like AutoShrink), we are taking advantage of the MSBuild support for arbitrary XML inside a property. I have not tried the XML parsing code with comments C I wrote the code to be pretty forgiving if there are unexpected elements. I will have to give it a try C do you have an example of the XML that is failing or providing inconsistent results






Re: Visual Studio Team System - Database Professionals Problems using <SetVariables> in various places

Tilfried Weissenberger

Hi Jamie, hi Gert,

thank you both for your input.

@Gert - here my answers

1) putting stuff in the .user file only relates to the "external" name and instance name of the local SQLExpress. The reason being I need to create certificates for SSB and place the public key of it in a central location. Therefore I need a "real" machine-name. thinking of it now, I could probably leverage TSQL to get the sql's machine-name. But since we used MSBuild vars in the form:

.USER file in local VS instance:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup Condition=" '$(Configuration)' == 'Default' ">
<AlwaysCreateNewDatabase>True</AlwaysCreateNewDatabase>
<TargetDatabase>Hermes_VS</TargetDatabase>
<LocalServer>L099999</LocalServer>
<LocalInstance>\SQLEXPRESS</LocalInstance>
<TargetConnectionString>Data Source=$(LocalServer)$(LocalInstance);Integrated Security=True;Pooling=False</TargetConnectionString>
<SetVariables>
<Variable Name="TESTVARWZ" Value="user_top" />
<Variable Name="ServerInstance" Value="$(LocalInstance)" />
<Variable Name="MsgrServerInstance" Value="$(LocalInstance)" />
<Variable Name="TESTVARWZ" Value="user_bottom" />
<Variable Name="TESTVARWZ1" Value="user_bottom" />
</SetVariables>
<SetVariables>
<Variable Name="TESTVARWZ" Value="user_bottom2" />
</SetVariables>
...
</Project>

.DBPROJ file in local VS instance:

...
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v8.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets" />
<PropertyGroup Condition=" '$(Configuration)' == 'Default' ">
<SetVariables>
<Variable Name="TESTVARWZ" Value="dbproj_default" />
<Variable Name="TESTVARWZ3" Value="dbproj_default" />
<Variable Name="DEPLOYFLAVOR" Value="&gt;$(Configuration)" />
</SetVariables>
<DeploymentCollationPreference>UseExistingCollations</DeploymentCollationPreference>
<AlwaysCreateNewDatabase>false</AlwaysCreateNewDatabase>
<GenerateDropsIfNotInProject>False</GenerateDropsIfNotInProject>
<BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
<TreatWarningsAsErrors>False</TreatWarningsAsErrors>
<PerformDatabaseBackup>False</PerformDatabaseBackup>
<SingleUserMode>False</SingleUserMode>
<UseFuzzyMatchForColumns>False</UseFuzzyMatchForColumns>
<SuppressWarnings>
</SuppressWarnings>
<OutputPath>.\sql\</OutputPath>
</PropertyGroup>
...

the beginning of my Script.PreDeployment.sql:

:setvar DEPLOYFLAVOR "VS"
...
:setvar TESTVARWZ "PreDeployScript"
:setvar TESTVARWZ1 "PreDeployScript"
:setvar TESTVARWZ2 "PreDeployScript"
:setvar TESTVARWZ3 "PreDeployScript"

VS is not able to correctly build the Variable's values.

2a) yes, providing I can figure-out the name of the local sql-server from TSQL and not have to rely on a SQLCMD Var, I will go for that!

2b) TeamBuild seems to work as expected - it's the VS IDE that's giving me headaches, as it does not seem to really fully parse my MSBuild vars AND AFTER THAT the SetVariables, BEFORE building and deploying the script.

which leads me to the answer for Jamie: Above, you see my .user and .dbproj file with some TESTVARs in it. Here's the result which is shown to me when running DEPLOY in the local VS:

DEPLOYFLAVOR=VS
TESTVARWZ=user_bottom2
TESTVARWZ1=PreDeployScript
TESTVARWZ2=PreDeployScript
TESTVARWZ3=PreDeployScript

As you can see, the DEPLOYFLAVOR is not even being read from the .dbproj file. TESTVARWZ IS being read from the .user-file, but the other's still have the same value from the PreDeploy script and have not "received" the setting from the .dbproj file!!

I am also not meaning to "merge" variable-values, but the collections. I believe the MSBuild style of handling multiple ItemGroups with the same data is to merge. I am not sure what one Property (SetVariables) in multiple locations does. I tried finding out in the .user file (see TESTVARWZ) and it seems as though in this case, the whole last property "wins". I am not sure why you are using a Property for the variables. It would make much more sense using an ITEM (ItemGroup).






Re: Visual Studio Team System - Database Professionals Problems using <SetVariables> in various places

Jamie Laflen MSFT

Yes, since it is a property the last definition of the property is the one used by MSBuild. We chose to implement these as a property because it is less awkward than using file definitions (item groups) to describe the set variables.

Yes, there is an issue with the evaluation of properties when executed from within the IDE C as you eluded to, it is different than what you would expect executing the same build from the command line. For the time being, I would recommend not modifying the .user file and keep the configuration in the .dbproj file. This has the added benefit of working the same way for all members of your development team (since .user files are not checked in).