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?
Best Answer
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 theGO
(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: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 themaster
Database first, which could lead to a performance issue.