12

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:

Annotated Procmon screenshot

How do I tell SSMS to look in the folder that the current script is saved to without explicitly declaring the path?

4 Answers 4

9

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 foo.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 foo.sql') DO ECHO :setvar mypath "%~dpB" > %TEMP%\relative_path.txt

:r $(TEMP)\relative_path.txt

:r $(mypath)\bar.sql

Notes:

  • 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 the CD command to get closer to the destination, such as:

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

I could be wrong, but I don't think that it is possible. Please see these links for an example of what others have done (none are ideal).

https://stackoverflow.com/questions/8753541/how-to-get-the-relative-path-of-file-in-sqlcmd-mode-in-ssms

http://boardreader.com/thread/Specifying_relative_path_to_r_command_in_lmlz__f0c78408-e001-48a2-8369-338c9534f496.html

0
4

I recommend saving your sqlcmd variables file in 'C:\Users\your_nt_login_name\AppData\Local\Temp' and referencing it from all my projects as $(TEMP)\sqlcmd_variables.sql.

1

If you open the solution/project, and likely file, by using Windows Explorer, it will open a new SSMS instance using that folder as its working directory. :)

Alternately, using the command line, and opening SSMS from the desired folder, sets the working directory for that instance of SSMS. i.e. use the following to start SSMS v20 from its default installation directory (your path may vary):

C:\>cd \dev\sqlserver\code

C:\Dev\SQLServer\Code>start cmd /c "C:\Program Files (x86)\Microsoft SQL Server Management Studio 20\Common7\IDE\Ssms.exe"

Once SSMS is open, start a new Query in SQLCMD mode, then run the following from the query window:

!! cd

You'll see the present working directory for sqlcmd is the directory where you started SSMS:

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.