Voice of the DBA

One of our internal people was looking to test some process in (I assume) Redgate Monitor and needed more job history than they had in msdb.sysjobhistory. They wanted to use SQL Data Generator to help, but couldn’t make it work.

This gives the solution I sketched for them. It can work with some other system tables, but not all. Many system tables do not allow user data to be inserted. Some do.

The Main Problem

The main problem here is that SQL Data Generator doesn’t see system tables. If I open a project in msdb, I see this:

That matches what I see in SSMS. Only user tables.

However, I know I can do this and it works.

INSERT INTO dbo.sysjobhistory
  (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
VALUES
  ('EA6B3BC3-D358-4B0C-A793-8C3C558098AB', 0, 'mystep', 0, 0, 'Executed as user: NT Service\SQLAgent$SQL2022. The job script encountered the following errors.'
  , 0, 20241111, 175438, 0, 0, 0, 0, 0, 'ARISTOTLE\SQL2022')

Now, how to get SQL Data Generator to help?

A Little System Table ETL

Since I know I can insert data into the table, how can I generate data? Apparently SQL Data Generator cannot read these tables, but I can use a trick that I’ve used in the past.

First, I’ll run this in msdb:

SELECT *
  INTO mysysjobhistory
  FROM dbo.sysjobhistory AS s
  WHERE 1 = 0

This code will make a copy of dbo.sysjobhistory with no data. However, this is a user table. Once I do this, now I can refresh SQL Data Generator and I can see my table.

Now I can use the settings to get the type of data I want. Here’s a preview of some data I set, using the data in my existing table.

Now, I can click generate data and I have data added to my user table. If I query this table, I see data:

The last step is to move this data. I’ll use this query:

INSERT dbo.sysjobhistory
   (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)

SELECT job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server
  FROM dbo.mysysjobhistory AS m

Once this runs, I can see the data in msdb.

Of course, I’d also have to populate sysjobs if I wanted this linked to a job and shown in the Agent Job History Viewer.

Summary

This post showed how I’ve sometimes worked in situations where I couldn’t directly access a table from an application. In this case, I want to get data into sysjobhistory, but SQL Data Generator doesn’t support that directly. My solution:

1. make a copy of the table
2. insert data into the copy
3. move the data into the original

This has worked in a few situations as well where I might need to move/stage data before it gets into an application table. In this case, we wanted to generate some random history for Redgate Monitor to read.

This can work for other tables as well, as long as you can insert..select into them.

SQL Data Generator is a neat tool to generate data quickly for a variety of purposes in SQL Server. Give it a try, especially if you already have the Toolbelt Essentials.