Auto Generate INSERT statements for a SQL Server 2008 Table using SSMS

I was doing some SSIS development for a client and had to generate and alter some test data for some packages.

I already had a table with values in that I wanted to capture via INSERT statements, so that I could chop and change the values as needed.

There is a method in SQL Management Studio 2008 (SSMS) that allows you to create these statements right out of the box for SQL Server 2008, without using any scripts or add-ins from SSMS.

So, for example,  to create the INSERT statements for the Table: HumanResources.Department in the AdventureWorks database.          (Note: Click on images to enlarge)

Chosen Table

Right Click on the Database Name, select Tasks|Generate Scripts…

Click the Next button on the “Generate and Publish Scripts” dialog box that appears.

From the “Choose Objects” screen, select the table from drop down menu. We are doing this for a single table, but as you can see you can do this for multiple objects or the entire database.

Click Next.

On the next screen, decide where you wish to output the script to. I chose a “new query window” in my example.

Then Press the Advanced Button.

From the Advanced Scripting Options dialog, select the Drop-down options for the “Types of data to script” option.  You can choose (i) Data Only (ii) Schema and data or (iii) Schema only

Click OK, and then select the Next Button. Click the Next button again from the next screen and then the Finish button after the task has completed

In this example, the CREATE TABLE and INSERT statements appear for my chosen database table within a new query window in SSMS.

I can now extract and edit the INSERT statements, as required, to create new or different test data for my SSIS project.

Enable or Disable a SSRS Report Subscription based upon another task or event.

Scenario

Whilst at a client site, I was asked whether you could stop (disable) a users Report Subscription based upon an external event, such as another SQL Agent Job or Task outcome, such as failing.

The approach I took to demonstrate how you could achieve this was to create another SQL Agent Job which “enabled” the users Report Subscription Job, based upon the occurrence of a “flag” record in another table. This table could easily be created by the calling control job, or a prior job step.

Steps

Here we can see a Report Subscription that has been created by a user: (click on any image for full-size view)

Subscription Details:

The job is set to run at 19:12.

Using SSMS (SQL Server Management Studio) and connecting to the Database that the “ReportServer” database resides on,  we can use the “Job Activity Monitor” to see the Job that was automatically created for this subcription.

Job Name: A48C0610-4A9F-4847-97E2-BAF7798DEE0C

We can write a SQL Script to view the Job Details:

USE msdb

SELECT *
FROM msdb.dbo.sysjobs
WHERE name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’

To view the Details of all the Jobs on the Server we can use the following script:

USE msdb

GO

SELECT Getdate() AS ‘ExecutionTime’

GO

SELECT @@SERVERNAME AS ‘SQLServerInstance’

GO

SELECT j.[name] AS ‘JobName’,
enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM msdb.dbo.sysjobs j
INNER JOIN MASTER.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

GO

Script to Enable/Disable Job

/* script to disable by job */
EXEC msdb.dbo.Sp_update_job
@job_name = ‘A48C0610-4A9F-4847-97E2-BAF7798DEE0C’,
@enabled = 0; — 0 to disable 1 to enable
GO

Therefore, we could setup another SQL Job, which will run this script dependant upon some logical condition. i.e. where a certain value or number of records exists in a table, etc.

To enable/disable the job based upon say records, in a table, adapt the following script accordingly to your requirements. I have used a temporary table to demonstrate the principle.

 /* script to enable(1) or disable(0) by job based upon records in table @flagtable */ 
USE msdb 
 
DECLARE @flagtable TABLE ( 
 flag INT) 
DECLARE @DoIt INT 
 
SET @DoIt = 0 
 
-- let?s add a record to this table for test purposed. To Disable the Job we would comment out the following two rows 
INSERT INTO @flagtable 
 (flag) 
VALUES (1) 
 
-- so lets say we will enable the job if a record exists in the @flagtable 
SELECT @DoIt = COUNT(*) 
FROM @flagtable 
 
/* script to enable(1) or disable(0) by job based upon records in set @flagtable */ 
EXEC msdb.dbo.Sp_update_job 
 @job_name = 'A48C0610-4A9F-4847-97E2-BAF7798DEE0C', 
 @enabled = @DoIt; 
 
GO 

Script to DISABLE the SQL Agent Job as @DoIt = 0

Script to ENABLE the SQL Agent Job as @DoIt = 1

So, to automate this task we can set up a new SQL Agent Job to run this script as shown;

Create the following Step, and add the T-SQL to enable or disable the Job as required.

Hence, if  more than 1 record exists in our “flag” table, it will ENABLE the Report subscription Job.

If no records exist in the “Flag” table, the Report Subscription Job will be set to Enabled = No (i.e. Disabled).

Remember, you will use your own logic to set the @DoIt parameter based upon your own requirements.

You can run this Job Manually or on a Scheduled as required:

Conclusion

Though I applied this to a Report Subscription Job, it could easily be adapted for other types of Jobs.

The beauty of it is that the original Job and its associated schedule timings remain unaltered. All we are doing is disabling/enabling the Job as necessary.