I wrote the first version of this script a long time ago, but recently had cause to return to it. A client I am working with has configured the posting reports in one company and wanted to copy the same configuration to the other companies.
This script allows you to do this, on a one by one basis. Set the first highlighted parameter to the source company, and the second to the destination company.
/*
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 @SQLStatement VARCHAR(8000)
DECLARE @SourceCompany VARCHAR(5) = 'T16R2'
DECLARE @DestinationCompany VARCHAR(5) = 'P16R2'
SET @SQLStatement = 'UPDATE
[''''Destination Posting Journal Destinations'''']
SET
PRNTJRNL = [''''Source Posting Journal Destinations''''].PRNTJRNL
,ASECTMNT = [''''Source Posting Journal Destinations''''].ASECTMNT
,PRTOPRNT = [''''Source Posting Journal Destinations''''].PRTOPRNT
,PRTOFLNT = [''''Source Posting Journal Destinations''''].PRTOFLNT
FROM
' + @DestinationCompany + '.dbo.SY02200 AS [''''Destination Posting Journal Destinations'''']
INNER JOIN
' + @SourceCompany + '.dbo.SY02200 AS [''''Source Posting Journal Destinations''''] ON [''''Source Posting Journal Destinations''''].SERIES = [''''Destination Posting Journal Destinations''''].SERIES
AND [''''Source Posting Journal Destinations''''].TRXSOURC = [''''Destination Posting Journal Destinations''''].TRXSOURC
AND [''''Source Posting Journal Destinations''''].PTGRPTNM = [''''Destination Posting Journal Destinations''''].PTGRPTNM'
EXEC (@SQLStatement)
As always with an SQL script, please make sure you have a good backup before running it.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.
Hi Ian,
Somehow I’m wondering if you ever tested the script above ?
Because the way it is written, it cannot work as it’s missing a proper table reference after the UPDATE statement.. SQL complains about an invalid ‘Destination’ name..
In order for the script to work, you need to take out the single quotes that are wrapping each Alias name for the source and destination database: [Destination Posting Journal Destinations] & [Source Posting Journal Destinations].
This way it works, as otherwise SQL is trying to interpret them with quotes as values.
I’ve used the script previously, but, think that when I came to post it, I made the alias more verbose to improve readability and broke it then. I do usually preview scripts through the site and test them again before posting, but it looks I didn’t in this case.