Integration Manager eConnect Incorrect Address Or SOAP Action

Microsoft Dynamics GPI was recently creating an integration for a client to load some Purchase Orders into Microsoft Dynamics GP using Integration Manager. I added the source and destination adapters and then did the destination mapping. However, when I tried to run the integration I got the following error message:

DOC 1 ERROR: There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnterException, if present, for more details.

DOC 1 ERROR: There was no endpoint listening at net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/EntityOperations that could accept the message. This is often caused by an incorrect address or SOAP action. See InnterException, if present, for more details.

Continue reading “Integration Manager eConnect Incorrect Address Or SOAP Action”

Management Reporter Client Crash During Import of Building Blocks

Microsoft Dynamics GPI’m in the middle of an upgrade of a client to Microsoft Dynamics GP 2015 R2 and am also upgrading Management Reporter to CU12 (I’m not entirely happy with CU13 and am avoiding it for now). We initially had problems with the upgrade as the server ran out of HDD space due to the size of the database and the changes made during the upgrade.

After discussing it with the client, we agreed to re-implement Management Reporter and migrate all of the building blocks from the old server to the new one.

It was while doing the import into Management Reporter 2012 CU12 that I ran into a problem; a persistent crashing:

Management Reporter for Microsoft Dynamics ERP has stopped working

I’d already imported the Report Definitions without any problem and was looking to make sure all of the building blocks were imported when it crashed with the above error message. I restarted it and tried again and it again crashed on me.

I did a little playing round and found that it only seemed to crash if the house moved over the top of the Report Definitions in the scrolling window; if I moved the cursor around the outside of the scrolling window and selected the tab of any of the other building blocks, such as the Column Definitions, I could move the mouse over the top of them and make selections. Just not over the top of the Report Definitions.

I headed online to see if I could find anyone else talking about the same issue and came across a post from Stoneridge Software had found something very similar, although they reported it was only the left most column whereas for me it was all of the columns.

Stoneridge also report that the fix for this was in CU13.

Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 4 – Log Space

Microsoft Dynamics GPAs mentioned in the last post, I’ve been having some fun with Management Reporter recently. Unfortunately, Integration Manager the errors it produces are often less than useful. After resolving the issues with the schema owner, SQL Timeout and SQL Login, the attempt to upgrade Management Reporter produced another generic error message:

Management Reporter 2012: An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Management Reporter 2012

An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Continue reading “Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 4 – Log Space”

Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 3 – SQL Login

Microsoft Dynamics GPOver the last couple of postrs, I have discussed the first two error messages I encountered recently (schema owner and SQL Timeout) doing an upgrade of Management Reporter for a client.

After resolving the issue with the SQL Timeout error, the attempt to upgrade Management Reporter produced an error message at the validation stage:

Validation Messages: Database configuration: Unable to connect to the specified database server with the Management Reporter service account. Make sure the database server is correct and that the service account has a SQL Server Login.

Validation Messages

Database configuration: Unable to connect to the specified database server with the Management Reporter service account. Make sure the database server is correct and that the service account has a SQL Server Login.

After clicking Close I launched Management Studio and loaded the properties for the user account used for the Management Reporter services:

Login Properties

After clicking on User Mapping I was able to see that the Management Reporter database was not selected. I marked the checkbox next to it and clicked OK.

I was able to click the Proceed button and continue the deployment. With the extended timeout from the last post, I left the upgrade running in the back ground.

Unfortunately, the story was far from over.

Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 2 – SQL Timeout

Microsoft Dynamics GPAs mentioned in the last post, I’ve been having some fun with Management Reporter recently. Unfortunately, Integration Manager the errors it produces are often less than useful. After resolving the issue with the schema owner, the attempt to upgrade Management Reporter produced another generic error message:

Management Reporter 2012: An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Management Reporter 2012

An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Continue reading “Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 2 – SQL Timeout”

Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 1 – Database Principal

Microsoft Dynamics 365 Business CentralI’ve had some right good fun with Management Reporter recently, actually not so much fun, but the bane of my life (in the course of pretty much one day). And over the new few posts I’m going to share the trials and tribulations I have undergone.

