Author: Ian Grieve

I was doing some work with a client recently with a custom extension being tested in some Microsoft Dynamics GP companies. Due to how it is deployed (very manually) we had only deployed it to some databases and not all.

Due to other project commitments, no testing was done for a while and when we returned, we weren’t sure if the deployment notes listed all databases correctly or if some of those databases had been overwritten for other testing.

So, a small script was needed to check for the presence of a custom table in all databases; the below is what I came up with to check for a table in all Microsoft Dynamics GP databases:

/*
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 @command nvarchar(max)

DECLARE @SystemDatabase VARCHAR(15) = 'DYNAMICS'
DECLARE @Table VARCHAR(50) = 'SY00800'
CREATE TABLE #ReturnedData(
	DBNAME VARCHAR(15)
	,TABLENAME VARCHAR(100)
)

SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL))
						BEGIN
							USE [?];
							INSERT INTO #ReturnedData (dbname, tablename) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.name LIKE ''' + @Table + '%'')
						END'

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

While writing a Rockton Software’s SmartFill product recently, I wanted to do a test on a large dataset. The testing of SmartFill I had done to that point had been on vendors, so I deviced to continue in that vein.

I produced the script below which inserts records into the PM Vendor Master File (PM00200) and PM Address MSTR (PM00300) tables bgy copying an existing vendor, but assigning a unique Vendor ID to each new record. For ease of running, I creaed the script as a stored procedure wic accepts the number of records to create, the vendor to be copied and the new name of the vendor which will be created concatenated with an increasing integer.

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_CreateTestVendors', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_CreateTestVendors
GO

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_CreateTestVendors
	@MaxLoop INT = 5
	,@VendorID VARCHAR(15) = 'BEAUMONT0001'
	,@VendorIDPrefix VARCHAR(8) = 'VEND'
AS
	/*
	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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
	*/
	DECLARE @Loop INT = 1

	WHILE (@Loop <= @MaxLoop)
		BEGIN
			-- Insert into PM Vendor Master File (PM00200)
			INSERT INTO PM00200
				(
					VENDORID,VENDNAME,VNDCHKNM,VENDSHNM,VADDCDPR,VADCDPAD,VADCDSFR,VADCDTRO,VNDCLSID,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,UPSZONE,SHIPMTHD,TAXSCHID,ACNMVNDR,TXIDNMBR,VENDSTTS,CURNCYID,TXRGNNUM,PARVENID,TRDDISCT,TEN99TYPE,TEN99BOXNUMBER,MINORDER,PYMTRMID,MINPYTYP,MINPYPCT,MINPYDLR,MXIAFVND,MAXINDLR,COMMENT1,COMMENT2,USERDEF1,USERDEF2,CRLMTDLR,PYMNTPRI,KPCALHST,KGLDSTHS,KPERHIST,KPTRXHST,HOLD,PTCSHACF,CREDTLMT,WRITEOFF,MXWOFAMT,SBPPSDED,PPSTAXRT,DXVARNUM,CRTCOMDT,CRTEXPDT,RTOBUTKN,XPDTOBLG,PRSPAYEE,PMAPINDX,PMCSHIDX,PMDAVIDX,PMDTKIDX,PMFINIDX,PMMSCHIX,PMFRTIDX,PMTAXIDX,PMWRTIDX,PMPRCHIX,PMRTNGIX,PMTDSCIX,ACPURIDX,PURPVIDX,NOTEINDX,CHEKBKID,MODIFDT,CREATDDT,RATETPID,Revalue_Vendor,Post_Results_To,FREEONBOARD,GOVCRPID,GOVINDID,DISGRPER,DUEGRPER,DOCFMTID,TaxInvRecvd,USERLANG,WithholdingType,WithholdingFormType,WithholdingEntityType,TaxFileNumMode,BRTHDATE,LaborPmtType,CCode,DECLID,CBVAT,Workflow_Approval_Status,Workflow_Priority,Workflow_Status,VADCD1099,ONEPAYPERVENDINV
				)
			-- VALUES
				(SELECT
					
					@VendorIDPrefix + CAST(@Loop AS VARCHAR(7))
					,VENDNAME,VNDCHKNM,VENDSHNM,VADDCDPR,VADCDPAD,VADCDSFR,VADCDTRO,VNDCLSID,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,UPSZONE,SHIPMTHD,TAXSCHID,ACNMVNDR,TXIDNMBR,VENDSTTS,CURNCYID,TXRGNNUM,PARVENID,TRDDISCT,TEN99TYPE,TEN99BOXNUMBER,MINORDER,PYMTRMID,MINPYTYP,MINPYPCT,MINPYDLR,MXIAFVND,MAXINDLR,COMMENT1,COMMENT2,USERDEF1,USERDEF2,CRLMTDLR,PYMNTPRI,KPCALHST,KGLDSTHS,KPERHIST,KPTRXHST,HOLD,PTCSHACF,CREDTLMT,WRITEOFF,MXWOFAMT,SBPPSDED,PPSTAXRT,DXVARNUM,CRTCOMDT,CRTEXPDT,RTOBUTKN,XPDTOBLG,PRSPAYEE,PMAPINDX,PMCSHIDX,PMDAVIDX,PMDTKIDX,PMFINIDX,PMMSCHIX,PMFRTIDX,PMTAXIDX,PMWRTIDX,PMPRCHIX,PMRTNGIX,PMTDSCIX,ACPURIDX,PURPVIDX,NOTEINDX,CHEKBKID,MODIFDT,CREATDDT,RATETPID,Revalue_Vendor,Post_Results_To,FREEONBOARD,GOVCRPID,GOVINDID,DISGRPER,DUEGRPER,DOCFMTID,TaxInvRecvd,USERLANG,WithholdingType,WithholdingFormType,WithholdingEntityType,TaxFileNumMode,BRTHDATE,LaborPmtType,CCode,DECLID,CBVAT,Workflow_Approval_Status,Workflow_Priority,Workflow_Status,VADCD1099,ONEPAYPERVENDINV
				FROM
					PM00200
				WHERE
					VENDORID = @VendorID)

			-- Insert into PM Address MSTR (PM00300)
			INSERT INTO PM00300
				(
					VENDORID,ADRSCODE,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,UPSZONE,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,SHIPMTHD,TAXSCHID,EmailPOs,POEmailRecipient,EmailPOFormat,FaxPOs,POFaxNumber,FaxPOFormat,CCode,DECLID
				)
			-- VALUES
				(SELECT
					
					@VendorIDPrefix + CAST(@Loop AS VARCHAR(7))
					,ADRSCODE,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,UPSZONE,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,SHIPMTHD,TAXSCHID,EmailPOs,POEmailRecipient,EmailPOFormat,FaxPOs,POFaxNumber,FaxPOFormat,CCode,DECLID
				FROM
					PM00300
				WHERE
					VENDORID = @VendorID)
			SET @Loop = @Loop + 1
		END
	GO
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_CreateTestVendors TO DYNGRP
GO

-- execute stored proc
EXEC usp_AZRCRV_CreateTestVendors 20, 'ADVANCED0001', 'VENDOR'
GO

This script was created for, tested with and only run against the Fabrikam sample database.