Pull dynamic reportpath of RDL into SQL inside the RDL.

Question

Pull dynamic reportpath of RDL into SQL inside the RDL.

david_h_edmonds

Default port

Points: 1430
More actions
October 14, 2019 at 2:10 pm

#3688497
Hello,
Long story of not best practice that I have inherited and am slowly trying to change. (No judgement please).
I have a set of approx. 1000 report server reports that have SQL embedded in them (Insert sad face).
Some of the reports are locking each other and sometimes taking down databases.
My idea is to add some code to the embedded SQL that identifies if the same report is already running and if so raise a message to the user to say they can't currently run it.
My issue is that I am trying to see if it is possible to pull the reportserver path of the RDL the code is in at execution time and supply it to the embedded SQL in order to make a comparison against reportserver.dbo.RunningJobs.
I am quite prepared to listen to other ideas and also to be told it isn't possible however, here is my current code.
I am trying to make the string on line 2 dynamic
```
EXEC dbo.UDSP_Running_SSRS_Jobs; -- contains a global temp table called ##tmp

DECLARE @path VARCHAR(max) = '/ReportPath/ReportName'
DECLARE @CurrentUser VARCHAR(max) = (SELECT UserName  FROM ##tmp  r WHERE r.requestpath = @path)
declare @message varchar(max)

IF @Path IN (SELECT DISTINCT requestpath FROM ##tmp)

SET @message = CONCAT(convert(varchar(max),getdate(),120),'_','This Report is currently being run by' ,'_', @CurrentUser, '_', 'Please try in a few minutes')
raiserror(@Message,10,10) with NOWAIT;

IF @Path NOT IN (SELECT DISTINCT requestpath FROM ##tmp)


-- PLACE REPORT CODE HERE
```
Any help gratefully appreciated.
Using SQL Server 2012 and SSRS 2012

Cheers,
Dave

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

is the locking at the sql level (ie in sql monitor or sp_who2 you can see a blocked process)???

I know with 1000 reports it's not feasible to go through all of the ones that commonly block and modify the code to use WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

It's also not always recommended to do this with the prospect of phantom rows etc

alternately you might want to scrape that value from the URL of the report and create a single proc that is called at the start of the report - if the returnvalue is 1 (meaning it is running) then get the code within the report to do a response.redirect, rather than a messy raiserror

https://docs.microsoft.com/en-us/sql/reporting-services/url-access-ssrs?view=sql-server-ver15

MVDBA

david_h_edmonds Default port Points: 1430 More actions · Answer 2

Hi, cheers for the response.

Yes the locking is at the SQL level and yes I am trying to avoid no lock on 1000 reports. 🙂

The scraping of the URL is exactly what I am after.

I will give the link a read.

Cheers,

Dave

david_h_edmonds Default port Points: 1430 More actions · Answer 3

Apparently it should be possible to use global variables inside SSRS to get the info I need.

I should be able to use: =Globals!ReportFolder + "/" + Globals!ReportName in a parameter expression.

My problem is that despite already being deployed to a folder on the report server, 9 times out of 10, Globals!ReportFolder is blank.

Any ideas?

Cheers,

Dave

hemhemhemhem Newbie Points: 4 More actions · Answer 4

Possibly off-topic, but it occurs to me: is there any reason you can't use report / dataset caching and/or report snapshots to attempt to alleviate some of the pressure on the db? I don't know that it will take you 100% of the way there, but it could be a band-aid for the moment?