SphereGen

SphereGen Logo

Part 2 – Issues and Challenges After Migration of Microsoft SQL

Reviewed for accuracy on January 23, 2023

In Part-1, SSRS migration steps have been discussed. Many congratulations for SSRS migration but migration journey is not yet over. SSRS migration opens door for new issue and challenges due to feature update but also it welcomes new feature that comes with newer version of SSRS.

Below mentioned all problem statements are related to migration of SSRS-2008R2 to SSRS2017 and Oracle12c have been used as report datasource that might not be relevant to other version of SSRS and other report datasources like (SQL Server, MySql etc…)

Let’s go through with these all step by step.

Date/Time datatype in Report Parameter

This one is the most impacted datatype after SSRS migration that has started throwing below

error in many reports. You can observe this error in SSRS log file.

“Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘’. —> Oracle.ManagedDataAccess.Client.OracleException: ORA-01830: date format picture ends before converting entire input string”

Solution

SSRS 2008 used the old System.Data.OracleClient. In SSRS 2016 and later you must install the Oracle ODP.NET provider, built and supported by Oracle. So probably just a difference in how the NLS_DATE_FORMAT session parameter is set by the two drivers.

Let’s discuss above error and solution:

1) If you have used Oracle method: To_Date in your dataset query without date format, then you are going to experience above error in SSRS log file because newer version of SSRS datetype parameter always hold date with default time.

T0_Date(:<Your Query Parameter Name>) : this will throw above error, if your report query parameter is directly bind with the report DateType parameter.

T0_Date(:<Your Query Parameter Name>,<Date Format>) : this will work without any error, if used has been specified correctly as per input value in

There are two options to fix this error.

Method#1 : Use Oracle method TRUNC in your dataset query like T0_Date(TRUNC(:<Your Query Parameter Name>)) that truncates time value from dateTime value

Method#2 : Use expression like =CDATE(<Report Parameter Name>.Value).ToString(“dd-MMM-yyyy”) in your dataset Parameters Property to bind your Query date type parameter with above expression instead of binding datetime report parameter directly.

2) After fixing above error, run your report successfully and then run following query on ReportServer database

select top 1 Parameters,* from ExecutionLog3 Order by TimeStart Desc

You will notice that ExecutionLog3.Parameters field is holding datetype parameter value like p_StartDate=1%2F13%2F2020%2012%3A00%3A00%20AM that is very much like HTML URL Encoding

To make this value in human readable format, browse below URL and use only datatime parameter value 1%2F13%2F2020%2012%3A00%3A00%20AM there and click on decode

https://www.freeformatter.com/url-encoder.html#ad-output

it will show this result 1/13/2020 12:00:00 AM

3) Many of the reports started showing performance issue after migration that had datetype parameter.

Using Oracle method TRUNC in dataset query like T0_Date(TRUNC(:<Your Query Parameter Name>)) that truncates time value from dateTime value has fixed the performance issue for us.

BOOLEAN datatype in Report Parameter

If you are using BOOLEAN datatype report parameter and you have used below kind of expression, you must be very careful as below expressions are always going to return False as output

=IIF(.Value=1,”TRUE”,”FALSE”)

OR

=IIF(CINT(.Value)=1,”TRUE”,”FALSE”)

Solution

Since Boolean datatype report parameter returns -1 for TRUE and 0 for False, you need to modify above expression as below to get the correct result in report output

=IIF(.Value= -1,”TRUE”,”FALSE”)

OR

=IIF(CINT(.Value)= -1,”TRUE”,”FALSE”)

Oracle LONG datatype is not supported

If your dataset query is holding an oracle Long datatype filed, report is not going to populate any value for this field, nor it will throw any error.

Solution

Either you need to change LONG datatype to CLOB in your oracle table definition or you need to add a new field of CLOB datatype and populate values of LONG datatype to CLOB datatype and further create a trigger on this table that will sync newly added CLOB datatype filed with existing LONG datatype field value. At the end use this CLOB field in your report’s dataset query that will populate values as expected in report output.

Table/Matrix header text display disturbed in IE Browser and excel export

Few report’s Table/Matrix header text started showing inappropriate way in internet browser and Excel export.

Solution

Right click on Table/Matrix header cell -> choose Textbox Properties – >Sizing options

Uncheck “Allow height to increase” option and checked “Allow height to decrease” option.

You need to perform this action on every header row cell of Table/Matrix header.

Bind variable does not exists error

Some reports started failing with below error.

“An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for dataset ‘owner_ds’. (rsErrorExecutingCommand)

ORA-01006: bind variable does not exist”

SQL screenshot

Solution

If there is any commented text (line next to where clause) in your dataset query having name of report parameter, you will experience this error.

Select

from table1 o, table2 r

where r.resource_id = o.resource_id

– – and o.effective_date <= nvl(:p_asofdate, trunc(sysdate))

To resolve this error, you need to remove the commented line from your dataset query and then above query will look like below

Select

from table1 o, table2 r

where r.resource_id = o.resource_id

Navigation and export icons are not showing correctly in Report Manager

Few users have reported that appropriate icons are not appearing in report browser and they are getting it as in yellow highlighted in below screenshot

SQL screenshot

Solution

Solution is simple. Just clear the browser cache and this issue will get fixed. Cheers 😊

EXCEL Subscriptions started failing

All the existing EXCEL subscriptions started failing and logged below error message in SSRS log file.

“ERROR: Error occurred processing subscription e7efaa03-6bfb-4b55-83bb-55c524458c8b: The value ‘EXCEL’ is not valid for setting ‘Render Format’”

Solution

In newer version of SSRS, Excel export is producing “.XLSX” file extension instead of traditional “.XLS” and behind the scene “EXECL” export option is not available directly (why so will see later here) and “EXCELOPENXML” option have been introduced that is causing above error.

If you open rsreportserver.config, you will notice below extensions for EXCEL inside tag.

<Extension Name=”EXCELOPENXML” Type=”Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer, Microsoft.ReportingServices.ExcelRendering”/>

<Extension Name=”EXCEL” Type=”Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer, Microsoft.ReportingServices.ExcelRendering” Visible=”false”/>

EXCELOPENXML” is new render format for EXCEL export hence if you export/Subscribe any report for EXCEL export, you will notice EXCELOPENXML format used in ReportServer.dbo.executionlog3 under Format column in below query output.

select top 1 [Format],* from ExecutionLog3 Order by TimeStart Desc

In above rsreportserver.config section, “EXCEL” is still there as an render option but this option is not visible (Visible=”false” in extension tag) for use and hence can’t be used directly but If there is embedded Report URL used in any of your application that forms URL like below it is still going to work and report output will get exported to traditional “.XLS” format.

https://<Report Server URL>/ReportServer?/myreport&rs:Format=EXCEL

for more see: https://docs.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-ver15

Now in order to make existing EXCEL Subscription working, we need to modify EXCEL rendering to use EXCELOPENXML as render format.

Use below query on ReportServer database that will update existing standard EXCEL Subscriptions to use EXCELOPENXML as render format.

Use ReportServer

GO

Declare @OldRenderFormat varchar(100)=’EXCEL’,@NewRenderFormat varchar(100)=’EXCELOPENXML’;

Declare @SubscriptionID nvarchar(1000),@ExtensionSettings Nvarchar(MAX),@XMLDATA XML;

declare

cur_FIXED_Subscriptions CURSOR for Select SubscriptionID,[ExtensionSettings]

from dbo.Subscriptions

where

[ExtensionSettings] Like ‘%<ParameterValue><Name>RenderFormat</Name><Value>’+@OldRenderFormat+'</Value></ParameterValue>%’;

 

Open cur_FIXED_Subscriptions

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings

While @@FETCH_STATUS=0

Begin

Set @XMLDATA=CAST(@ExtensionSettings AS XML)

—Replace OLD Render format to New Render Format

SET @XMLDATA.modify(‘replace value of (/ParameterValues/ParameterValue/Value[../Name=”RenderFormat”]/text())[1] with (sql:variable(“@NewRenderFormat”))’)

—Update OLD Render format to NEW Render format

Update Subscriptions

Set [ExtensionSettings]=cast(@XMLDATA as nvarchar(max))

where

Convert(nvarchar(1000),SubscriptionID)=@SubscriptionID

 

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings

End

 

Close cur_FIXED_Subscriptions

DeAllocate cur_FIXED_Subscriptions

Use below query on ReportServer database that will update existing data-driven EXCEL Subscriptions to use EXCELOPENXML as render format

—///Script to change report render format from ‘EXCEL’ to ‘EXCELOPENXML’ in Subscription Command Text

Use ReportServer

GO

Declare @OldRenderFormat varchar(100)=’EXCEL’,@NewRenderFormat varchar(100)=’EXCELOPENXML’

Declare @SubscriptionID nvarchar(1000),@DataSettings Nvarchar(MAX),@XMLDATA XML,@NewCommandTextQuery Varchar(MAX);

—-

declare

cur_FIXED_Subscriptions CURSOR for

Select SubscriptionID,DataSettings from Subscriptions

where Cast(DataSettings As XML).value(‘(/DataSet/Query/CommandText/text())[1]’,’Varchar(MAX)’) like ‘%”’+@OldRenderFormat+”’%’;

 

Open cur_FIXED_Subscriptions

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@DataSettings

While @@FETCH_STATUS=0

Begin

Set @XMLDATA=CAST(@DataSettings AS XML)

–Get the Subscription Command text Query and replace ‘EXCEL’ with ‘EXCELOPENXML’

Select @NewCommandTextQuery=Replace(@XMLDATA.value(‘(/DataSet/Query/CommandText/text())[1]’,’Varchar(MAX)’), ””+@OldRenderFormat+””,””+@NewRenderFormat+””)

 

SET @XMLDATA.modify(‘replace value of (/DataSet/Query/CommandText/text())[1] with (sql:variable(“@NewCommandTextQuery”))’)

—Update New DataSettings

Update Subscriptions

Set [DataSettings]=cast(@XMLDATA as nvarchar(max))

where

Convert(nvarchar(1000),SubscriptionID)=@SubscriptionID

 

Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@DataSettings

End

Close cur_FIXED_Subscriptions

DeAllocate cur_FIXED_Subscriptions

HTML4.0 Rendering is not supported

Since HTML4.0 rendering option is not supported, you need to update existing subscriptions with appropriate available rendering option like MHTML and you can use above “EXECL” to “EXCELOPENXML” queries with minimum modification for this as well.

New Feature – Common SYS account for all file share subscriptions

In newer version of SSRS, Report Server Admin can setup a centralized file share account that can be consumed by all file share subscriptions.

Visit this link to update existing file share subscriptions to use centralized file share account
https://www.spheregen.com/update-existing-windows-file-share-subscriptions-to-use-common-file-share-account/

For more details about this feature, please visit to https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/subscription-settings-and-a-file-share-account-configuration-manager?view=sql-server-ver15

This is a new feature whereas old way to setup file share credential to access the file in every file share subscription is still valid can be used in old way.

New Feature – Report Comments with/without attachment

Report comments as a new feature have been introduced where user can add comment with/without attachment in every report. Run any report and you will see this option in top right side (see this screenshot). If you don’t have appropriate access permission, you will not able to see this option.
SQL screenshot

If you don’t like this option and want to hide this option from Report, use below steps that will hide this feature.

  • Launch Sql Server Management Studio and Connect to “Reporting Services” using required authentication. See below screenshot
SQL screenshot
  • Expand Security – > Roles folder and double click on “Browse” role. Uncheck both the options (Comment on Reports and Manage Comments) related to comment Task. See below screenshot
  • Do the same for other roles (Content Manager, My Reports, Publisher and Report Builder)

After this, re-start service of reporting services

SQL screenshot

You are all done with migration. Cheers 😊

About SphereGen

SphereGen logo on white background

SphereGen is a unique solutions provider that specializes in cloud-based applications, Intelligent Automation, and Extended Reality (AR/VR/MR). We offer full-stack custom application development to help customers employ innovative technology to solve business problems.

Learn more about what we do in Application Development: https://www.spheregen.com/application-development

microsoft partner badge
uipath silver partner badge