I was hoping you all could help me with an SQL 2016 ReportServer issue I’m having.
Problem:
I have a report server named PROD and I’m trying to get refreshed views from that report server so I can modify reports in Visual Studios, SSDT 2015 so I can modify a report, WIP Report. The view I’m looking for is the TWIP_ table. I run the report in Epicor. Go back to SSDT and run:
use [databasename]
SELECT Company,CreatedOn,RptDescription, RIGHT(REPLACE([FILENAME], ‘-’,‘’), 32) AS [GUID]
FROM Ice.SysRptLst
Order by 2 desc
I get a list of the GUID’s, but they’re not updated in the Views even after I refresh. Okay, next step…
I went to the SQL database and ran an sp_refreshview for all views in the database, hit’em all I say. None of the newly created report views showed up in the views on the PROD report server.
Just as I side note if I create the view, right click views on the PROD report server and select New View, try dbo.View_TWIP and use that GUID it will error out on with: **
Invalid object name ‘TWip_cb49b49e4ef740e2910a324c70728e12’
Are you looking at the Report ‘temp data’ DB Server or the core ERP db?
If you are not aware of the architecture, ERP builds up the content of reports and extracts the data into a ‘Report Temp Data’ db so SSRS can run against that ‘Guid’ table
I understand that, I can’t pull the View from the ERP Database, I pull the view from the ReportServer DB. I need the stored view from either the ReportServer’s Table_GUID or the Views dbo.GUID inorder to get the RDD and parameter information.
The ReportServerTempDB doesn’t have an accessible view for me to use for the report in order to add tables, columns, etc…
I use to be able to run a report in Epicor and retrieve the GUID in VS then make the needed changes I needed to for that report. That’s not happening right now. I run the report in Epicor . Retrieve the GUID in VS but it’s an invalid object name.
Are you archiving the data? By default the temp data is purged after the report is rendered. In that case you go to look after report is done and poof - we cleared it before you got there.
