VS2010 DB project deployment error SQL01268 Directory search error

Question

I have deployed a VS2010 database deployment to my build server on which SQL Server 2008 R2 is installed. Here's the output that matters:

Creating Acme.Database ...

D: \ TeamCity \ BuildAgent \ Work \ da2fc5ebd5805d44 \ src \ Acme.Database \ sql \ Acme.Database.sql (30.0): SQL01268 error: .Net SqlClient data provider: Msg 5133, Level 16, State 1, Line 1 The directory search for the file "C: \ Program Files \ Microsoft SQL Server ** MSSQL10.MSSQLSERVER ** \ MSSQL \ DATA \ Acme_Database_log.ldf" failed with an 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): SQL01268 error: .Net SqlClient data provider: Msg 1802, Level 16, State 1, Line 1 CREATE DATABASE failed. Some file names cannot be created. Check related errors.

An error occurred while executing a batch.

Actual way here:

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

The only link to the path that I could find in the database project was in the root folder, a file with the name:

PRIMARY.Acme_Database.sqlfile.sql

It includes the ALTER DATABASE statement, which I changed to use the _50 path, but that did not fix the problem.

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

Answer 1

It turns out that if you specify "file" in "Schema Objects", "Database Level Objects", "Storage \ Files" (use "Add Item", "File"), the values ​​specified in the path override those that were viewed from the server. If you must specify them, use the variables from sqlcmdvariables $ (DefaultdataPath) $ (Databasename) to populate them.

Answer 2

Take a look at the default path on the server itself.

Screencast: http://screencast.com/t/OWM5ODFjZj

Perhaps the problem is that you do not have the specified path.

[Update] The reason I propose this is because during the creation of the deployment script, if you did not specify the parameters in the tab "Project Settings →" Deployment, DefaultDataPath is viewed from the target server path.

Answer 3

I don't know anything about TeamCity, so that might be a dumb question, but how do you do the deployment? It looks like the MSBuild deployment task for the database project can take a property that gives the data path:

http://www.codewrecks.com/blog/index.php/2009/10/06/deploy-a-database-project-with-tfs-build/

Can you set the path this way?

Answer 4

I had the same error, but a different solution. Everything was correct for me (as far as I can tell), but the generated script deployment sets the path variables before the databasename variable, which leads to a reference to a variable that did not exist. I updated Database.sqlcmdvars to copy the value of the database name - not the best practice, but a sufficient workaround for my needs.