Find SQL Function In All Databases

Microsoft SQL ServerI did a few posts back in 2016 on finding objects in SQL (a column in all tables, all custom sql objects in a database and trigger in all databases).

I needed to find if a function existed in all databases, so I produced the following script which searches in all Microsoft Dynamics GP databases:

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

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_FindFunctionInAllDatabases
	@Function VARCHAR(50) = 'uf_AZRCRV'
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 4.0 International (CC BY-NC-SA 4.0 Int).
	*/
	DECLARE @command nvarchar(max)

	SELECT @command = 'IF EXISTS ( SELECT 1 FROM SY01500 AS [''Company Master''] INNER JOIN sys.databases [''Sys Databases''] ON [''Company Master''].INTERID = [''Sys Databases''].name WHERE name = ''?'')   
						BEGIN
							USE [?];
							DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
							SELECT DB_NAME() AS ''DB_NAME'',t.name FROM sys.objects AS t
							WHERE t.type_desc LIKE ''%FUNCTION%'' 
							AND t.name LIKE '''+ @Function + '%''
						END'

	EXEC sp_MSforeachdb @command
GO

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

-- execute stored proc
EXEC usp_AZRCRV_FindFunctionInAllDatabases 'dgpp'
GO

SQL Script to Insert Vendors

Microsoft Dynamics GPWhile 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.

SQL Script To Activate Horizontal Scroll Arrows

Microsoft Dynamics GPThe Account field in Microsoft Dynamics GP is of a fixed width, but the maximum width of the account string themselves can be much longer than the field.

The solution Microsoft have supplied is an option which activates horizontal scroll arrows in the Account field:

Acount Maintenance

This is activated, on a per user basis, via the User Preferences window (Microsoft Dynamics GP menu >> User Preferences) by marking the Horizontal Scroll Arrows:

User Preferences

However, if you have a lot of users, this can sometimes be a difficult message to disseminate.

An alternative I came up with for a client a wile ago was to create a trigger on the Users Master (SY01400) table which updates the field after a new user is created:

/*
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).
*/
CREATE TRIGGER
	utr_AZRCRV_UpdateSY01400ActivateHorizontalScrollArrows
ON
	SY01400
AFTER INSERT AS
	UPDATE
		['Users Master']
	SET
		HSCRLARW = 1
	FROM
		SY01400 AS ['Users Master']
	INNER JOIN
		inserted AS INS
			ON
				INS.USERID = ['Users Master'].USERID
GO

If users have already been created, then the following script can be used to activate the horizontal scroll arrows for them:

/*
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).
*/
UPDATE
	SY01400
SET
	HSCRLARW = 1
WHERE
	HSCRLARW = 0
GO

We actually decided to remove the trigger and apply the update script via a SQL Server Agent scheduled job which runs on a period basis (if I recall correctly, it was configured to run each evening at 2100 (avoiding backup jobs).

Find SQL Trigger In All Databases

Microsoft SQL ServerIn the last post, I posted a SQL script which could be used to find a column in the database. This post has a SQL script which can be used to locate triggers in all databases.

Change the highlighted section to the name of the trigger you’re looking for and run the script.

This is not something I am going to do manually, so I wrote a script which would find all tables containing the specified column (change the highlighted section):

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @command nvarchar(max)
DECLARE @Trigger VARCHAR(50) = 'utr_'

SELECT @command = 'IF EXISTS ( SELECT 1 FROM sys.databases WHERE name = ''?'')   
					BEGIN
						USE [?];
						DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
						SELECT DB_NAME() AS ''DB_NAME'',t.name,t.is_disabled FROM sys.triggers AS t 
						INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE ''' + @Trigger + '%''
					END'

EXEC sp_MSforeachdb @command

List View Action Pane Error During Upgrade

Microsoft Dynamics GPWe have been doing a number of upgrades for clients recently, the majority of which went fine. However, we did have one, being done by one of my team, where there was an error produced on the table List View Action Pane when upgrading to Microsoft Dynamics GP 2015 R2:

Company Detail

Continue reading “List View Action Pane Error During Upgrade”

SQL Trigger To Delete EFT Bank Information When The Vendor Is Deleted

Microsoft Dynamics GPA while ago I came across a bug in the Creditor (or Vendor for my American readers) Maintenance window where when a creditor is deleted the EFT information is not deleted; this has caused problems for a number of clients and I finally decided I needed to do something about it.

The result is a SQL trigger on the Creditor Master (PM00200) table; when a creditor is deleted the trigger runs and deletes all records in the Address Electronic Transfer Funds Master (SY06000) table are then deleted:

CREATE TRIGGER dbo.utr_AZRCRV_DeleteSY06000 ON dbo.PM00200 AFTER Delete AS
	/*
	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).
	*/
	DELETE
		['Address Electronic Transfer Funds Master']
	FROM
		SY06000 AS ['Address Electronic Transfer Funds Master']
	INNER JOIN
		deleted
			ON
				deleted.VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID
GO

This the avoids the possibility of a new creditor record being linked to a different creditors bank details. As always with a script (especially one which deletes information, make sure it is tested and you’re happy with how it works before releasing live.

SQL Trigger To Automatically Enable Email Documents In Purchasing – All

Microsoft Dynamics GPThis post includes a script which amalgamates the previous two posted triggers to enable email documents (purchase orders and check remittances) into one.

The below script creates a trigger on the SY04905 table to automatically flag the remittance and purchase orders to be emailed out in PDF format and using Message IDs of REMITTANCE and PURCHASEORDER respectively.

/*
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).
*/
CREATE TRIGGER utr_AZRCRV_Update_SY04905_Activate_All ON dbo.SY04905 AFTER Insert AS
	-- Enable Purchase Order
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'PURCHASEORDER'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 12
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 1
	-- Enable Remittance
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'REMITTANCE'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 19
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 6
GO

SQL Trigger To Automatically Enable Email Documents In Purchasing – Purchase Orders

Microsoft Dynamics GPWhen creating a new vendor there is a fair bit of information which needs to be entered and it is sometimes nice to reduce the amount which needs to be done manually; in the last post I had a script to enable the email document for Check Remittances.

The below script creates a trigger on the SY04905 table to automatically flag the purchase orders to be emailed out in PDF format and using a Message ID of PURCHASEORDER respectively.

/*
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).
*/
CREATE TRIGGER utr_AZRCRV_Update_SY04905_Activate_PO ON dbo.SY04905 AFTER Insert AS
	-- Enable Purchase Order
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'PURCHASEORDER'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 12
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 1
GO

SQL Trigger To Automatically Enable Email Documents In Purchasing – Remittance

Microsoft Dynamics GPWhen creating a new vendor there is a fair bit of information which needs to be entered and it is sometimes nice to reduce the amount which needs to be done manually.

The below script creates a trigger on the SY04905 table to automatically flag the remittance to be emailed out in PDF format and using a Message ID of REMITTANCE.

/*
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).
*/
CREATE TRIGGER utr_AZRCRV_Update_SY04905_Activate_Remittance ON SY04905  AFTER Insert AS
	-- Enable Remittance
	UPDATE
		Email
	SET
		EmailDocumentEnabled = 1
		,EmailMessageID = 'REMITTANCE'
		,EmailDocumentFormat = 3
	FROM
		SY04905 AS Email
	INNER JOIN
		inserted
			ON Email.EmailDictionaryID = 0
				AND Email.EmailSeriesID = 4
				AND Email.MODULE1 = 19
				AND Email.EmailCardID = inserted.EmailCardID
				AND Email.EmailDocumentID = 6
GO