SQL Script to get all accounts set as available in Purchasing lookups

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each combination.

/*
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). */
SELECT ['Account Master'].ACTINDX
,['Account Index Master'].ACTNUMST
,['Account Master'].ACTDESCR
FROM GL00100 AS ['Account Master'] INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE DSPLKUPS IN
(
0x04000000 -- purchasing ,0x05000000 -- sales/purchasing ,0x06000000 -- inventory control/purchasing ,0x07000000 -- sales/inventory control/purchasing ,0x0C000000 -- purchasing/payroll ,0x0D000000 -- sales/purchasing/payroll ,0x0E000000 -- inventory control/purchasing/payroll ,0x0F000000 -- sales/inventory control/purchasing/payroll ,0xFFFFFF7F -- never set ) GO

SQL Script to get all vendor purchasing accounts

Microsoft Dynamics GPI recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.

The SQL is fairly straightforward, but I had to make sure each account was only included once:

/*
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). */
SELECT DISTINCT ['Account Master'].ACTINDX
,['Account Index Master'].ACTNUMST
,['Account Master'].ACTDESCR
FROM PM00200 AS ['PM Vendor Master'] LEFT JOIN PM00203 AS ['Vendor Accounts'] ON ['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID
LEFT JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX
OR ['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX
LEFT JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE ['PM Vendor Master'].VENDORID = 'ADVANCED0001'
GO

SQL Script to find SQL Objects containing specific text

Microsoft SQL ServerI did some work for a client recently which involved some custom SQL objects created by a consultant at their previous VAR. It appeared that there was a consistent prefix on the created objects which meant we could use a script I’ve previously posted to identify them all.

However, we quickly realised there were other objects which did not adhere to the naming convention we’d identified. Fortunately, I remembered writing a script to update database names in SQL views after copying live to test which identified the views to update by using the sys.sql_modules object.

Using that oncept, I came up with the below script which will search for the text specified in the variable at the top in all objects:

/*
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 @SearchString VARCHAR(100) = 'string to find' SELECT DISTINCT ['sys objects'].name AS 'Object Name' ,['sys objects'].type_desc AS 'Object Type Description' FROM sys.sql_modules AS ['sys modules'] INNER JOIN sys.objects AS ['sys objects'] ON ['sys objects'].object_id = ['sys modules'].object_id WHERE ['sys modules'].definition like '%' + @SearchString + '%'

Hands On with Microsoft Dynamics GP October 2019 Release: System Requirements

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release series where I am going hands on with the new version of Microsoft Dynamics GP.

The October 2019 release of Microsoft Dynamics GP largely has the same system requirements as Microsoft Dynamics GP 2018 R2 with the addition of the following:

  • Windows Server 2019
  • SQL Server 2019
  • Office 2019

SSRS requires that you use local SQL Server relational databases… error

Microsoft Dynamics GPMicrosoft Dynamics GP ships with a set of default reports which can be deployed into SQL Server Reporting Services; before doing the deploy, there is a setting in the SSRS web.config file which needs to be changed to avoid a maximum request length error.

Recently, when performing an upgrade of a clients system to Microsoft Dynamics GP 23018 R2, the deployment of the reports went fine, but we encountered a problem while testing the deployed reports:

SSRS reports error message

The client had a licenced version of SQL Server 2018 Web Edition which worked in all other respects; the SSRS service was running locally on the SQL Server which was hosting the databases for both SSRS and Microsoft Dynamics GP itself.

This wasn’t something I’ve encountered before, but, fortunately, a colleague had. The problem is not with the Web Edition itself, but rather with the data connection deployed by Microsoft Dynamics GP for the reports. When the data connections are deployed, there is a space created preceding the Data Source name:

Connection settings in SSRS Manager

The fix is to go through all of the datab connections and remove the space after the equals sign, so that, in the example above, Data Source= IG-SQL2017-01\GP... becomes Data Source=IG-SQL2017-01\GP....

Cannot Launch SQL Server Management Studio

Microsoft SQL ServerI work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.

If I tried to start it by typing ssms in a command prompt I received the following error:

ssms error

ssms1

Windows cannot find 'ssms'. Make sure you rtyped the name correctly, and then try again.

I did some exploring and found this article on Stackoverflow which resolved my problem.

Find the Ssms.exe.config file and erase the line <NgenBind_OptimizeNonGac enabled="1" />:

Noterpad.exe showing Ssms.exe.config

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 (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).
*/
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

Can I Create a Temp Table With Collation Different to the Server Collation?

Microsoft SQL ServerAs with virtually all headlines containing a question, the short answer is no. From the research I have done, the temp table will always be created with the collation of the SQL Server. If you want to skip to the solution, click here.

Why would you want a different collation? Well, in this case we need a different collation as there is a third party database which a customisation in Microsoft Dynamics GP is using for pricing lookups. The database was originally on a server with the same collation (SQL_Latin1_General_CP1_CI_AS) as the Dynamics GP server (and which the database shares). However, the database has now been moved to a new server with the default UK server collation (Latin1_General_CI_AS).

The problem now is that when data is being inserted or updated into this database, a collation error is produced:

Cannot resolve the collation conflict

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal operation.

Code: 8004OE14

Source: Microsoft OLE DB Provider for ODBC Drivers

Continue reading “Can I Create a Temp Table With Collation Different to the Server Collation?”

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.