SQLCMD - Capture output from Operating System Command as variable

Question

I would like to capture the output of an sqlcmd !! (operating system command) call into a variable that I can use in an insert statement. Or, just read the contents of a file into a variable.

The general idea is something like this:

:SETVAR version !! "type version.txt"

insert into dbo.DeployVersion ([Version],[Date],[User]) values ('$(version)',getdate(),'$(SQLCMDUSER)')

But it doesn't seem that I can chain SQLCMD calls in that way. Any ideas?

type version.txt just prints the content of version.txt file to the console, like cat in linux.

Answer 1

The best I could find was this approach, which seemed really silly.

:setvar quot "'"
declare @versionString nvarchar(300)
set @versionString =
$(quot)
:r .\version.txt
$(quot)

insert into dbo.DeployVersion ([Version],[Date],[User]) values ('$(version)',getdate(),'xyz')

This works in purely SQLCMD and is an answer to the original question.

However, this is being used in an sqlpackage deploy and I don't see a way to externally reference a version.txt file from outside of the dacpac. So instead I added a variable to the dacpac project and I specify that value on the commandline using sqlpackage ... /Variables:Version=%someValueReadFromBatFile%