How to get the relative path of file in SQLCMD Mode in SSMS?

Question

I have the below master script which creates tables, and inserts some data and then creates the stored procedures.

--todo_master.sql  use master go  :r todo_create_ddl.sql :r todo_create_dml.sql :r todo_create_sprocs.sql go 

However, even though the todo_master.sql is in the same path as the other three scripts, it is unable to locate those three scripts.

I get the following error:

A fatal scripting error occurred. The file specified for :r command was not found. 

If I provide the complete path like below, these files are found and executed as intended.

"C:\Docs and Settings\user\My Docs\SSMS\Projects\todo_create_ddl.sql"

What might I be missing?

---

Edit As suggested by Jason I tried this, but still get the same error:

use master go  :setvar path "C:\Documents and Settings\user\My Documents\SQL Server Management Studio\Projects" :setvar ddl "todo_create_ddl.sql" :setvar dml "todo_create_dml.sql" :setvar sprocs "todo_create_sprocs.sql"  :r $(path)$(ddl) :r $(path)$(dml) :r $(path)$(sprocs) go 

Answer 1

You can work around this by using the sqlcmd setvar option to assign the path to a variable. Then use that variable in your :r call like:

:setvar path "c:\some path" :r $(path)\myfile.sql 

This link has a more in depth example: http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/

With that in place you can remove the setvar line and pass that in from the command line with:

Sqlcmd /Sserver /E -ddatabase -iInputfilename -oOutputfilename -v path=c:\somepath 

This would work around the issue of the script not running from the directory where the first SQL script was called from.