Prevent SQL job running when another job is running

 
CREATE VIEW myschema.Admin_RunningSQLJobs
AS
SELECT job.NAME
,job.job_id
,job.originating_server
,activity.run_requested_date
,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
JOIN (
SELECT MAX(agent_start_date) AS max_agent_start_date
FROM msdb.dbo.syssessions
) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL
AND stop_execution_date IS NULL


Using this view we can check for a running job before running our job…
In this example we don’t want the nightly pricing build to run if a monthly build is in progress, note, nightly is scheduled later than monthly. So wrap the call to the stored procedure in the job step like this…
 
IF NOT EXISTS(SELECT * FROM myschema.Admin_RunningSQLJobs WHERE name='Pricing - Monthly Rebuild') 
BEGIN
EXEC myschema.Nightly Pricing build
END

Note about backups:
We restore the live company into the test company of our ERP system, once a week, to keep the test company data fresh. It is worth noting that it is wise to keep the above SQL view in a database that is not involved in a restore, otherwise errors will occur trying to access the view as that database is in restore mode and cannot be accessed by the SQL job.
For this reason we keep this view in an “admin” database that is not part of the automated restore process, using the view in SQL jobs used against the test database prevents them erroring when the restore process in in progress.
 

SQL formatting and Power Query

Today I got caught out by using SSMSBoost to SQL format a query being used as the source for a Microsoft Power Query.

On updating the stored procedure the query stopped working in Power Query, with the error that the column “Temp” was missing.

Looking at the query output in SSMS, I could see that the column had changed to TEMP in upper case from Temp in the original query, easily fixed. Then I realised what might have happened to cause it and proved it. Formatting the SQL had caused the temp column to be upper cased, my suspicion is that Temp was wrongly identified as a keyword by the formatting template and thus with the setting in SSMSBoost being to uppercase keywords, it got uppercased. Power Query is case sensitive so got upset.

SSMSBoot Settings Menu showing the UPPERCASE keywords = True

Just a warning for those of you that use SQL formatting tooling, one to keep an eye out for, luckily having a column named Temp is not too common.

Recovering a deleted Reporting Services Report

Yes, I deleted the wrong report while housekeeping. This raised the question as to how to recover it once it has been deleted? There is no recycle bin to or undelete option in reporting services on the version I work with and at the time of writing (Nov 2017).

It turns out to be very simple and quick to restore, if you have backups of your “ReportServer” database, which of course every one has.

Option 1 – Restore to point in time

The simplest way is to restore the “ReportServer” database from backup, to a point in time just before the deletion, but this would loose any changes on the report server made since it was deleted. In my case I had spent a few hours after deleting the report with housekeeping all all kinds of things on the report server, so this was not desirable as I’d loose that work.

Option 2 – Restore the single report from backup

Slightly more involved as an option, but still quick and easy, follow the steps below, it only takes a few minutes to do.

To restore the “ReportServer” database

    • Right click the database in SSMS, select restore
    • Rename the Destination Database to ReportServerRestored
    • Use the check boxes to find the point in time to restore to, here I chose to not apply the logs to make for a speedy restore
    • Click Files on page selector in left hand side. If the default path is not appropriate (under Restore As, in the grid, you may need to scroll left to see), then change the path that the database files will be restored to
    • Click options on page selector in left hand side to select options. Unselect Take tail-log backup.
    • Click ok to start the restore

2017-11-02_12-07-37

Extract the XML that represents the report from the restored database

Reports are stored in a binary field in a table in the database as XML. The following article explains how to get at the XML that defines the reports,

 Extracting SSRS Report RDL (XML) from the ReportServer database

Using the information in this article we can get to the report we need by running the script in SSMS.

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServerRestored.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
ItemID,Name,[Type],TypeDescription
,Content --varbinary
,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
,CONVERT(xml,Content) AS ContentXML --xml

FROM ItemContentNoBOM
where name like '%{enter part of report name here}%'

Note you need to edit the {enter par of report name here} to be what it says, you will then be presented with the row(s) of interest. Work out which is the report you need if multiple rows are returned (note the restored database name is embedded into the SQL, change if you restored to another name).

 2017-11-02_12-35-35

On the row, example shown above, click the hyperlink of the column “ContentXML” to open the XML in an XML editor within SSMS (cool feature). Then go to File>>Save As.. within SSMS to save to a drive for later import to the report server.

Rename the xml file

Rename the xml file as a .rdl file you can make the filename an appropriate descriptive name for the report as this will later show in the report server

Upload the report definition file to the server

Upload the .rdl file to the reports folder on the reporting services server. Navigate to the folder it should reside in and select “Upload File” as shown below

