SHARE
TWEET

SQL Agent Job: String Pattern Hidden From History

SqlQuantumLeap Jun 19th, 2019 (edited) 16 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  ----- Specific String Pattern Hidden From SQL Agent Step Output -----
  3.  
  4. Created By: Solomon Rutzky / Sql Quantum Leap
  5. Created On: 2019-06-19
  6. Updated On: n/a
  7.  
  8. Script Location: https://pastebin.com/vp7Yh3VJ
  9. Related Article: https://sqlquantumleap.com/2019/06/20/specific-string-pattern-magically-hidden-from-step-output-in-sql-agent-history/
  10. ----------------------------------------------------------------------
  11. */
  12.  
  13. USE [msdb];
  14. GO
  15.  
  16.  
  17. BEGIN TRY
  18.  
  19.   BEGIN TRANSACTION;
  20.  
  21.   DECLARE @ReturnCode INT,
  22.           @jobId BINARY(16);
  23.  
  24.   SELECT @ReturnCode = 0;
  25.  
  26.  
  27.  
  28.   EXEC @ReturnCode =  msdb.dbo.sp_add_job
  29.           @job_name = N'Output History Test',
  30.           @enabled = 0,
  31.           @notify_level_eventlog = 0,
  32.           @notify_level_email = 0,
  33.           @notify_level_netsend = 0,
  34.           @notify_level_page = 0,
  35.           @delete_level = 0,
  36.           @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/ )
  37.  
  38. SCRIPT LOCATION: https://pastebin.com/vp7Yh3VJ
  39.  
  40. NOTE: You might need to manually create the "C:\Temp\" folder/directory for the output file.
  41. ',
  42.           @category_name = N'[Uncategorized (Local)]',
  43.           @owner_login_name = N'sa',
  44.           @job_id = @jobId OUTPUT;
  45.  
  46.   IF (@ReturnCode <> 0)
  47.   BEGIN
  48.       RAISERROR(N'Error: sp_add_job exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  49.   END;
  50.  
  51.  
  52.   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
  53.           @job_id = @jobId,
  54.           @step_name = N'OUTPUT HISTORY TEST',
  55.           @step_id = 1,
  56.           @cmdexec_success_code = 0,
  57.           @on_success_action = 1,
  58.           @on_success_step_id = 0,
  59.           @on_fail_action = 2,
  60.           @on_fail_step_id = 0,
  61.           @retry_attempts = 0,
  62.           @retry_interval = 0,
  63.           @os_run_priority = 0,
  64.           @subsystem = N'TSQL',
  65.           @command = N'
  66. -- 1) Non-space / non-return before delimited text:
  67. PRINT ''~[a][b][😱][d][A][B][πŸ™€][D][1]'';
  68.  
  69.  
  70. -- 2) No spaces / returns:
  71. PRINT ''[a][b][😱][d][A][B][πŸ™€][D][2]'';
  72.  
  73.  
  74. -- 3) Spaces / returns inside [b]:
  75. PRINT ''[a][ b ][😱][d][A][B][πŸ™€][D][3]'';
  76.  
  77.  
  78. -- 4) Spaces / returns before [a]:
  79. PRINT ''      [a][b][😱][d][A][B][πŸ™€][D][4]'';
  80.  
  81.  
  82. -- 5) Spaces / returns after [b]:
  83. PRINT ''[a][b]          [😱][d][A][B][πŸ™€][D][5]'';
  84.  
  85.  
  86. -- 6) Spaces / returns plus non-space/non-return after [b]:
  87. PRINT ''[a][b]       *     [😱][d][A][B][πŸ™€][6]'';
  88.  
  89.  
  90. -- 7) Only spaces preceding first non-space character:
  91. PRINT ''       *     [😱][d][A][B][πŸ™€][7]'';
  92.  
  93. ',
  94.           @database_name = N'master',
  95.           @output_file_name = N'C:\Temp\OutputHistoryTest.txt',
  96.           @flags = 6;
  97.  
  98.   IF (@ReturnCode <> 0)
  99.   BEGIN
  100.       RAISERROR(N'Error: sp_add_jobstep exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  101.   END;
  102.  
  103.  
  104.  
  105.   EXEC @ReturnCode = msdb.dbo.sp_update_job
  106.           @job_id = @jobId,
  107.           @start_step_id = 1;
  108.  
  109.   IF (@ReturnCode <> 0)
  110.   BEGIN
  111.       RAISERROR(N'Error: sp_update_job exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  112.   END;
  113.  
  114.  
  115.   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
  116.           @job_id = @jobId,
  117.           @server_name = N'(local)';
  118.  
  119.   IF (@ReturnCode <> 0)
  120.   BEGIN
  121.       RAISERROR(N'Error: sp_add_jobserver exited with @ReturnCode = %d.', 16, 1, @ReturnCode);
  122.   END;
  123.  
  124.  
  125.   COMMIT TRANSACTION;
  126.  
  127. END TRY
  128. BEGIN CATCH
  129.  
  130.   IF (@@TRANCOUNT > 0)
  131.   BEGIN
  132.       ROLLBACK TRANSACTION;
  133.   END;
  134.  
  135.   DECLARE @ErrorMessage NVARCHAR(4000),
  136.           @ErrorNumber INT,
  137.           @ErrorState INT,
  138.           @ErrorSeverity INT;
  139.  
  140.   SELECT @ErrorMessage = ERROR_MESSAGE(),
  141.          @ErrorState = ERROR_STATE(),
  142.          @ErrorSeverity = ERROR_SEVERITY(),
  143.          @ErrorNumber = ERROR_NUMBER();
  144.  
  145.   RAISERROR(N'Msg %d: %s', @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorMessage);
  146.  
  147. END CATCH;
  148.  
  149. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top