views:

57

answers:

3

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.

+1  A: 

I don't know anything about TeamCity really so this may be a stupid question, but how are you doing the deployment? It looks like the MSBuild deploy task for a DB 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 that way?

MikeBaz
I don't have it wired up to TeamCity yet - I've been opening it up in VS2010 and right-clicking in solution explorer and selecting Deploy. I'll give the MSBuild approach a try, though, thanks!
ssmith
Tried this, but setting the path via command line / msbuild params didn't work because the path was being overridden by presence of .sqlfile.sql files (see my comment on the correct answer)
ssmith
+1  A: 

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

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

The fact that you don't have a path specified could likely be the issue.

[Update] The reason I suggest this is that during the creation of the deployment script, if you have not specified the settings in the Project Settings -> Deployment tab, the DefaultDataPath is looked up from the target server's path.

Ryan Cromwell
+1  A: 

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.

Ryan Cromwell
Not sure it matters where they are - I had Acme.sqlfile.sql and Acme_log.sqlfile.sql in the root of my dbproj project (for some reason) and these had paths in them that were overriding what the server was saying. Deleted them and deploy now works fine either from build server or from dev machine. Thanks, you went above and beyond helping to figure this one out!
ssmith