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!
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