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