If I have foo.sql and bar.sql in the same folder, foo.sql can reference bar.sql when run from SSDT in sqlcmd mode with :r ".\bar.sql"
. However, SSMS won't find it. Procmon shows SSMS is looking in %systemroot%\syswow64
:
How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?
Best Answer
Getting a relative path in SSMS is not that straight-forward since you are not executing the script; SSMS has loaded the script into memory and is executing its text. So the current directory/folder is the default process starting folder. You can see this by running the following in SQLCMD mode in SSMS:
However, I did find a kinda-sorta way to do this. I admit that this is not the super-most ideal way to do this, however, it currently seems to be the only way to get a true relative path (given that setting the path in a variable isn't really "relative" per se).
So what you can do is:
:r
and it will set that variable to the desired pathNotes:
The above method assumes only 1 file on the system is named foo.sql. If more than one file has that name, the last one found will be the path set in the relative_path.txt file
Doing the
CD C:\
will start at the root of the C: drive. This is probably not the most efficient place to start. If you generally have your SQL files in an area such as C:\Users{YourLogin}\Documents\Visual Studio 2013\Projects, then just change theCD
command to get closer to the destination, such as: