1

Resolved

On SQL Server 2008, enumeration fails on reportserver

description

During the running of the extractor in a SQL Server 2008, enumeration fails on the third report on the server. I opened this report, ran the report, and looked for anything unusual. The report seems fine to me. I would like to get a full enumeration of the report server and get this issue fixed.

Do you need any additional information?

Thanks,

John

comments

rav4john wrote Jul 31, 2015 at 5:18 PM

I should add that this is SQL Server 2008 R2.

kmartin wrote Aug 2, 2015 at 11:04 PM

Can you please upload the output from the command line tool?
I'm chasing the error message(s) that are returned when the enumeration fails.
And if possible the SQL Statement.

Send a private message if you can't post that information publicly.

From that I should be able to locate the underlying problem.

It may be that the report is using an ODBC statement, or non T-SQL query.

rav4john wrote Aug 4, 2015 at 1:19 PM

Yes, following is the command line output.

Enumerating Analysis Services metadata.
Enumerating 'Freight Cube'...Done
Enumerating 'Freight Cube Copy'...Done
Enumerating Reporting Services metadata.
Analysing Report BillingReport_Edge
Analysing Report Core_ShipmentSummary
Analysing Report Edge Orders Shipped_Detail
Error occurred: Object reference not set to an instance of an object.
Committing analysis information to database...Completed.

Following is the SQL ( I think the problem may be here as several of the datasets uses a parameter to set the database name. This would not be available at design time.)

1st Dataset SQL: Select DBName, ClientName,DestConn from BI_Connections Where Status_Client = 'active' and Status_ETL = 'active' and DestConn is not NuLL
order by ClientName

2nd Dataset SQL: I think this is the problem, The program uses an expression for the connection string. It is derived from what database the user selects. ="Data Source=JP02MISSQL01A;Initial Catalog=" & Parameters!ClientName.Value

3rd Dataset SQL: select
distinct ProviderName from dimSite where ClientName = (case @Client when 'Aerin' then 'Aerin Lauder'
else @Client end)
and ProviderName is not null

4th Dataset SQL: select ClientName from bi_connections where DBName = @ClientName

5th Dataset SQL: Uses the same datasource as 2nd Dataset SQL.

6th Dataset SQL: Uses the same datasource as the 2rd Dataset SQL.

7th Dataset SQL: Uses the same datasource as the 2nd Dataset SQL

kmartin wrote Sep 30, 2015 at 10:19 AM

Confirmed that this is an issue. It is caused by a DataSourceDefinition that uses an expression.
I've replicated the issue, and I'm working on a fix.

wrote Sep 30, 2015 at 11:07 AM

Resolved with changeset 111743: Add capability to handle parameterised connection strings, and handle the resulting null from the connection string parser.

kmartin wrote Sep 30, 2015 at 11:08 AM

** Closed by kmartin 30/09/2015 04:07

wrote Sep 30, 2015 at 11:08 AM

wrote Sep 30, 2015 at 11:08 AM