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

Handgun

A related issue in connect.microsoft.com – Handgun

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.

Bastard

Thanks for the suggestion. Unfortunately this did not work for me. I have updated the question to include what I have tried based on your suggestion. – Handgun

Try putting a backslash between the variables. :r $(path)\$(ddl) – Bastard

What I have figured out so far was that having spaces in the folder path does not work. I tried this with folder path which has no spaces and found that it works indeed. – Handgun

To be more precise, having folder path does not work properly. It worked in some instances and it did not in other. – Handgun

All this does is create an absolute path to execute, which is not what the original question asked for. This is not a viable solution where relative paths are needed, such as for continuous integration, continuous delivery etc, where this approach would require editing the file for different environments or calling the script with unique powershell parameters for different environments. – Synchroflash

@Synchroflash - with this couldn't you create a powershell wrapper, though? Like, a powershell script takes the given SQL script, reads its path, and then passes in its own directory-path as a SqlCmd argument? – Illyria

Sure, but that is a different solution though. – Synchroflash

Agree entirely with @Synchroflash - makes you wonder why they bothered including :r at all. If the only way to do this is to pass the path as a variable to SQLCMD using PS, then I can do this through the name of the sql file itself, I don't need to pass a variable to SQLCMD to have it run a script that runs the scripts, I just get PS to run the scripts using SQLCMD - no need for :r at all. – Yaw

Answer 2

I realize this is pretty old, but I noticed an error in your Edited code: you need to include a backslash between the path and the script name.

:r $(path)\$(ddl)
:r $(path)\$(dml)
:r $(path)\$(sprocs)

Prostate

Answer 3

I have found, that this would be the best:

:setvar path C:\"some path"

:r $(path)\myfile.sql

You have to put statements with a space in quotes, but not the entire statement. That is why you can do C:\"some path"

Tacho

Answer 4

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:

!! PWD

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:

1. Execute a DOS command to find todo_master.sql and store the path to that file in a text file in a folder that you can get from SSMS, either because it is a hard-coded path or because it uses an environment variable that is available to both the DOS command and to SQLCMD mode in SSMS
2. When storing the path in that file, store it as a SQLCMD mode command that sets a variable to that path
3. Import that text file into SSMS using :r and it will set that variable to the desired path

!! CD C:\ & FOR /F %B IN ('DIR /B /A -HS /S todo_master.sql') DO ECHO :setvar mypath "%~dpB" > %TEMP%\relative_path.txt

:r $(TEMP)\relative_path.txt

:r $(mypath)\todo_create_ddl.sql
GO
:r $(mypath)\todo_create_dml.sql
GO
:r $(mypath)\todo_create_sprocs.sql
GO

Notes:

• The above method assumes only 1 file on the system is named todo_master.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 the CD command to get closer to the destination, such as:

  !! CD C:\Users\{YourLogin}\Documents\Visual Studio 2013 & FOR ...
  

Furlong

Thanks for your solution, one minor tweak is that if your path contains spaces you need to include the delims option for the FOR command. Example: FOR "delims=;" %B IN (... – Alyssaalyssum

Answer 5

I had this same issue myself and I hope I'm not stating the obvious - why not open a Dos/Cmd or PowerShell instance, cd to the directory containing the scripts, then load Management Studio from there?

I have this alias in my PowerShell set-up (your path may differ):

Set-Alias -Name 'Ssms' -Value "${env:ProgramFiles(x86)}\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe" 

I then cd to the folder containing the solution file, then I do

Ssms mysolution.ssmssln

Amatory

Answer 6

Use a batchfile as a helper.

Modify todo_master.sql to use an environment variable called mypath:

use master
go 
:r $(mypath)\todo_create_ddl.sql
:r $(mypath)\todo_create_dml.sql
:r $(mypath)\todo_create_sprocs.sql
go

And in your batch file todo_master.bat

/* set the environment variable to the current directory */
SET mypath=%cd%
/* run your sql command */
sqlcmd -i todo_master.sql

Wichern

Answer 7

1)No spaces in files and folders

2)Give absolute path in post deployment script.

3)Remove the double colon in post deployment script

4)post deployment script path should have / not the \ (CICD pipeline deployment using Github actions).

:r mainfolder/subfolder1/subfilder2/file1.sql

GO

Copeland