The SQL Reporting Services URL is not valid

How many times have you been caught with this error message  after GP Utilities has completed the DYNAMICS system DB & company DB upgrade ? 

Every time I perform a TEST environment upgrade with any GP version, I get caught by the same trap and just have to slap myself for not remembering to check some tables prior to running the GP Utilities.

According to this old Microsoft blog post from the GP dev team, this is a known issue for many years and I don’t know why it hasn’t been fixed yet… 

https://blogs.msdn.microsoft.com/dynamicsgp/2012/02/09/microsoft-dynamics-gp-ssrs-reports-and-deployment/


When launching Microsoft Dynamics GP 2010 Utilities R2/SP2 or later, which includes the updated deployment model for the SSRS reports, the application will compare the contents of the sySrsReports/syExcelReports and syDeployedReports tables which are located in the DYNAMICS database. If there are objects in the sySrsReports that do not exist in the syDeployedReports table or if the build number has been incremented with a new version of the report it will automatically deploy those missing/updated items.  It looks to the SY40800 table (Reporting Tools Setup) which is in the DYNAMICS database as well to see if the site information has been populated.  This gets populated when the appropriate url’s or paths are entered within the Reporting Tools Setup Window within Microsoft Dynamics GP.

Whenever you try to run GP Utilities on an existing GP instance where built-in SSRS and/or Excel reports have been deployed previously, the program would just go on with the re-deployment of the new SSRS & Excel collection without asking any permission if it detects that they were previously deployed… sound great ? not really.. 

Protect your existing reports!

First of all, this might be an acceptable behaviour if you upgrade your LIVE environment and just assume that all your existing reports will get a new version and scrapped all over.. Even then, I’d be very concerned about my customized reports that you may have changed over the course of time in your previous GP version.. not fun to have to redo them all! So be aware of that and make backups of your .rdl files in SSRS and Excel modified reports in the shared network folder (assuming they were deployed for everyone).

Now to the technical side.. I’ve been explaining the details in many Dynamics Community forum threads, but this ones summarizes it all (not quite the upgrade failure, but similar root cause):

https://community.dynamics.com/gp/f/32/t/174359

In order to prevent GP Utilities to automatically trigger the SSRS & Excel reports re-deployment, you have to clear out (or adjust the URL) the following tables in the DYNAMICS DB : 
SY40800
syDeployedReports

You can use the following SQL code to perform this. To prevent your GP Utilities to stall after the company upgrade, clearing out SY40800 is enough. You may later want to clear out also syDeployedReports as it is just going to be clogged with un-necessary entries when it comes to re-deploy your reports to a new location on your TEST bed server. 

USE DYNAMICS
-- check deployment URL
SELECT * FROM SY40800
-- take a backup
select * into SY40800_bub
from SY40800
-- clear out report setup
delete from SY40800
-- check for which company reports where deployed
select * from syDeployedReports

As said, if your TEST server has the same setup as your LIVE server, you could just alter & update the URL in the SY40800 & syDeployedReports tables to point to the new location and avoid the error completely from the GP Utilites.. just think about that before starting the process 🙂

Please share if you find this useful. I hope you can refer to it some time in a future upgrade.
Until next, have a great time!
@GP_Beat

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#

smay772

A fine WordPress.com site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

David Musgrave's Winthrop Development Consultants Blog

This blog will keep you up to date with Winthrop Development Consultants and David’s experiences, tips and tricks as a developer and consultant in the Microsoft Dynamics GP community.

Gilbert Quevauvilliers - BI blog

My learnings and findings in the world of Business Intelligence

Build Numbers

Build numbers and version history for various products

Rambles of a non-IT person

Business development work to make lives easier

%d bloggers like this: