Find SSRS reports with a default GUID value set

Epicor ERP 10
, ,
1

Had a user tell me a report had stopped working, only to find out I had left a default GUID in the report, when it was being tested.

Any SQL Query I could run to find a list of reports with anything in the GUID parameter?

The SysMon Error included:

An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 
  Query execution failed for dataset 'OrderHed'. ---> System.Data.SqlClient.SqlException: 
    Invalid object name 'OrderHed_922e7373f70e4cb8af249f158c832fc4'.
2

You’ll want to do something like this where you scan Information_Schema.Tables

3

You can query ReportServer.dbo.Catalog table where ReportServer is is the SSRS report server database. The Parameter column contains an xml listing of all the parameters:

use ReportServer

select cast(Parameter as XML), * from Catalog

Example:

<Parameters>
  <UserProfileState>0</UserProfileState>
  <Parameter>
    <Name>TableGuid</Name>
    <Type>String</Type>
    <Nullable>False</Nullable>
    <AllowBlank>False</AllowBlank>
    <MultiValue>False</MultiValue>
    <UsedInQuery>True</UsedInQuery>
    <State>MissingValidValue</State>
    <Prompt>TableGuid</Prompt>
    <DynamicPrompt>False</DynamicPrompt>
    <PromptUser>True</PromptUser>
  </Parameter>
  <Parameter>
    <Name>Company</Name>
    <Type>String</Type>
    <Nullable>True</Nullable>
    <AllowBlank>True</AllowBlank>
    <MultiValue>False</MultiValue>
    <UsedInQuery>True</UsedInQuery>
    <State>MissingValidValue</State>
    <Prompt />
    <DynamicPrompt>False</DynamicPrompt>
    <PromptUser>True</PromptUser>
    <DefaultValues>
      <Value />
    </DefaultValues>
    <Values>
      <Value />
    </Values>
  </Parameter>
  <Parameter>
    <Name>InvoiceNum</Name>
    <Type>Integer</Type>
    <Nullable>True</Nullable>
    <AllowBlank>False</AllowBlank>
    <MultiValue>False</MultiValue>
    <UsedInQuery>True</UsedInQuery>
    <State>MissingValidValue</State>
    <Prompt />
    <DynamicPrompt>False</DynamicPrompt>
    <PromptUser>True</PromptUser>
    <DefaultValues>
      <Value>0</Value>
    </DefaultValues>
    <Values>
      <Value>0</Value>
    </Values>
  </Parameter>
</Parameters>
1 Like
4

Well, that just lists the tables … Which I can already see in SSMS.

By browsing the tables, I’ve found that it looks like a default table GUID is stored dbo.Catalog.Parameter

But the Parameter field holds XML, so searching for segments like:

    <DefaultValues>
      <Value>86b410ac25c54841b19844be191fc7eb</Value>
    </DefaultValues>

I ended up using:

select ItemID, Name, Parameter
FROM dbo.Catalog 
WHERE Parameter LIKE '%Parameter%TableGUID%DefaultValues%<Value>%[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]%/Values%/Parameter%'
2 Likes
5

I use this to browse all report successfully run. It shows the GUID

Select * from Ice.SysRptLst order by RecSeq desc
6

I’m not looking for the reports that have been run. But rather reports that have a default TableGUID parameter set in them.

During development of a report I’ll enter a value for the default TableGUID, so I don’t have to enter it everytime I want to preview the report in the SSRS builder.

But I often forget to remove the default TableGUID. Then when that report eventually expires (past the archive period), the info no longer exists, and the report fails to run.

1 Like
7 
select name from Epicor10Reports.sys.tables where name like '%<GUID #>%'
8

“Epicor10Reports” appears to be a DB object name that you created. Generically speaking, what is it?

9

It is the database name where the Report tables are separate from the Epicor ERP and Ice tables.

image
image.png259Ă—665 16.8 KB

1 Like
10

Maybe this will be of any use:

1 Like
11

That DB in our setup is just named “Reports”

And running that script on the DB (removing the “Epicor10Reports”) yields no records.