You’re offline. This is a read only version of the page.
75,730
If you move your GP databases to a new server or create a test company based on an existing company, you will find many of the SSRS reports or Business Analyzer return no data. This is because the SQL views behind these reports have the sql server instance and database name embedded in them. You can use this script to create another script that will correct this. You will need to go into SSMS and run this script and then File->Save Results As. When you pull up this script you then need to change all occurrences of CREATE VIEW to ALTER VIEW.
SELECT REPLACE(OBJECT_DEFINITION(object_id), 'Old_Server_Name', 'New_Server_Name') + CHAR(13) + CHAR(10) + 'GO'
FROM
sys.views
WHERE
CHARINDEX('Old_Server_Name', OBJECT_DEFINITION(object_id)) > 0
*This post is locked for comments
I have moved my gp databases (2010) to new 2012 sql server while leaving the deployment on the current TS. I need to repoint my ssrs reports to the new server and once I do that that and I have ascertained all the modified reports work, I then want to move my gp 2010 install to the new 2012 server I have set up. I can't tell from your post if your recommendations would work in this scenario? For example, are you running the scripts on the new sql server, have you move the ssrs database to the new server. Thanks for your help.
John
That is true Ian. Since doing an Edit and Replace in SSMS Query window is so simple I did even think about it. I was just amazed to see SQL instance and Server Names embedded in these views.
Hi Richard,
You could wrap your current replace with another one to change the CREATE VIEW with ALTER VIEW:
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (