SQL script to insert test email address to all customers

Microsoft Dynamics GPBack when this blog was a few months old, I posted a script on updating emails on vendors to keep emails internal for testing and have recently had the need to provide a customer a similar script for testing the emailing of invoices and statements.

This first script updates all existing records to replace the email address with one supplied by the customer (the highlighted email address should be changed to the required email address):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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 @TestEmail VARCHAR
(100) SET @TestEmail = 'email@example.com' UPDATE SY01200 SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'CUS'

this second script will insert an email address against all customer addresses which don’t currently have an email (the highlighted email address should be changed to the required email address).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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 @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' INSERT INTO SY01200 ( Master_Type ,Master_ID ,ADRSCODE ,INETINFO ,EmailToAddress ,EmailCcAddress ,EmailBccAddress ) --VALUES ( SELECT 'CUS' ,CUSTNMBR ,ADRSCODE ,'' ,@TestEmail ,'' ,'' FROM RM00102 AS ['Customer Master Address File'] WHERE ( SELECT COUNT(Master_ID) FROM SY01200 AS ['Internet Addresses'] WHERE Internet Addresses.Master_Type = 'CUS' AND Internet Addresses.Master_ID = ['Customer Master Address File'].CUSTNMBR AND Internet Addresses.ADRSCODE = ['Customer Master Address File'].ADRSCODE ) = 0 )

These scripts are only intended for use on a development or test system so please be careful when running them; as always with update scripts, I’d recommend having a good backup before running them just in case of problems.