sqlcmd :out not working as expected?

Consider the following code:

DECLARE @db sysname;
DECLARE @filename varchar(260);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = 'C:tempcreate_database_' + @db + '.txt';
    :setvar c @filename
    :out $(c)
    PRINT $(c);
    FETCH NEXT FROM cur INTO @db
END
CLOSE cur;
DEALLOCATE cur;

I’m expecting to get multiple files created in C:temp, one for each database, but nothing is created, and no errors are reported by SQL Server.

I’ve tried running this in SSMS using SQLCMD mode, and I’ve also tried running it from sqlcmd.exe

The way to do this with sqlcmd mode is to script a script which scripts a script, then execute those scripts, so it’s a slightly different way of thinking about things, a bit like Inception or Borges, the dream within the dream:

-- Script a script which scripts a script
SET NOCOUNT ON
GO

:out d:temptemp.sql
GO

SELECT REPLACE( ':out d:temp@database.sql
-- Do some work here
PRINT ''select ''''@database''''''
GO

-- Return stdout to normal
:out STDOUT
GO
', '@database', name ) AS [--sql]
--C:tempcreate_database_' + name + '.txt'
FROM sys.databases 
GO


-- Return stdout to normal
:out STDOUT
GO

-- Now execute your scripted file
:r d:temptemp.sql
GO


-- Create another script which executes all thoses scripts ...
:out d:temptemp2.sql
GO

SELECT REPLACE( ':r d:temp@database.sql
GO
', '@database', name ) AS [--sql]
FROM sys.databases 
GO

-- Return stdout to normal
:out STDOUT
GO

-- Execute your script of scripts?
:r d:temptemp2.sql
GO

Using SQLCMD in this way was new to me and got me searching the Internet for examples.

After studying the information in SQL Server Management Studio: Hacking SQLCMD Mode and pondering the limitations of SQLCMD variables, I took a slightly different approach to your problem. My solution may not fully answer your question about why :OUT is not working as expected, but I’ll offer it up anyway.

My full script is at the end of my answer, but here are some fragments with explanations.

---

I started out by redirecting the output to my local user temp directory

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:UsersuswshAppDataLocalTemp'
:OUT $(TEMP)DBScript.sql

---

Now, we invoke the cursor to read through the list of databases. I’m using the PRINT command to generate operating system echo commands to create the individual database create files. The output of the entire cursor execution is written to the DBScript.txt file in my user temp folder.

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:tempcreate_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

---

Upon execution of the above, the DBScript.txt file now contains these commands (The double bang at the beginning of each command will tell SQLCMD to execute these as operating system commands):

!!echo nul > C:tempcreate_database_master.txt
!!echo nul > C:tempcreate_database_tempdb.txt
!!echo nul > C:tempcreate_database_model.txt
!!echo nul > C:tempcreate_database_msdb.txt
!!echo nul > C:tempcreate_database_Test.txt
!!echo nul > C:tempcreate_database_AdventureWorks2014.txt

---

Now, back to the original script, we revert the SQLCMD output to stdout and execute the commands in DBScript.txt

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)DBScript.sql
GO

---

When I look at my C:Temp directory, I now have the following individual files:

Here is the full script:

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:UsersuswshAppDataLocalTemp'
:OUT $(TEMP)DBScript.sql

--Declare variables used by cursor
DECLARE @db sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT d.name
FROM sys.databases d;
OPEN cur;
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '!!echo nul > C:tempcreate_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

--Revert output to stdout
:OUT stdout

--Execute the script you just created that has the !!echo commands
:r $(TEMP)DBScript.sql
GO

Ok, I think I get the answer from here

I’d like to quote it here

It’s not possible because sqlcmd vars and t-sql vars are evaluated
independently in different and disparate steps of the execution of a
sqlcmd script.

This is so true, in another word, your :setvar and :out are evaluated first before t-sql are run, and that also explains why my simple t-sql shown below gives the error message

DECLARE @filename varchar(260)='c:temptest.txt';

:setvar c @filename
:out $(c)
PRINT $(c);

You see, the error message has “unable to redirect output to @filename” and ‘c:windowssystem32@filename’, it is because, when :out is evaluated, it does not know @filename’s value, so it just use the literal string @filename for :out purpose, which will automatically default to the default path of “c:windowssystem32”