As development was in progress, the partner was working on the clients standalone test system, but when they tested the drill downs, the data was coming from the live system. I did some exploring of the views and found that the drilldowns are coded during deployment to include the server and database:
'Account Index For Drillback' = 'dgpp://DGPB/?Db=GP&Srv=2018SQL1\GP&Cmp=TEST&Prod=0' +dbo.dgppAccountIndex(1,['Account Master'].[ACTINDX] )
This issue will affect the views not only when the databases are copied to a new system, but also when a live company is copied to a test company on the same server.
The solution was to create a script which would alter the views to the new server or database:
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ DECLARE @ViewDefinition AS NVARCHAR(max) DECLARE @OldDatabase AS VARCHAR(5) = 'TWO' DECLARE @NewDatabase AS VARCHAR(5) = DB_NAME() DECLARE @OldServer AS VARCHAR(50) = '2018SQL1' DECLARE @NewServer AS VARCHAR(50) = @@ServerName CREATE TABLE #ViewDefinitions( ViewDefinition NVARCHAR(MAX) ,ROW_ID INT IDENTITY ) INSERT INTO #ViewDefinitions (ViewDefinition) --VALUES (SELECT REPLACE( REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW') ,'Srv=' + @OldServer + '&Cmp=' + @OldDatabase,'Srv=' + @NewServer + '&Cmp=' + @NewDatabase) FROM sys.all_views AS ['All Views'] JOIN sys.sql_modules AS ['SQL Modules'] ON ['SQL Modules'].object_id = ['All Views'].object_id and ['SQL Modules'].definition LIKE '%Srv=' + @OldServer + '&Cmp=' + @OldDatabase + '%') DECLARE cursor_Views Cursor FOR SELECT ViewDefinition FROM #ViewDefinitions Open cursor_Views FETCH NEXT FROM cursor_Views INTO @ViewDefinition WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) EXEC (@ViewDefinition) FETCH NEXT FROM cursor_Views INTO @ViewDefinition END CLOSE cursor_Views DEALLOCATE cursor_Views DROP TABLE #ViewDefinitions GO
The script uses the @@ServerName and DB_Name functions for the new server and database names; the two highlighted sections are the parameters for the old server and database which need to be amended.
As this script updates the views, make sure you have a good database backup before running.
Updated 24/10/2018 following feedback from David Morinello