Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Need to add step to se...
Microsoft Dynamics GP (Archived)

Need to add step to server move KB878449

(0) ShareShare
ReportReport
Posted on by Richard Wheeler 75,848 Moderator

I just came across something new when it comes to performing a server move for GP. I discovered that the drill back feature did not work on the server to which the database was copied. The cause was because the server name of which the SSRS reports were originally deployed was embedded in some of the column names of the SQL views. So I had to find which views, in this case Account _Summary for one, and manually update the view with the new server name. Does any one know of a complete list of views where this occurs?

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    RE: Need to add step to server move KB878449

    Which database are you talking about--the report server database or the master?

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: Need to add step to server move KB878449

    Here is an even better version. This creates a series of create view scripts and all you have to do is change all occurences of CREATE to ALTER

    SELECT  REPLACE(OBJECT_DEFINITION(object_id), 'VMTMGP2013', 'GPSQL') + CHAR(13) + CHAR(10) + 'GO'

    FROM    sys.views

    WHERE   CHARINDEX('VMTMGP2013', OBJECT_DEFINITION(object_id)) > 0

    or if you prefer, this version does it all.

    SELECT  REPLACE(REPLACE(OBJECT_DEFINITION(object_id), 'Old_Server_Name', 'New_Server_Name'), 'CREATE VIEW', 'Alter view') + CHAR(13) + CHAR(10) + 'GO'

    FROM

    sys.views

    WHERE

    CHARINDEX('Old_Server_Name', OBJECT_DEFINITION(object_id)) > 0

  • Derek Albaugh Profile Picture
    Derek Albaugh on at
    RE: Need to add step to server move KB878449

    Thanks Richard

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: Need to add step to server move KB878449

    Someone pointed me to this script that will find a text string in your SQL objects in your database. I will need to turn this into an update statement and add this to the steps in KB878449 when copying databases between servers. If you do not do this the drill back feeature in your SSRS reports and Business Analyzer will not work. This is required when the server names are different.

    SELECT obj.[name],cmt.[text]

    FROM [dbo].[sysobjects] obj

    INNER JOIN [dbo].[syscomments] cmt

    ON obj.[id] = cmt.[id]

    where cmt.[text] like '%whatever%'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Almas Mahfooz Profile Picture

Almas Mahfooz 3 User Group Leader

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans