SQLCMD mode: Environment variable syntax

Question

I assume the best way to set my SSMS 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 

And also perhaps: Is there an environment variable for the folder of the Query.sql with this code, or for the working folder? Thanks!

Answer 1

Some caveats:

[1] Supported SQLCMD scripts in SSMS

https://learn.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?view=sql-server-ver15

[2] From the above link, which you appear to have read (a long time ago), read further about the :!!

Then have a look at this as an example

:!!set

...which is the same as this...

!!set

anyway the output shows it's SystemRoot, not systemroot.

[3] SSMS includes the completion time by default

i) Tools > Options > Query Execution > SQL Server > Advanced.

ii) Uncheck the Show Completion Time checkbox. And so, to the answer...

:out $(SystemRoot)"\the_result.txt"
select * from myTab 
:out stdout

I got this...

<p style="color: red"> 
Unable to redirect output to C:\WINDOWS\the_result.txt. <br>
Access to the path 'C:\WINDOWS\the_result.txt' is denied.<br>
Msg 208, Level 16, State 1, Line 31<br>
Invalid object name 'myTab'.
</p>

...then again, why would I want to output a query to a file in C:\WINDOWS for a table that doesn't exist! ;) Let's create a test table and the final script is:

--[4] Turn off (x row affected)
set nocount on
GO

create table myTab(
    ID int identity(1,1) not null
    ,colTest varchar(20) null
);
insert into myTab
    select 'Hello world';
go

--change the output to be a file in the temporary directory
--This file will be overwritten each time the script is ran
:out $(TEMP)\the_result.txt
--print this to the output
print ' <- [5] There might be squiggly bits'
print '$(SystemRoot)'
select * from myTab
go

--reset the output
:out stdout
--use type to see file contents
!!type %TEMP%\the_result.txt
--let's be tidy
!!del %TEMP%\the_result.txt
drop table myTab
go

Messages output

 <- [5] There might be squiggly bits
C:\WINDOWS
ID          colTest
----------- --------------------
1           Hello world

Summary

With doing nothing else, you have access to environment variables once in SQLCMD mode in SSMS

print '$(SystemRoot)'

Answer 2

How to Read Environment Variables in SQL Server Using T-SQL

To read Environment variables in T-SQL, you can use the xp_cmdshell extended stored procedure in SQL Server.

The following example shows how to read the %windir% environment variable, which gives the Windows directory path on SQL Server using xp_cmdshell:

DECLARE @windir nvarchar(255)
CREATE TABLE #Tmp
(
EnvVar nvarchar(255)
)
INSERT INTO #Tmp exec xp_cmdshell 'echo %windir%'
SET @windir = (SELECT TOP 1 EnvVar from #Tmp)

SELECT @windir as 'Windows Directory'

NOTE: To run this command, you need to be a member of the sysadmin fixed server. If you want others to be able to execute this command, you will have to explicitly grant them permission to execute the xp_cmdshell stored procedure.

Find more information about this stored procedure at MSDN. Source

Using command prompt environment variables within sqlcmd In the following example, four environment variables are set and then called from sqlcmd.

C:\>SET tablename=Person.Person
C:\>SET col1=FirstName
C:\>SET col2=LastName
C:\>SET title=Ms.
C:\>sqlcmd -d AdventureWorks2012
1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
2> FROM $(tablename)
3> WHERE Title ='$(title)'
4> GO

Source