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
Best Answer
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: