how to avoid sqlcmd blank line between Resultsets?

Question

hello, refering my last question on extracting SQL stored procedures into .sql files (see here) I have another question:

how to avoid or delete the sqlcmd blank line between Resultsets?

Reason (see MSDN) When multiple results are returned, sqlcmd prints a blank line between each result set in a batch.

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

Thanks in advance!

sean

Answer 1

here is update for your batch file (it will work for > 8000 chars limit but it's easy to tune up this limit):

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"

personally I'm concerned about so large procedures.

that's a limitation, but if you have stored procedures with line numbers longer than 4000 characters, you probably have much, much bigger problems than can be solved reading this blog ... none of my stored procedures have lines greater than about 150 characters long, so that's probably not a huge deal for most people. As I said, if your lines are that long, you have bigger problems!
Adam Machanic - "Reflect" a TSQL routine

but there are also thoughts that large procedures are not an issue:

"text" field is defined as NVARCHAR(4000) so each row can only hold up to 4000 characters. However, it is not uncommon to have object code that is much larger than 4000 characters. Solomon Rutzky - Searching Objects for Text

Answer 2

Hello again, I had some problems using this, it didn't run, but it helped me with showing how to do it, my current approach looks like this:

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), @SP3 NVARCHAR(4000) SET @I = 0 SET @SP1 = '' SET @SP2 = '' SET @SP3 = '' SELECT @I = count(number)from dbo.syscomments WHERE id = OBJECT_ID('%%a') WHILE @I > 0 BEGIN   SELECT @SP1 = CASE WHEN @I = 1 THEN text ELSE @SP1 END,  @SP2 = CASE WHEN @I = 2 THEN text ELSE @SP2 END, @SP3 = CASE WHEN @I = 3 THEN text ELSE @SP3 END from dbo.syscomments WHERE (id = OBJECT_ID('%%a')) AND (colid = @I) SET @I = @I-1 END SELECT @SP1,@SP2,@SP3" -o "%%a.sql"

indeed the SELECT @SP1,@SP2,@SP3 is not correct since it produces a space between the variables. But if I use + just the first varialbe will be returned (or just the first 4000 chars).

In SQL Management Studio the statement

SELECT CAST(@SP1 AS varchar(max)) + CAST(@SP2 AS varchar(max)) + CAST(@SP3 AS varchar(max))

works fine, but carried over to the sqlcmd statement mysterious amount of 259 or 260 chars will be returned only.

any suggestions? Thanks in advance :)