2017-11-02_12-22-29

Recreate subscriptions and schedules

You will have to manually set up subscription schedules again for the report if they existed before.

Delete the restored database

From SSMS right click the database name and select delete to remove it. Check that the database files are also deleted on the server.

 

The report is now restored and the task is completed!

If you found this useful please comment, it helps motivate me to keep adding to the blog…

SSMS SQL Hazard

Let me point out a hazard using SSMS, when developing a SQL delete query, especially more complex ones that take time to build up. I also think about some of the ways I work and how they minimise the chances of damage to data.
Let us say we are developing the simple query to remove orphan records from the prices table of Dynamics GP:
 
DELETE IV00107
SELECT *
FROM IV00107
LEFT JOIN IV00108
ON IV00107.ITEMNMBR=IV00108.ITEMNMBR
and IV00107.CURNCYID=IV00108.CURNCYID
AND IV00107.PRCLEVEL=IV00108.PRCLEVEL
AND IV00107.UOFM=IV00108.UOFM
WHERE
IV00107.PRCLEVEL='LIST'
AND IV00108.ITEMNMBR IS NULL
 
…as we develop it, I highlight from SELECT down to NULL and hit F5 to run it. That will only run the “select”, to see what it will be deleting later. Later I would put double dash in front of the select to and run the whole statement to get the delete to execute.
 
Let us say we have a break, then come back having got the approval to delete the records. So our eye catches the delete and so we highlight from DELETE to NULL and hit F5. Oh no! We have just deleted everything in IV00107! Think about it, the delete and SELECT are interpreted (correctly) as two different operations.
 
Working this way, developing the record set to remove as a SELECT, then adding the DELETE to the top ready to be ran is a common pattern for me. Although I usually put a double dash in front of the DELETE as shown below to prevent this mistake or the mistake of just hitting F5 during testing, without remembering to select first. The action of uncommenting the DELETE triggers my mind to also then comment out the SELECT.
 
--DELETE IV00107
SELECT *
FROM IV00107
LEFT JOIN IV00108
ON IV00107.ITEMNMBR=IV00108.ITEMNMBR
and IV00107.CURNCYID=IV00108.CURNCYID
AND IV00107.PRCLEVEL=IV00108.PRCLEVEL
AND IV00107.UOFM=IV00108.UOFM
WHERE
IV00107.PRCLEVEL='LIST'
AND IV00108.ITEMNMBR IS NULL

I made this mistake against a test SQL database today, a reminder of why you should develop against a test environment. Luckily I use SSMSBoost, a productivity pack plug in for SSMS. This addin to SSMS will warn when you are executing a delete without a where clause, throwing up a fatal action guard window,  so it prevented me from actually causing any damage.
 
ssmsboost fatal action guard window
 
Immediately I could see what I had done and hit the No button.
 
Another factor that led to this potential mistake was not aliasing my tables like I normally would do, if I had written the following then the DELETE would not have been able to find the table i7 and hence would not have executed and would return;

Msg 208, Level 16, State 1, Line x
Invalid object name 'i7'

DELETE TOP(1000) i7
SELECT *
FROM IV00107 i7
LEFT JOIN IV00108 i8
ON i7.ITEMNMBR=i8.ITEMNMBR
and i7.CURNCYID=i8.CURNCYID
AND i7.PRCLEVEL=i8.PRCLEVEL
AND i7.UOFM=i8.UOFM
WHERE
i7.PRCLEVEL='LIST'
AND i8.ITEMNMBR IS NULL
 
Yet another practice that would have helped is that I also would normally have the TOP statement in the query. I would then pressing F5 ten times, in this case until there are no more rows to process. I use TOP to make it run faster and not cause lock escalation (we have 2million records in price table). Other times if there is too much to remove from F5, I’d put it in a loop with a @ROWCOUNT check to see if anything is left to process, that would not have stopped a disaster though. In reality a delete on this table would have taken so long I would soon see my mistake and cancel the query before it committed, but I’m showing a principle here in this post. So the TOP statement would have prevented quite so much data loss.
 
Luckily I didn’t end up with any issues at all, but as is often the case an accident only happens when the perfect storm of factors come together. Keeping to my normal way of working protects me but I thought others might learn from why I work the way I do.
 
If I had removed the records in production, then it would not have been a bit deal in this case as we restore our production database into our test company regularly and automatically (see my post on how to do this). As these prices do not change much from day to to, I could have just squirted the missing records from that company into production, then restored to point in time the test company and again re-synced the prices from that restore, resulting in no impact on users.  This is one of the reasons I’m a fan of having fresh copies of production available in test, also useful for patching up mistakes users make in a timely manner.