Multi-command SQLCMD mode script

Question

I am trying to execute a sql procedure for a list of tables in sqlcmd mode to redirect all the output into one file per table.

Unfortunately only the last file of the redirected outputs of all commands gets content.

For example I execute the following:

:OUT  c:\test\tab0.sql 
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab0', @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1
:OUT  c:\test\tab1.sql 
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab1' , @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1

Both files are created, but only the last file gets all the content.

Any hints how I can change this in order to get the results in one file per command?

Answer 1

Put a GO (i.e. batch separator) between the two sections. SQLCMD commands and variables are processed per each batch. I tested this and without the GO (i.e. how it is set up in the Question), I get the same behavior that you are seeing: first file exists but is empty, while the second file has the results for both stored procedure executions. This is due to both :out commands being in the same batch, hence the 2nd :out replaces the first one.

But, if I put a GO between them, each output file has just the results for that one execution:

:OUT  c:\test\tab0.sql 
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab0', @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;
GO
:OUT  c:\test\tab1.sql 
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo',
           @table_name ='tab1' , @include_timestamp = 0,
           @ommit_images = 1, @CreateProcedure = 1;

---

P.S. Unrelated to this issue, you shouldn't prefix your Stored Procedure names with sp_ as that is reserved for special use by SQL Server and searches the master Database first, which could lead to a performance issue.