Copy Account Categories Between Microsoft Dynamics GP Companies

Microsoft Dynamics GPWhen implementing Microsoft Dynamics GP for a new client, they usually have very similar setup between companies. While you can use the Professional Services Tools Library tool Company Copy, you sometimes need to replicate data which was configured after this function had been used.

I’ve previously posted scripts to copy segments and financial calendars; today’s script will copy Account Categories to a new company ensuring you have the categories in both companies:

Continue reading “Copy Account Categories Between Microsoft Dynamics GP Companies”

Update Navigation Pane Series Order

Microsoft Dynamics GPWhen I install demo VMs, I usually want everything setup the same way each time I do it. This includes things like inserting UK VAT Rates as well as some more mundane configuration issues like the order of series on the navigation pane.

That’s what the script in this post does; it switches off some of the series so they don’t show and reorders the remaining ones into the order I want them in.

Before running the script, set the USERID at the top (highlighted).

/*
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 @USERID VARCHAR(30) = 'iang' UPDATE SY07130 SET Visible = 0 WHERE USERID = @USERID AND CmdSequence BETWEEN 6 AND 9 UPDATE SY07130 SET CmdSequence = CmdSequence + 30 WHERE USERID = @USERID UPDATE SY07130 SET CmdSequence = 1 WHERE USERID = @USERID AND CmdParentCmdID = 11 UPDATE SY07130 SET CmdSequence = 2 WHERE USERID = @USERID AND CmdParentCmdID = 1 UPDATE SY07130 SET CmdSequence = 3 WHERE USERID = @USERID AND CmdParentCmdID = 6 UPDATE SY07130 SET CmdSequence = 4 WHERE USERID = @USERID AND CmdParentCmdID = 3 UPDATE SY07130 SET CmdSequence = 5 WHERE USERID = @USERID AND CmdParentCmdID = 7 UPDATE SY07130 SET CmdSequence = 6 WHERE USERID = @USERID AND CmdParentCmdID = 2 UPDATE SY07130 SET CmdSequence = 7 WHERE USERID = @USERID AND CmdParentCmdID = 4 UPDATE SY07130 SET CmdSequence = 8 WHERE USERID = @USERID AND CmdParentCmdID = 5 UPDATE SY07130 SET CmdSequence = 9 WHERE USERID = @USERID AND CmdParentCmdID = 10 UPDATE SY07130 SET CmdSequence = 10 WHERE USERID = @USERID AND CmdParentCmdID = 22002 GO

As always before running a script, ensure you have a good backup of your databases.

Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes

Microsoft Dynamics GPI’ve been receiving an error in the Fabrikam Sample Company database when trying to create a new Financial Calendar for a while and finally had the time to track it down.

The error appears when you enter a new year and click the Calculate button:

Error when calculating a new year

Microsoft Dynamics GP
[Microsoft][SQL Server Native Client 11.0][SQL Server[Violation of PRIMARY KEY constraint 'PKSY40100'. Cannot insert duplicate key in object 'dbo.ST40100'. The duplicate key value is (0, 2021, 0, 2 ,General Entry ).

Continue reading “Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes”

SQL Stored Procedure to Generate Sequential Number

Microsoft SQL ServerWhile much of the work I do is directly with Microsoft Dynamics GP, I also do work for clients which isn’t directly related. I’ve created code to generate numbers a few times in the past and figured I might as well post the base code I use for this to make it easier to find in future.

I’ve created it in such a way that several unique numbers can be stored and incremented.

The first part of the code creates a table to hold the number type and next number:

-- drop table if it exists
IF OBJECT_ID (N'ut_AZRCRV_NextNumber', N'U') IS NOT NULL
	DROP TABLE ut_AZRCRV_NextNumber
GO
-- create table
CREATE TABLE ut_AZRCRV_NextNumber(
	NMBRTYPE VARCHAR(50)
	,NEXTNMBR INT
)
GO

Next, I create a stored procedure which will increment and return the next number:

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_GetNextNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextNumber
GO
-- create stored proc
CREATE PROCEDURE [dbo].[usp_AZRCRV_GetNextNumber]
	(
	@NMBRTYPE VARCHAR(50)
	,@NEXTNMBR INT OUTPUT
	)
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). */
SET NOCOUNT ON BEGIN TRAN -- if this is the first value generated for this table, start with one IF NOT EXISTS (SELECT * FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE) INSERT INTO ut_AZRCRV_NextNumber (NMBRTYPE,NEXTNMBR) VALUES (@NMBRTYPE,1) -- select next number from table into variable SELECT @NEXTNMBR = NEXTNMBR FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE -- increment number by 1 UPDATE ut_AZRCRV_NextNumber SET NEXTNMBR = NEXTNMBR + 1 WHERE NMBRTYPE = @NMBRTYPE COMMIT TRAN -- return variable containing next number RETURN @NEXTNMBR GO

Then, I grant execute permissions to the relevant database role:

-- grant execute permission on stored proc to ur_AZRCRV_InvoiceUser
GRANT EXECUTE ON usp_AZRCRV_GetNextNumber TO ur_AZRCRV_InvoiceUser
GO

And finally, I have the SQL code which will generate the next number:

-- code to get next number
DECLARE @NMBRTYPE VARCHAR(50) = 'Sales Invoice'
DECLARE @NEXTNMBR INT

EXEC [usp_AZRCRV_GetNextNumber] @NMBRTYPE, @NEXTNMBR OUTPUT

SELECT @NEXTNMBR
GO

Add Company Access Back to sa User

