/*
 ----- 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