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
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.