Pasted

/*
 ----- Specific String Pattern Hidden From SQL Agent Step Output -----
 
Created By: Solomon Rutzky / Sql Quantum Leap
Created On: 2019-06-19
Updated On: n/a
 
Script Location: https://pastebin.com/vp7Yh3VJ
Related Article: https://sqlquantumleap.com/2019/06/20/specific-string-pattern-magically-hidden-from-step-output-in-sql-agent-history/
----------------------------------------------------------------------
*/
 
USE [msdb];
GO
 
 
BEGIN TRY
 
  BEGIN TRANSACTION;
 
  DECLARE @ReturnCode INT,
          @jobId BINARY(16);
 
  SELECT @ReturnCode = 0;
 
 
 
  EXEC @ReturnCode =  msdb.dbo.sp_add_job
          @job_name = N'Output History Test',
          @enabled = 0,
          @notify_level_eventlog = 0,
          @notify_level_email = 0,
          @notify_level_netsend = 0,
          @notify_level_page = 0,
          @delete_level = 0,
          @description = N'RELATED ARTICLE: "Specific String Pattern Magically Hidden From Step Output in SQL Agent History" ( https://sqlquantumleap.com/2019/06/20/specific-string-pattern-magically-hidden-from-step-output-in-sql-agent-history/ )
 
SCRIPT LOCATION: https://pastebin.com/vp7Yh3VJ
 
NOTE: You might need to manually create the "C:\Temp\" folder/directory for the output file.
',
          @category_name = N'[Uncategorized (Local)]',
          @owner_login_name = N'sa',
          @job_id = @jobId OUTPUT;
 
  IF (@ReturnCode <> 0)
  BEGIN
      RAISERROR(N'Error: sp_add_job exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  END;
 
 
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
          @job_id = @jobId,
          @step_name = N'OUTPUT HISTORY TEST',
          @step_id = 1,
          @cmdexec_success_code = 0,
          @on_success_action = 1,
          @on_success_step_id = 0,
          @on_fail_action = 2,
          @on_fail_step_id = 0,
          @retry_attempts = 0,
          @retry_interval = 0,
          @os_run_priority = 0,
          @subsystem = N'TSQL',
          @command = N'
-- 1) Non-space / non-return before delimited text:
PRINT ''~[a][b][😱][d][A][B][🙀][D][1]'';
 
 
-- 2) No spaces / returns:
PRINT ''[a][b][😱][d][A][B][🙀][D][2]'';
 
 
-- 3) Spaces / returns inside [b]:
PRINT ''[a][ b ][😱][d][A][B][🙀][D][3]'';
 
 
-- 4) Spaces / returns before [a]:
PRINT ''      [a][b][😱][d][A][B][🙀][D][4]'';
 
 
-- 5) Spaces / returns after [b]:
PRINT ''[a][b]          [😱][d][A][B][🙀][D][5]'';
 
 
-- 6) Spaces / returns plus non-space/non-return after [b]:
PRINT ''[a][b]       *     [😱][d][A][B][🙀][6]'';
 
 
-- 7) Only spaces preceding first non-space character:
PRINT ''       *     [😱][d][A][B][🙀][7]'';
 
',
          @database_name = N'master',
          @output_file_name = N'C:\Temp\OutputHistoryTest.txt',
          @flags = 6;
 
  IF (@ReturnCode <> 0)
  BEGIN
      RAISERROR(N'Error: sp_add_jobstep exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  END;
 
 
 
  EXEC @ReturnCode = msdb.dbo.sp_update_job
          @job_id = @jobId,
          @start_step_id = 1;
 
  IF (@ReturnCode <> 0)
  BEGIN
      RAISERROR(N'Error: sp_update_job exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  END;
 
 
  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
          @job_id = @jobId,
          @server_name = N'(local)';
 
  IF (@ReturnCode <> 0)
  BEGIN
      RAISERROR(N'Error: sp_add_jobserver exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  END;
 
 
  COMMIT TRANSACTION;
 
END TRY
BEGIN CATCH
 
  IF (@@TRANCOUNT > 0)
  BEGIN
      ROLLBACK TRANSACTION;
  END;
 
  DECLARE @ErrorMessage NVARCHAR(4000),
          @ErrorNumber INT,
          @ErrorState INT,
          @ErrorSeverity INT;
 
  SELECT @ErrorMessage = ERROR_MESSAGE(),
         @ErrorState = ERROR_STATE(),
         @ErrorSeverity = ERROR_SEVERITY(),
         @ErrorNumber = ERROR_NUMBER();
 
  RAISERROR(N'Msg %d: %s', @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorMessage);
 
END CATCH;
 
GO