9

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this:

restore database [DB]  from disk='F:\FULL1.bak' with norecovery
restore database [DB]  from disk='F:\DIFF1.bak' with norecovery
restore log [DB]  from disk='F:\log1.bak' with norecovery
restore log [DB]  from disk='F:\log2.bak' with norecovery
...
restore log [DB]  from disk='F:\logN.bak' with norecovery
restore database [DB] with recovery

Througout the SP, the lines get added to a parameter @sql varchar(max) which, at the end, gets printed with print(@sql), and if the user specified the right flag, gets executed with exec(@sql). Both work perfectly. However, when calling the SP with sqlcmd like this:

set restoresql=exec restoredatabase @DB='Databasename', @Execute='y'
sqlcmd -h-1 -S SRV\T2 -U sa -P sa -d master -Q "%restoresql%" -o output.txt

The output file output.txt will always have a maximum of 4002 characters.

I've tried adding -y 8000 but that doesn't help. How can I get the output file to give me the full generated script?

1
  • Thought about just using PowerShell's SQLPS command Invoke-Sqlcmd? I don't believe it has the character limit issue but have not specifically used it to try.
    – user507
    Apr 12, 2015 at 21:03

1 Answer 1

4

To be clear, the issue is really with the PRINT command and not the SQLCMD utility.

I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 characters, but not beyond 8000, run the following:

sqlcmd -Q "DECLARE @Yo VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX), '#'), 9000); PRINT @Yo;" -o out.txt

If you need more than 8000 characters printed, then you need to iterate over the variable in chunks of up to 8000 characters (the PRINT command will only display up to 8000 CHAR / VARCHAR characters at a time). But you don't want to simply chop it into even 8000 character chunks as that could be right in the middle of something that should not be split, like a word / object name / number, etc. Since the PRINT command will always include a newline character at the end, it is best to print up to the last newline character of each 8000 character chunk. The result is that the only lines to get split across multiple displayed lines are those that are over 8000 characters before the next newline character, and there is really nothing that can be done about that.

Try the following proc in place of your print(@sql) command (FYI: you don't need parenthesis around @sql for PRINT like you do for EXEC). Please note that finding the last newline character in a string requires creating a LastIndexOf function since one is not built into T-SQL. You can create a T-SQL UDF to do this. You can also use SQLCLR in which case you can code it yourself or simply get the Free version of the SQL# library (which I am the author of).

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

IF (OBJECT_ID(N'dbo.Display') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.Display;
END;

GO
CREATE PROCEDURE dbo.Display
(
  @TextToDisplay VARCHAR(MAX)
)
AS
SET NOCOUNT ON;

  DECLARE @Length INT = LEN(@TextToDisplay),
          @Buffer VARCHAR(8000),
          @BufferLength INT,
          @StartIndex INT = 1,
          @LastNewlineIndex INT;

  SET @TextToDisplay = REPLACE(@TextToDisplay, CHAR(13), ''); -- normalize

  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 8000);
    SET @BufferLength = DATALENGTH(@Buffer);

    IF (@BufferLength < 8000)
    BEGIN
      BREAK;
    END;

    SET @LastNewlineIndex =
                   SQL#.String_LastIndexOf(@Buffer, CHAR(10), @BufferLength, 1);

    IF (@LastNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@LastNewlineIndex - 1));
      SET @StartIndex += @LastNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END; -- WHILE (1 = 1)

  -- Don't print empty line if final chunk was 8000 chars leaving final loop with 0
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
GO

Tests:

DECLARE @Test VARCHAR(MAX);
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7998) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1.1' + REPLICATE('a', 7000) + '1.1' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.2' + REPLICATE('a', 400) + '1.2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.3' + REPLICATE('a', 4000) + '1.3' + CHAR(13) + CHAR(10);
SET @Test = @Test + '2' + REPLICATE('b', 1798) + '2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';

NOTES:

  1. You could get away using the built-in CHARINDEX function to look for each next newline, and in this respect it would function almost like using a string splitting function. The difference between what is needed here and doing a simple split on newlines is that any element returned that is over 8000 characters will still need to be split into chunks of no more than 8000 characters.
  2. In order to handle NVARCHAR(MAX) instead of VARCHAR(MAX), use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) values
  3. There is a stored procedure, Util_Print, in the SQL# library that handles NVARCHAR(MAX) and has some functionality beyond the T-SQL code shown above, but that is only available in the Full (i.e. paid for) version, not in the Free version. But the code shown here will handle most cases.
4
  • You are right, it was a NVARCHAR(MAX) instead of a VARCHAR(MAX). This somewhat helps, as most scripts seem to be between 4000 and 8000 characters, but the problem is still there: There is a limit to how much characters you can print., while a VARCHAR(MAX) can hold much more data, so the original question still stands. Apr 9, 2015 at 11:23
  • 1
    @user1261104 I misunderstood and thought you only wanted the 8000. I have updated my answer to address getting more than 8000 characters. Apr 12, 2015 at 20:05
  • 1
    Wow, didn't expect that an whole procedure needed to be built for such a relative simple task. But it works amazingly, thanks! Apr 16, 2015 at 15:19
  • @user1261104 Well, given that it can only display 8000 chars of VARCHAR or 4000 of NVARCHAR, AND you don't want to chop a line in the middle, there isn't much choice. You could put this logic directly into your proc, but having as a separate proc means it can be used elsewhere as well :-). Apr 16, 2015 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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