skip to main content

Get the relative path of a file in SQLCMD Mode in SSMS

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

use master
go

:r todo_create_ddl.sql
:r todo_create_dml.sql
:r todo_create_sprocs.sql
go

However, even though the master script 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 provided the complete path like below, these files were found and executed as intended.

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

After asking around on StackOverflow, I got some direction. We can use setvar to create the paths in this way:

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

I have updated the initial script to use setvar:

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

Then I figured out that having spaces in the relative path doesn't work.

References