updating ReportServer.dbo.Catalog directly: too smart for my own good?

• Lowell

  SSC Guru

  Points: 323506

  More actions

  February 11, 2015 at 6:24 am

  #304191

  preface: SSRS GUI is so clunky to me, i tried to avoid it.

  So there's a suite of reports on one of my servers that i can't find the original project for.

  i just wanted to tweak one tiny little snippet of code, though, so i got fancy.

  i extracted the image of one specific report back to an xml file, myreport.rdl. via some CLR functions i use all the time.

  DECLARE @image varbinary(max)

  select @image = Content from reportserver.dbo.Catalog where name ='MyReport'

  exec dba_utilities.dbo.CLR_SaveFileImage 'D:\temp','MyReportV2.rdl',@image

  I wanted to change the <CommandText> that is used for a specific datasource, so i did in a plain old text editor, and then updated the Content right back.

  DECLARE @image varbinary(max)

  select @image = dba_utilities.dbo.CLR_GetFileImage('D:\temp','MyReport.rdl')

  UPDATE reportserver.dbo.Catalog

  SET Content = @image

  where name ='MyReport'

  the CLR for image read/write is bulletproof, there's definitely no errors there; i can compare old and new and definitely see the changes in the report definition .

  so i kick off the subscription for that report, and immediately run sp_whoisactive. i get the name of the job and the command to kick off the job via this query:

  SELECT distinct 'exec msdb.dbo.sp_start_job @job_id = ''' + convert(varchar(40),b.job_id) COLLATE SQL_Latin1_General_CP1_CI_AS + '''; --' + + b.name COLLATE SQL_Latin1_General_CP1_CI_AS + ' : ' + e.name COLLATE SQL_Latin1_General_CP1_CI_AS As Cmd,

  b.job_id As JobID,

  b.name AS JobName

  , e.name

  , e.path

  , d.description

  , a.SubscriptionID

  , laststatus

  , eventtype

  , LastRunTime

  , date_created

  , date_modified

  FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b

  ON convert(varchar(40),a.ScheduleID) = b.name

  JOIN ReportServer.dbo.ReportSchedule c

  ON b.name = convert(varchar(40),c.ScheduleID)

  JOIN ReportServer.dbo.Subscriptions d

  ON c.SubscriptionID = d.SubscriptionID

  JOIN ReportServer.dbo.Catalog e

  ON d.report_oid = e.itemid

  where 1 = 1

  and e.name = 'MyReport'

  order by e.name

  sp_whoisactive is showing the OLD SQL statement that is WAS in the <CommandText> of the Datasource, and not the new one.

  ok so i thought, maybe reporting services caches the content, so i stopped and started the Reporting Services Service via RS Configuration Manager, and ran the SSRS report again.

  sp_whoisactive shows that it's definitely the old command still, regardless of the direct update i made to the [Content] column.

  so my question is for a subscription, is the report definition copied to a second location, so i need to change it there too?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• peterzeke

  SSCrazy Eights

  Points: 9003

  More actions

  May 27, 2015 at 11:58 am

  #1801046

  Lowell,

  Did you ever figure this out? I have a similar interest in updating the Content field in the Catalog table for various reports, rather than opening each report individually in visual studio. My initial attempts have failed -- the Content field reflects my changes, but the SSRS report manager still renders the reports with the old rdl info. Makes me think that reporting services stores a copy of the rdls somewhere else besides the Catalog table.

  --Pete

• Lowell

  SSC Guru

  Points: 323506

  More actions

  May 30, 2015 at 3:32 am

  #1801797

  this is what i THINK i found out. If the report has a subscription, the rdl is cached in another table, and its bibary, no way to tweak it. i ended up editing the reports as described, then redoing all the subscriptions. at some point a couple were not right after editing, and a redid the rdl in BIDS. Im looking for a way to scrupt out subscriptions so i can rebuild them dynamically

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  May 30, 2015 at 3:46 am

  #1801800

  I think the reports are "compiled" into the Segments table, so not easily changeable via TSQL.

  You can use Powershell and the SSRS Web Services, here is a starter link: https://msdn.microsoft.com/en-GB/library/dn747196.aspx

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]