sqlcmd :out não está funcionando como esperado?

Usar SQLCMDdessa maneira era novo para mim e me fez pesquisar na Internet por exemplos.

Depois de estudar as informações no SQL Server Management Studio: Hacking SQLCMD Mode e ponderar sobre as limitações das SQLCMDvariáveis, adotei uma abordagem um pouco diferente para o seu problema. Minha solução pode não responder totalmente à sua pergunta sobre por que :OUTnão está funcionando conforme o esperado, mas vou oferecê-la de qualquer maneira.

Meu script completo está no final da minha resposta, mas aqui estão alguns fragmentos com explicações.

Comecei redirecionando a saída para o diretório temporário do meu usuário local

set nocount on

--Redirect output to user temp folder
--On my computer, this was 'C:\Users\uswsh\AppData\Local\Temp'
:OUT $(TEMP)\DBScript.sql

Agora, invocamos o cursor para ler a lista de bancos de dados. Estou usando o PRINTcomando para gerar echocomandos do sistema operacional para criar os arquivos de criação de banco de dados individuais. A saída de toda a execução do cursor é gravada no arquivo DBScript.txt na pasta temporária do meu usuário.

--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:\temp\create_database_' + @db + '.txt';
        FETCH NEXT FROM cur INTO @db
    END
CLOSE cur;
DEALLOCATE cur;
go

Após a execução do acima, o arquivo DBScript.txt agora contém esses comandos (o duplo golpe no início de cada comando indicará SQLCMDpara executá-los como comandos do sistema operacional):

!!echo nul > C:\temp\create_database_master.txt
!!echo nul > C:\temp\create_database_tempdb.txt
!!echo nul > C:\temp\create_database_model.txt
!!echo nul > C:\temp\create_database_msdb.txt
!!echo nul > C:\temp\create_database_Test.txt
!!echo nul > C:\temp\create_database_AdventureWorks2014.txt

Agora, de volta ao script original, revertemos a SQLCMDsaída para stdout e executamos os comandos em DBScript.txt

--Revert output to stdout
:OUT stdout

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

Quando olho para o meu diretório C:\Temp, agora tenho os seguintes arquivos individuais:

Aqui está o roteiro completo:

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

A maneira de fazer isso com o modo sqlcmd é criar um script que cria um script e, em seguida, executar esses scripts, então é uma maneira um pouco diferente de pensar sobre as coisas, um pouco como Inception ou Borges, o sonho dentro do sonho:

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

:out d:\temp\temp.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:\temp\create_database_' + name + '.txt'
FROM sys.databases 
GO


-- Return stdout to normal
:out STDOUT
GO

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


-- Create another script which executes all thoses scripts ...
:out d:\temp\temp2.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:\temp\temp2.sql
GO

Ok, acho que recebo a resposta daqui

gostaria de citar aqui

Não é possível porque sqlcmd vars e t-sql vars são avaliados independentemente em etapas diferentes e díspares da execução de um script sqlcmd.

Isso é tão verdadeiro, em outras palavras, seu :setvare :outé avaliado primeiro antes que o t-sql seja executado, e isso também explica por que meu t-sql simples mostrado abaixo fornece a mensagem de erro

DECLARE @filename varchar(260)='c:\temp\test.txt';

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

Você vê, a mensagem de erro tem "não é possível redirecionar a saída para @filename" e 'c:\windows\system32\@filename', é porque, quando :out é avaliado, ele não conhece o valor de @filename, então apenas use a string literal @filenamepara o propósito :out, que será automaticamente padronizado para o caminho padrão de "c:\windows\system32\"