DDBoost error using SQL agent with redirect output to log feature

[stevensgg]

Description of the issue Error: Msg 0, Sev 0, State 1: Unknown token received from SQL Server [SQLSTATE HY000] String data, right truncation [SQLSTATE 01004] Msg 16389, Sev 16, State 1: The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted. [SQLSTATE 08S01]

Cause

Issue is specific to customers using SQL agent with redirect output to log feature. The white space in this case was due to the table schema being dumped out to the output file, as MS App agent version 4.5 and later has the output to table result feature.

When you use the save output to file option in the SQL agent job, for any tables returned, it prints out the header info. In the previous versions of Microsoft App agent (i.e versions prior to 4.5) it did not return any sql tables, hence it did not print out anything and simply contains the content of the messages window.

In version 4.5 and later, the output of the command is in a table format and this causes the header to be dumped to the output file. And because our message column is 4000 characters wide (to allow for the maximum text), this causes large amount of white space and dashes in the output, which represents the header. The messages window contents is also saved to this file, so the file has duplicated data.

Resolution

To solve this issue capture the table output to a variable, this would eliminate it from the output file, as it is stored in memory only and discarded: e.g. instead of:

DECLARE @returnCode int EXEC @returnCode = dbo.emc_run_backup ' -c clientname -l full -y +365d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=ddserver" -a "NSR_DFA_SI_DD_USER=username" -a "NSR_DFA_SI_DEVICE_PATH=/ddboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" MSSQL:DBAJobs' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

Use this syntax:

declare @t table (msg nvarchar(MAX)) DECLARE @returnCode int insert into @t (msg) EXEC @returnCode = dbo.emc_run_backup ' -c clientname -l full -y +365d -a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE" -a "NSR_DFA_SI_DD_HOST=ddserver -a "NSR_DFA_SI_DD_USER=username" -a "NSR_DFA_SI_DEVICE_PATH=/ddboost" -a "NSR_DFA_SI_DD_LOCKBOX_PATH=C:\Program Files\DPSAPPS\common\lockbox" -a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE" MSSQL:DBAJobs' IF @returnCode <> 0 BEGIN RAISERROR ('Fail!', 16, 1) END

SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64) May 27 2021 17:34:14 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Version of the script Version: 2022-01-02 13:58:13

What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @BackupSoftware = 'DATA_DOMAIN_BOOST', @DataDomainBoostHost = '', @DataDomainBoostUser = '', @DataDomainBoostDevicePath = '*****', @DataDomainBoostLockboxPath = 'C:\Program Files\DPSAPPS\common\lockbox', @BackupType = 'FULL', @Verify = 'N', @NumberOfFiles='4', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'

What output are you getting? Error: Msg 0, Sev 0, State 1: Unknown token received from SQL Server [SQLSTATE HY000] String data, right truncation [SQLSTATE 01004] Msg 16389, Sev 16, State 1: The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted. [SQLSTATE 08S01]

[eltonkerber]

I fixed the code as the recommendation, just in case anyone else need.

DatabaseBackup.txt

[olahallengren]

To solve this issue capture the table output to a variable, this would eliminate it from the output file, as it is stored in memory only and discarded:

Does this mean that information that was printed in the previous version will now be discarded?