How can I manage different database build reference paths between TFS and my local machine?

Question

I have two database projects DB_A and DB_B. Project DB_A references DB_B.

I added a database reference by pointing to DB_B.dbschema at

C:\SourceParent\DB Projects\DB_B\sql\debug\DB_B.dbschema

When I build project DB_A on my local machine it works fine.

Now I want to build this on my TFS build server, but I get the following error

File D:\Builds\SourceParent\Build_Name\DB_B\sql\debug\DB_B.dbschema does not exist

I have a build for DB_B on my build server, but it's at a different path (the build definition doesn't necessarily match the project name).

I think I could add a build event in my build definition to copy the .dbschema file to the correct location on the server, but I'd rather not do this. (This would require a special build template for each build definition.)

Is there some way I can make the TFS build point to a different location? Or some other nice way I can accomplish this?

Answer 1

I solved this by creating an environment variable $(DB_SCHEMAS) pointing to a directory on my machine (e.g. C:\dbschemas).

Then I added a post build event in project DB_B to copy DB_B.dbschema to $(DB_SCHEMAS).

I then added a database reference in project DB_A pointing to $(DB_SCHEMAS)\DB_B.dbschema. Note that Visual Studio will add this reference using a relative path. To fix this, I unloaded the project in Solution Explorer, and edited the hint path to use the $(DB_SCHEMAS) environment variable.

Now I just added this environment variable on the TFS server and it will work as expected. Building project DB_B copies its .dbschema file to $(DB_SCHEMAS) (wherever that may be) and then building project DB_A references this scheme.

The only downside is that my teammates all have to add this environment variable on their machines as well, otherwise they can't build locally.