Using Cross-Database Reference and Team Build using VS2005 SR1
I have setup a db pro project that utilizes a Cross-Database Reference. This all works fine while in the IDE and performing a build. When I Build the project the resulting .sql file has the following inside it:
...
:setvar STR_DB "STR"
...
Create Procedure [xyz}
select distinct
PMST.CML#, --servicing_id
from [$(STR_DB)].dbo.PMASTR1 PMST
...
All is great at this point. But when I check this project into Team and then subsequently issue a build for Team, the resulting .sql file is missing the setvar command for STR_DB entirely.
I have tried several approaches to get this to function under Team Build and nothing seems to work. I have tried all the web articles that describe editing your .dbproj file. I have tried moving the setvariables elements to the first property group in the .dbproj file. I saw a similar post on this site that indicated that :setvar is not set until deploy (that is ok because I am just looking for the :setvar to be included in the build .sql file like it is under the IDE build while building from Team).
I would be very appreciative if someone could let me know what I missing here? Or point me in a direction (other than gertd's blog about cross database references. Been there and that does not detail the team build steps) on how to functionally get cross database references to work under a team build scenario. Thank you.
excerpt from my .dbproj file
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<Configuration Condition=" '$(Configuration)' == '' ">Default</Configuration>
<Name>"DatabaseProject"</Name>
<SchemaVersion>2.0</SchemaVersion>
<ProjectGuid>{05916356-7a25-4aac-8500-0b14ffb5bea6}</ProjectGuid>
<ShowWizard>True</ShowWizard>
<OutputType>Database</OutputType>
<DBProduct>SQL Server 2005</DBProduct>
<RootPath>
</RootPath>
<ArithAbort>True</ArithAbort>
<NumericRoundAbort>False</NumericRoundAbort>
<AnsiNulls>True</AnsiNulls>
<ConcatNullYieldsNull>True</ConcatNullYieldsNull>
<AnsiPadding>True</AnsiPadding>
<AnsiWarnings>True</AnsiWarnings>
<QuotedIdentifier>True</QuotedIdentifier>
<DefaultSchemaForUnqualifiedObjects>dbo</DefaultSchemaForUnqualifiedObjects>
<EnableFullTextSearch>True</EnableFullTextSearch>
<EnableCLRIntegration>False</EnableCLRIntegration>
<AutoUpdateStatisticsAsynchronously>True</AutoUpdateStatisticsAsynchronously>
....
<SetVariables>
<Variable Name="STR_DB" Value="STR" />
</SetVariables>
....
<ItemGroup>
<Reference Include="STR_Ref, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null,ReferencePath=E:\Visual Studio 2005\Projects\Abs Website\Database\CMS\CMS\sql\STR_Ref.dbmeta">
<Name>STR_Ref</Name>
<AssemblyName>STR_Ref.dbmeta</AssemblyName>
<HintPath>sql\STR_Ref.dbmeta</HintPath>
<Private>True</Private>
<DatabaseProjectNodeReference>True</DatabaseProjectNodeReference>
<ServerVariableName>
</ServerVariableName>
<ServerVariableValue>
</ServerVariableValue>
<DatabaseVariableName>%24%28STR_DB%29</DatabaseVariableName>
<DatabaseVariableValue>STR</DatabaseVariableValue>
</Reference>
</ItemGroup>
</Project>
I found a solution (work around) here.
http://topxml.com/rbnews/XML/re-69920_DataDude-Annoyance-13--Project-variables-omitted-from-output-script-.aspx
It is was very obscure to find so I am passing along the reference here. I agree whole heartedily with this individual. This really is a significant need and should be fixed in DbPro. Honestely, I do not agree with the arguments made in several post about the difference between sqlcmd and deploying, etc, etc, etc.
Just put an option in DbPro that will simply emit the variables from the variable page into the .sql files under a build condition. It that violates some misguide thinking of how to interact with sqlcmd files then disclaim it with a warning but do not eliminate the ability to tie variables into the .sql file like this (my two cents).
When I tried the post build event approach under TFSBuild, it locked up on me. Odd thing is the post build event approach worked fine under an ide Build. Anyhow, it is probably because that directory structures are completely different under a TFSBuild scenario compared to IDE Build. So I adapted this solution slighty. I removed using a post build event to execute the vbs script. And in the TFSBuild.Proj file I added an after compile copy & exec task. Excerpt of the edit to TFSBuild.Proj shown here (not my solution is under \database\CMS\CMS below TFS build's solution root):
....
<Target Name="AfterCompile">
<Copy SourceFiles="$(SolutionRoot)\Database\CMS\CMS\post_build.vbs" DestinationFolder="$(OutDir)" />
<Exec WorkingDirectory="$(OutDir)" Command=""$(OutDir)post_build.vbs"" />
</Target>
</Project>
I also rewrote the vbs script for better performance. This version does not concatenate the .sql file into a string variable. Instead it temporarily renames the .sql file and creates a new empty .sql file. As it read line by line it also writes to the newly created empty .sql. At the correct spot it writes in the variable lines. And then finishes writing the remainder of the file. In the end it cleans up the .tmp file.
On Error Resume Next
Dim fso, ts, line, x, y, script, variables, filepath
Const ForReading = 1, ForWriting = 2, ForAppending = 8
' NOTE: This name must match the DataDude "Build Output File Name"
' in project properties.
filepath = "CMS.(local).CMS.sql"
filepath_Orig = filepath + ".tmp"
' Append your additional variables here, one per line.
' Include blank lines as desired for spacing.
variables = Array( _
"" _
,"-- Additional variables added by Post_Build.vbs" _
,":setvar STR_DB ""STR""")
Set fso = CreateObject("Scripting.FileSystemObject")
'rename file with .tmp
if fso.FileExists(filepath_Orig) then
fso.DeleteFile filepath_Orig,True
end if
if fso.FileExists(filepath) then
fso.MoveFile filepath , filepath_Orig
fso.CreateTextFile filepath,true
'read one file and write to the other file
Set ts = fso.OpenTextFile(filepath_Orig, ForReading)
Set ts2 = fso.OpenTextFile(filepath, ForWriting)
x = 0
Do Until ts.AtEndOfStream
line = ts.ReadLine
ts2.writeline(line)
x = x + 1
If x = 7 Then ' Right after the last existing :setvar.
' Append variable definitions.
For Each line In variables
ts2.writeline(line & vbCrLf)
Next
End If
'script = script & line & vbCrLf
Loop
ts.Close
ts2.Close
Set ts = Nothing
Set ts2 = Nothing
fso.DeleteFile filepath_Orig
else
fso.CreateTextFile "post_build_output.text"
Set ts2 = fso.OpenTextFile("post_build_output.text", ForWriting)
ts2.writeline ("Could not find " + filepath)
end if
Set fso = Nothing