A recent upgrade of Management Reporter resulted in a fairly generic error message being displayed while trying to configure the legacy connector (the connector type is not important for this error):

Management Reporter 2012: An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Management Reporter 2012

An error occurred while configuring 'Management Reporter Application Service'. Check the deployment log for details.

Continue reading “Management Reporter Error: An Error Occurred While Configuring ‘Management Reporter Application Service’ Part 1 – Database Principal”

SQL View To Allow Customer/Item Link In SmartList Builder

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink 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).
*/
	SELECT
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO

SQL Error: “The query uses non-ANSI outer join operators”

Microsoft Dynamics GPI’ve recently been working on a project upgrading a client from Microsoft Dynamics GP 9 SP3 to a later version and also from Microsoft SQL Server 2000 to SQL Server 2008 R2. Much of the upgrade has gone through without problems, but we’ve encountered one or two issues with customisations and custom reports.

The following error message was produced during testing when generating one of the old Crystal Reports:

Crystal Report Viewer - Failed to open rowset. Detsails: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

Crystal Report Viewer

Failed to open rowset.
Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

The compatibility level of a database for Microsoft Dynamics GP should NOT be changed back to 80 when this error is encountered. The solution to use is the recommended one from the error message: to rewrite the query.

In this case the client had a set of Crystal Reports written against GP 9 which called a variety of stored procedures. I spent a few hours reviewing and rewriting stored procedures to remove the non-ANSI outer joins and replacing them with ANSI ones.

SQL Script To Copy Tax Setup To All Companies Using Cursor

Microsoft Dynamics GPFollowing on from the creation of the script to copy tax schedules between companies, I was on a fairly long train journey and spent a small part of the time wrapping a cursor round the original script to allow tax schedules to be configured in one company and rolled through the others.

There is one setting at the top of the script to set the SourceCompany.

One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

/*
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 @SQLStatement AS VARCHAR(2000)
DECLARE @SourceCompany VARCHAR(5)
DECLARE @DestinationCompany VARCHAR(5)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)

			-- Sales/Purchases Tax Schedule Header Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
				(TAXSCHID,TXSCHDSC)
			--VALUES
				(SELECT 
					TAXSCHID,TXSCHDSC
				FROM
					' + @SourceCompany + '..TX00101 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Schedule Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
				(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
			--VALUES
				(SELECT
					TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
				FROM
					' + @SourceCompany + '..TX00102 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales Purchases Tax Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
				(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
				TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
				NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
				TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
			--VALUES
				(SELECT
					TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
					TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
					NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
					TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
				FROM
					' + @SourceCompany + '..TX00201 AS TX 
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Summary Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
				(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
			--VALUES
				(SELECT
					TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
				FROM
					' + @SourceCompany + '..TX00202 AS TX
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you use this script, then please make sure you have a good backup before running it and also test afterwards.

SQL Script To Copy Tax Setup To A New Company

Microsoft Dynamics GPWhile helping a client configure some new companies recently, we needed to create the tax schedules in each of the companies; unfortunately, there were about 30 tax schedules and 4 companies which needed them. To do manually, or even with a macro, was going to take a while, so I took a few minutes out to see if it was going to be possible to run the update using a SQL script.

The below is what I came up with and appeared to work fine for the companies we tested it in. One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

There are two variables at the top which will need to be configured before running the script: @SourceCompany and @DestinationCompany.

/*
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 @Statement AS VARCHAR(2000)
DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'PI'

-- Sales/Purchases Tax Schedule Header Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
	(TAXSCHID,TXSCHDSC)
--VALUES
	(SELECT 
		TAXSCHID,TXSCHDSC
	FROM
		' + @SourceCompany + '..TX00101 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Schedule Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
--VALUES
	(SELECT
		TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
	FROM
		' + @SourceCompany + '..TX00102 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
	(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
	TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
	NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
	TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
--VALUES
	(SELECT
		TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
		TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
		NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
		TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
	FROM
		' + @SourceCompany + '..TX00201 AS TX 
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Summary Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
--VALUES
	(SELECT
		TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
	FROM
		' + @SourceCompany + '..TX00202 AS TX
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

If you do use this script, then please make sure you have a good backup before running it and also test afterwards.