Fastest way to reset AX SSRS setting in SSMS

Another useful snippet, just to make the deployment of a TEST to DEV database restore go faster: changing the SSRS settings in AX to match your machine's settings.


DECLARE @ServerName NVARCHAR(10)
DECLARE @FirstSRSRecId BIGINT
DECLARE @ConfigurationId NVARCHAR(20)

SET @ServerName = HOST_NAME() -- Not using @@SERVERNAME because the SQL instance name is different than the hostname
PRINT @ServerName

SELECT TOP 1 @FirstSRSRecId = RECID FROM SRSSERVERS

DELETE FROM SRSSERVERS
    WHERE RECID <> @FirstSRSRecId

UPDATE SRSSERVERS
    SET SERVERID = @ServerName,
       SERVERURL = 'http://' + @ServerName + '/ReportServer',
       REPORTMANAGERURL = 'http://' + @ServerName + '/Reports',
       AOSID = '01@' + @ServerName,
       CONFIGURATIONID = @ServerName + 'Config',
       ISDEFAULTREPORTLIBRARYSERVER = 1,
       SERVERINSTANCE = 'MSSQLSERVER'

SELECT TOP 1 @ConfigurationId = CONFIGURATIONID FROM SRSSERVERS

PRINT ''
PRINT 'Updated ' + @ConfigurationId