Database deploy failing because sqlcmdvars file putting in a non-existent path and causing an error (The variable Path2 cannot be found)

Question

I have 2 db's in a solution, one server project and one database project.  The Project names are not the same as the target db's (don't know if that is relevant).  I deploy and the server project deploys fine, but the database project builds fine and fails the deployment. The first warning is "The source's object [XXX_Log] is different than the target's version of the object but the target object will not be updated." Then the error is "Error TSD01268: SQL Execution error: A fatal error occurred. The variable Path2 could not be found."  I look at the SQLCMDVARS file and two variables are put in there (not by me): Path1 = I:\MSSQL and Path2 = I:\MSSQL. I don't have an I: drive mapped on my system and it is causing the build to fail.

When I do a "Create a deployment script" only, it passes. When I try to create a deployment script and deploy to a db, it fails with this error.

Signed,
Pulling my hair out. (a.k.a. Brian)

Answer 1

Hi Brian,

Please stop pulling out your hair. ;)

The variables Path1 and Path2 come from the server your reverse engineered the database from.  The reason they were added to the project is that the database files are created in location other than the default database path of sql server.  If you want to use the default database file path you can simply replace these variables in the .sql files with $(DefaultDataPath)$(DatabaseName).mdf' and $(DefaultDataPath)$(DatabaseName).ldf'. You can also remove the 2 files from the project all together which will effectively have deployment create the database using the default database file path and settings.  
 

See also: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/b3787079-419d-46cf-89f8-37f44c61643d/

 

Thanks,

 

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.

Answer 2

You probably get tired of hearing this, but you were correct. The db was on a server that had 2 instances on it and since it was a low powered dev server, they just put the mdf and ldf in a newly installed hard drive. I didn't realize, of course, that when the db was imported that it would pull over those files. (BTW, what is the advantage to using those files vs. just creating a new db?)

Also let me say that you guys have created a great product. I work for a small company and there have been many times that the developers or the boss would mention a need and I'd speak up and say TFS can do it! The look at me surprised and say "Really? I didn't know that!" I only wish that there was a book out on it now to take us from the very basics to the advanced. the MSDN documentation seems to be disjointed at times and that is why we need you guys to answer these questions.

Thanks for taking the time to share your knowledge with us. I hope you get guys get a bonus based on how many questions you answer. If that is the case then "Party at Barclay's house!" Because you can afford it.

Brian

Answer 3

The advantage of having the files in your project is you can define settings and a path that differs from the default setting.  You could then deploy the files always the same way to multiple database servers.  You can also adjust them using SQLCMD variables to customize per target enviroment.  Really, if the database is not very big or you dont have partions dedicated to logs/data it usually ok with just deploying to the default directory defined by SQL Server.  One thing to note though, when files are present we only deploy them initially.  Any changes to the files will not be made during deployment.  Only additional files will be created during deployment.  So in short, you can add more files, but you cant remove existing files during deployment.  The reason is that removing files is often equivalent to dropping the database.  Changes to existing files need to be done through Admin Tools like SSMS.

Thanks for the positive feedback!  We always appreciate hearing how we are doing.

I think your idea of a monetary bonus for helping out customers on the forum is a fine idea indeed.  Ill make sure to pass that one on to management. ;)

Thanks,

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.

Answer 4

I have the same problem.  "Variable Path2 could not be found "
I have the exisitng path in the target server, but still have this error.  any ideas?
And 2 more questions:
1)  which 2 file can be removed , so it will use default path?
2) in .sql file, I don't see  a line like  "  $(DefaultDataPath)$(DatabaseName).mdf'   "

Thank you!

Answer 5

Hi LGodwin,

You will find this in the project system under Schema Objects -> Database Level Objects -> Storage -> Files. You may have Path2 being used in one of your sql files, but not defined in your Database.sqlcmdvars file under project properties.

You can remove these files if you want to simply deploy the database to the default file location of the target server. This assumes you are not using a custom file/filroup configuration as described above.

Your .sql files may include $(Path1) and $(Path2) indicating the database you imported did not store its files in the default paths.

Thanks,

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question. http://blogs.msdn.com/bahill

Answer 6

Thank you very much. I got it deployed finally, but I have questions.

Here are steps that I did to deploy a DB on a server which does not have VSTSDB installed.

1) Deploy the database project on a development machine, since development machine does not  have connection to the server where the production DB sits. So in Deployment Configuration, I can only choose Create a deployment script(.sql) from Deploy Action List; In Target Connection Settings, I can only set to the development machine itself.    dbschema  file is  generated at build time.

2) Copy .dbschema file to the server, and run
vsdbcmd.exe /a:Deploy /cs:"Server=Server1;Integrated Security=true;Pooling=false" /dsp:Sql /dd+ /model:"d:\deploy\Database_Temp.dbschema"  /p:TargetDatabase="Database_Temp"   
in command prompt.

3) got error first time on creating ASPNET user , because the model creates ASPNET and IUSR user, dbschema file generated in the development machine, so they are like
  [DEV1\ASPNET]  and  [DEV1\IUSR_DEV1]. When dbschema file runs on Server1, it couldn't create DEV1\ASPNET user on Server1, so I had to manually change users to

[Server1\ASPNET]  [Server1\IUSR_DEV1] in dbschema file, then it got deployed the model to  the Server DB.

Did I miss anything? or it has to manually change those?

Thank you !

Answer 7

These logins are tied to a specific machine. They appear to be local windows logins so yes you will need to change them, put them in the post deployment script, or not model logins in your project.  If you are using active directory logins and both boxes are on the same domain then it would just work.  Also, if you were using SQL Logins you wouldnt have the issue either since they can be created in what ever environment you deploy to.  Take a look at this blog post: http://blogs.msdn.com/psirr/archive/2009/07/31/template-driven-sql-generation.aspx

There are a few options outlined as to how to deal with logins that are specific to a machine.

Thanks.

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question. http://blogs.msdn.com/bahill