I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
SQL Server – How to Make sqlcmd Return an ERRORLEVEL Other Than 0 When Script Fails
sql serversql-server-2008-r2sqlcmd
Related Solutions
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:
- 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. - In order to handle
NVARCHAR(MAX)
instead ofVARCHAR(MAX)
, use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) values - There is a stored procedure,
Util_Print
, in the SQL# library that handlesNVARCHAR(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.
Do this:
1.> Open a Command Prompt window and type: cd\ dir sqlcmd.exe /s
Our Command Prompt window returned the following: C:\Program Files\Microsoft SQL Server\100\Tools\Binn
Note: if no sqlcmd.exe is found something happened with your SQL install. To get you up and running I'd simply download the utilities themselves here:
https://www.microsoft.com/en-us/download/details.aspx?id=36433
2.> From the Command Prompt window command menu Mark and highlight that path that was returned and press Enter to put it in the paste buffer.
3.> Edit the System path (not the user path) within Windows and paste the path you just found just after all the %SYSTEMROOT% path references and click OK. You may need to restart your server for this to take affect.
Related Question
- Sql-server – large insert script (generated from tasks) – errors when running from sqlcmd
- Execute Permission Denied Using SQLCMD – How to Resolve
- SQLCMD Script Output Messages Not in Order – How to Fix
- SQL Server – Decrypting a Master Key Using SQLCMD vs Script
- SQLCMD – Does ‘!!’ Command Work in Azure Data Studio?
Best Answer
You should use the option -b in sqlcmd.
-b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0
http://msdn.microsoft.com/en-us/library/ms162773.aspx