web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

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,850 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

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,850 Moderator on at

    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%'

  • Derek Albaugh Profile Picture
    Derek Albaugh on at

    Thanks Richard

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,850 Moderator on at

    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

  • Community Member Profile Picture
    Community Member on at

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

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

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans