SQLCMD mode: environment variable syntax?

Question

I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode.

How do I use environment variables (e.g. %systemroot%) in the code? The following bombs:

:set mypath %systemroot% :out $(mypath)"\the_result.txt" select * from myTab

In other words, when using SQLCMD not from the command line, but inside my T-SQL code. I just need to sort out the syntax.

(And also perhaps, though forum protocol is probably to ask it as separate questions:

1. Is there an environment variable for the folder of this .sql / the working folder?

2. How do I prevent the line counts to go to my output file also? )

Thanks!

Answer 1

The sqlcmd syntax for setting variables is :setvar. You can access environment variables from sqlcmd mode (using the $(variableName) syntax) however I find these case-sensitive and it's a bit fussy about what you can and can't do. Try this, hopefully it makes sense:

:setvar mypath "$(SystemRoot)"

:out $(SystemRoot)\test.txt     -- will work

print '$(mypath)'   -- oops won't work
print '$(SystemRoot)'   -- should work

go

:out stdout