SqlQuantumLeap

SQL Agent Job: String Pattern Hidden From History

Jun 19th, 2019
87
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

ร—