Month: March 2022

I recently needed to find which databases in SQL Server had a specific view deployed to them. I’ve created scripts in the past to find SQL objects in all databases (table,trigger and functions), but not one for SQL views.

Some of the previous scripts looked in all databases and others were limited to only the Microsoft Dynamics GP databases; this script is one of the latter, using the SY015000 table to only search for a SQL view in the Dynamics GP databases:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 @command NVARCHAR(MAX)

DECLARE @SystemDatabase VARCHAR(15) = 'D20'
DECLARE @View VARCHAR(50) = 'uv_AZRCRV'
CREATE TABLE #ReturnedData(
	DBNAME VARCHAR(15)
	,VIEWNAME 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, VIEWNAME) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.type = ''V'' AND o.name LIKE ''' + @View + '%'')
						END'

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

After assisting a client with a server migration, an issue was reported with a fixed asset integration:

DOC 1 ERROR: The stored procedure 'taCreateAssetID' doesn't exist.

The first thing we checked was to see if the stored procedure did exist, which it did. The next option was a suspicion of permissions. After checking in SQL I found that the service account did not have access to all of the company databases; we added the DYNGRP database role to the service account and tried the integration again and found that it now ran successfully.

I was doing some work with a client recently where we needed to output two different formats of EFT file for the same bank account. This is possible using the EFT Transfer Method on the bank on the creditors. However, with a large number of creditors we needed a way to update the records in bulk. While it would be possible to do this with a macro, it would be fiddly and take a while.

Instead, I created a SQL script which can be run to update the Address Electronic Funds Transfer Master (SY06000) table:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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
	['Address Electronic Funds Transfer Master']
SET
	EFTTransferMethod = 2
		/*
			1=Not Specified
			2=Business Account
			3=Corporate Account
			4=Personal Account
			5=Foreign Account
		*/
FROM
	SY06000 AS ['Address Electronic Funds Transfer Master']
WHERE
	SERIES = 4
AND
	EFTBankType = 3 
		/*
			2 = Ireland
			3 = United Kingdom
			26 = Other 1
			27 = Other 2
			30 = Canada
			31 = United States
		*/
GO

The script is currently set up to update only UK banks and to set the EFT Transfer Method to Business Account. The green comments show some of the other values which you can use.

If you use the script, make sure you have a good backup before.

Working on a recent project for a client we needed to extract the chart of accounts from one Microsoft Dynamics GP company and import it into a couple of others (altering the first segment during the process. The below script was created to extract all of the chart of account data in the format required for import through Integration Manager:

SELECT
	['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,CASE WHEN ['Account Master'].ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END AS 'Active'
	,['Account Master'].ACCTENTR AS 'Allow Account Entry'
	,['Account Master'].ACTALIAS AS 'Account Alias'
	,['Account Category Master'].ACCATDSC AS 'Account Category Description'
	,CASE WHEN ['Account Master'].PSTNGTYP = 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type'
	,CASE WHEN ['Account Master'].TPCLBLNC = 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance'
	,['Account Master'].USERDEF1 AS 'User Defined 1'
	,['Account Master'].USERDEF2 AS 'User Defined 2'
	,['Account Master'].USRDEFS1 AS 'User Defined 3'
	,['Account Master'].USRDEFS2 AS 'User Defined 4'
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master']
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM[

I was doing some work for a client to build a workflow process in Microsoft Dynamics GP recently and we only got the SMTP details quite late in the process. This meant that we built quite a large workflow process without being able to enable the email notifications. Once we did have the SMTP available we needed to go back through all of the steps and enable the email notifications.

Rather than do this manually, which would have been a time consuming process, I looked at the tables and identified the table and fields which would need to be updated. This was the Workflow Step Table (WF100003) table; the below script can be used to enable the notifications, set the Message ID and enable the include Document Attachments (this was for a payables batch approval process):

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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
	WF100003
SET
	EmailMessageID = '^ASSIGN PO APPROVAL'
	,Workflow_Step_Send_Email = 1
	,WFIncludeDocumentAttach = 1
WHERE
	Workflow_Step_Send_Email = 0

As always with scripts, make sure you have a good backup and test before using on a live system.