Microsoft Dynamics GPWe’re busy doing some work for a client for whom we’ve recently taken over the support of their Microsoft Dynamics GP implementation. For the initial set of projects, we’re assisting them in the creation of a standalone test systemm. When you do this, the first thing you need to do is log in using the sa account and reset passwords.

However, we found that at some point in the past, all company access had been removed from the sa user account leaving us unable to log into Dynamics GP.

Fortunately, company access is only stored within one table in the system database: User-Company Access (SY60100).

The SQL below will add company access back to the sa user for all company databases:

/*
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).
*/
INSERT INTO SY60100
	(TRKUSER,USERID,CMPANYID,SRBCHSEC_1,SRBCHSEC_2,SRBCHSEC_3,SRBCHSEC_4,SRBCHSEC_5,SRBCHSEC_6,SRBCHSEC_7,SRSFNSEC_1,SRSFNSEC_2,SRSFNSEC_3,SRSFNSEC_4,SRSFNSEC_5,SRSFNSEC_6,SRSFNSEC_7,MSCPRMIS)
--VALUES
	(
	SELECT
		0,'sa',CMPANYID,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF,0xFFFFFFFF
	FROM
		SY01500 AS ['Company Master']
	WHERE
		(
		SELECT
			COUNT(*)
		FROM
			SY60100 AS ['User-Company Access']
		WHERE
			['User-Company Access'].CMPANYID = ['Company Master'].CMPANYID
		AND
			['User-Company Access'].USERID = 'sa'
		) = 0
	)
GO

After you’ve run the above to add company access back, you also need to run the SQL insert statement in this post to add POWERUSER access as well.

With the two scripts run, the sa account can be used to reset the DYNSA user and other user accounts.

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 Function to Remove Alphanumeric Characters

Microsoft Dynamics GPI created a customisation recently for a client which would generate a Vendor ID based on the name, by removing alphanumeric characters. In order to make it as flexible as possible, I created the function to accept a parameter for type which will cause the function to strip different characters:

  • A – leaves alpha characters only.
  • N – leaves numeric characters only.
  • AN – leaves alphanumeric characters.

The second parameter is the string which should have the characters stripped:

IF OBJECT_ID (N'uv_AZRCRV_StripCharacters', N'FN') IS NOT NULL
	DROP FUNCTION uv_AZRCRV_StripCharacters
GO

CREATE FUNCTION uv_AZRCRV_StripCharacters(@Type VARCHAR(100), @String VARCHAR(MAX))
	RETURNS VARCHAR(MAX)
/*
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).
*/
BEGIN
	DECLARE @PatIndex VARCHAR(20)
	IF (@Type = 'Alpha' OR @Type = 'A')
		SET @PatIndex = '%[^a-z]%'
	IF (@Type = 'Numeric' OR @Type = 'N')
		SET @PatIndex = '%[^0-9]%'
	IF (@Type = 'AlphaNumeric' OR @Type = 'AN')
		SET @PatIndex = '%[^a-z0-9]%'

	WHILE PATINDEX(@PatIndex, @String) < 0
		SET @String = STUFF(@String, PATINDEX(@PatIndex, @String), 1, '')

	RETURN @String
END

GRANT EXECUTE ON uv_AZRCRV_StripCharacters TO DYNGRP
GO

This Workflow Is Locked By Another User And Cannot Be Edited

Microsoft Dynamics GPI was onsite recently with a client making a Workflow project live and encountered a lock on the workflow we needed to amend. There were no users logged into the company so we knew the lock was an orphaned one.

This workflow is locked by another user and cannot be edited. Please try again later.

"This workflow is locked by another user and cannot be edited. Please try again later."

The locks on Workflow are stored in the Workflow User Security (WF00104) table; when clearing locks, care should be taken to only remove the lock required, so as not to cause additional problems.

The highlighted section should be changed to the Workflow Type for which the lock should be removed:

/*
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 FROM
	WF00104
WHERE
	Workflow_Type_Name = 'Purchase Requisition Approval'
GO

SQL Script to Delete Unused Segments

Microsoft Dynamics GPWhile the General Ledger Year-End Close routine can delete unused segments, during implementation, or creation of new companies , we sometimes end up with segments created which are not needed. The below script can be used to remove all segments not assigned to an account (segments which have been used will not be removed).

The script allows the user to define which segment should be removed by changing the highlighted parameter:

/*
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 @SGMTNUMB AS VARCHAR(2) = 3

DELETE FROM
	GL40200
WHERE
	SGMTNUMB = @SGMTNUMB
AND
	SGMNTID NOT IN (
			SELECT
				CASE @SGMTNUMB 
				WHEN 1 THEN GL100.ACTNUMBR_1
				WHEN 2 THEN GL100.ACTNUMBR_2
				WHEN 3 THEN GL100.ACTNUMBR_3
				WHEN 4 THEN GL100.ACTNUMBR_4
				WHEN 5 THEN GL100.ACTNUMBR_5
				WHEN 6 THEN GL100.ACTNUMBR_6
				WHEN 7 THEN GL100.ACTNUMBR_7
				WHEN 8 THEN GL100.ACTNUMBR_8
				WHEN 9 THEN GL100.ACTNUMBR_9
				WHEN 10 THEN GL100.ACTNUMBR_10
				END
			FROM
				GL00105 AS GL105
			INNER JOIN
				GL00100 AS GL100
					ON
						GL100.ACTINDX = GL105.ACTINDX
			)
GO

Find Table in All Microsoft Dynamics GP Databases

Microsoft Dynamics GPI 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 (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) = '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