5

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

  • Wow, very creative use of variable and sqlcmd mode (via sqlcmd utility) :-) – jyao Jul 10 '18 at 20:00
  • @jyao - if I can get it to work ;-) – Max Vernon Jul 10 '18 at 20:08
  • Why not use powershell to do the task ? – Kin Shah Jul 11 '18 at 12:21
  • @Kin - we have a powershell script doing this. I'm just interested in understanding why it works like this. – Max Vernon Jul 11 '18 at 12:36
3

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:\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
  • 2
    I heard you like scripts... – Erik Darling Jul 11 '18 at 1:17
  • Script much? : ) – wBob Jul 11 '18 at 6:41
  • thanks for the simplified example. The only problem I had was admitting that I needed a GO prior to each and every :out statement in order for them to work as expected. – Max Vernon Jul 11 '18 at 14:45
4

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

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:

enter image description here

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 > 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
  • 1
    Excellent answer, Scott - thanks very much for taking the time! I had to choose a single answer as "correct", so that went to the @wBob since his answer and yours both use the same methodologym but his was first to be posted. – Max Vernon Jul 11 '18 at 14:45
  • 1
    @MaxVernon - I learned some new stuff while working on this which is a plus for me too :) – Scott Hodgin Jul 11 '18 at 14:47
2

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:\temp\test.txt';

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

enter image description here

You see, the error message has "unable to redirect output to @filename" and 'c:\windows\system32\@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:\windows\system32\"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.