VS2010 DB Project Deploy Error SQL01268 Directory Lookup Failed

Question

My VS2010 database deploy is failing on my build server, which has SQL Server 2008 R2 installed on it. Here's the output that's relevant:

Creating Acme.Database...

D:\TeamCity\BuildAgent\Work\da2fc5ebd5805d44\src\Acme.Database\sql\Acme.Database.sql(30,0): Error SQL01268: .Net SqlClient Data Provider: Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:\Program Files\Microsoft SQL Server**MSSQL10.MSSQLSERVER**\MSSQL\DATA\Acme_Database_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).

D:\TeamCity\BuildAgent\Work\da2fc5ebd5805d44\src\Acme.Database\sql\Acme.Database.sql(30,0): Error SQL01268: .Net SqlClient Data Provider: Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

An error occurred while the batch was being executed.

The actual path is here:

C:\Program Files\Microsoft SQL Server**MSSQL10_50.MSSQLSERVER**\MSSQL\DATA\Acme_Database.mdf

The only reference to the path I could find in the database project was in the root folder, a file called:

PRIMARY.Acme_Database.sqlfile.sql

It includes an ALTER DATABASE statement that I changed to use the _50 path, but this did not fix the issue.

I'm not sure why the db project needs to know where the actual database MDF file is located in any case - I would have thought it could just connect to it vis TSQL, but in any case I can't get the project to deploy with this error and would appreciate any help.

Answer 1

Turns out if you specify a "file" in Schema Objects\Database Level Objects\Storage\Files (use Add Item, File) the values specified in the path override those looked up from the Server. If you must specify them use the variables from sqlcmdvariables $(DefaultdataPath)$(Databasename) to fill things out.