Which workarounds exists to missing relative path feature of SQLCMD :r?

Question

I'm not the first to notice the lack of this feature.

But I wonder if there are some useful workarounds. I want to deploy a main script and some depended scripts in the same folder.

BTW Is there a ticket on Microsoft Connect for this?

Edit:

Locking at this, it seems equivalent to the question if a script running in sqlcmd mode can determine its path in the file system.

2nd Edit:

From SO I found a way to pass the path of the scripts into sqlcmd

sqlcmd -S myServer -d myDB -U user -P password -i file1.sql -v p1="D:\myscripts" 

And I can verify that it is passed to File1.sql

PRINT '$(p1)' 
:setvar path1 $(p1) + '\file1.sql'
:r $(path1)

But I don't find the right way to concat the path and the filename in the second line.

3rd Edit: I found the solution to concat path and filename in here.

PRINT '$(p1)' 
:r $(p1)\file1.sql

Are there still better ways?

• user507: I would not expect SQLCMD is going to see to many improvements for this issue with the addition of PowerShell. It would not suprise me if they added it to the deprecated list in future releases. Just something to consider.
• Gaius: This is done with @@ in Oracle's SQL*Plus; might be worth expressing the question as SQL Server equivalent-of
• PollusB: Since you are using SQLCMD to run a script, why don't you use CMD functions like PUSHD and POPD to set path before running the script?

Answer 1

Microsoft isn't improving sqlcmd - all their bets are on the PowerShell horse. When you're doing automated deployments like you describe, PoSH will give you more options for all kinds of dependency stuff. I'd focus there.