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:\temp\create_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
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:\Users\uswsh\AppData\Local\Temp'
: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 &gt; C:\temp\create_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 &gt; C:\temp\create_database_master.txt
!!echo nul &gt; C:\temp\create_database_tempdb.txt
!!echo nul &gt; C:\temp\create_database_model.txt
!!echo nul &gt; C:\temp\create_database_msdb.txt
!!echo nul &gt; C:\temp\create_database_Test.txt
!!echo nul &gt; C:\temp\create_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:\Users\uswsh\AppData\Local\Temp'
: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 &gt; C:\temp\create_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