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?

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.