How to avoid sqlcmd blank line between results?

Question

referring to my last question about extracting SQL stored procedures in .sql files (see here) I have one more question:

how to avoid or remove empty sqlcmd line between results?

Reason (see MSDN) When multiple results are returned, sqlcmd prints an empty string between each result set in the batch.

This means that stored procedures (longer than 4000 characters) are divided into two parts each, in syscomments and when exporting from sqlcmd to a text file (.sql), there will be a new line at this split point. How to remove or avoid this?

Thanks in advance!

Sean

Answer 1

your batch file is updated here (it will work for> 8000 characters, but this limit is easy to configure):

for /f %%a in (sp_list.txt) do sqlcmd -E -S SERVER -d DB -h-1 -Q "DECLARE @I INT, @SP1 NVARCHAR(4000), @SP2 NVARCHAR(4000) SET @I = 0 SET @SP1 = '' SET @SP2 = '' SELECT @I = @I + 1, @SP1 = CASE WHEN @I = 1 THEN text ELSE @SP1 END, @SP2 = CASE WHEN @I = 2 THEN text ELSE @SP2 END from dbo.syscomments WHERE id = OBJECT_ID('%%a') SELECT @SP1+@SP2" -o "%%a.sql"

I am personally concerned about such large procedures.

this is a limitation, but if you have stored procedures with line numbers longer than 4000 characters, you probably have a lot more problems than you can solve by reading this blog ... none of my stored procedures have lines longer than 150 characters, so for most people this probably doesn't matter much. As I said, if your lines take so long, you have more problems!
Adam Machanik - Reflect TSQL Procedure

but there are also thoughts that large procedures are not a problem:

"" NVARCHAR (4000), 4000 . , , 4000 . Solomon Rutzky -