Professional Services Tools Library – DBMS Error 4920

Microsoft Dynamics GPI ws recently doing some work for a client, one part of which was to configure Microsoft Dynamics GP to duplicate creditors created in one company into another using the PM Master Trigger functionality in the Professional Services Tools Library (PSTL).

When I tried to enable the trigger I received the following error message:

Microsoft Dynamics GP - The stored procedure failed to enable taVendorInsert returned the following results: DBMS: 4920, Microsoft Dynamics GP: 0Microsoft Dynamics GP – The stored procedure failed to enable taVendorInsert returned the following results: DBMS: 4920, Microsoft Dynamics GP: 0

Continue reading

New Book Available: Implementing the Microsoft Dynamics GP Web Client

Microsoft Dynamics GPLast year I had two books published via Packt Publishing, the Microsoft Dynamics GP 2013 Cookbook and Microsoft Dynamics GP 2013 Financial Management. Well, in the next few days I will have a new book available for purchase; Implementing the Microsoft Dynamics GP Web Client:

Implementing the Microsoft Dynamics GP Web Client

The book will initially be available from Amazon in the USA and various countries in Europe as a paperback and can also be purchased in PDF eBook form (the eBook is actually available now; the hard copy from Amazon and other retailers will follow soon).

This book provides guidance for the implementation of the Microsoft Dynamics GP web client with step-by-step guides, illustrations and links to useful resources.

Who This Book Is For

This book is aimed at Dynamics GP users and partners who intend to implement the web client for Microsoft Dynamics GP for individual clients; much of the process for installing a multi-tenant deployment is the same as the scale-out one, but this book is specifically focusing on the scale-out deployment. This book assumes you have a knowledge of Windows Server, Active Directory for creating users and groups, IIS and application deployments. A basic knowledge of Microsoft Dynamics GP is also required.

What This Book Covers

This book covers the scale-out deployment of the Microsoft Dynamics GP 2013 R2 web client, from prerequisites through installation to logging in, using and managing. An upgrade of the Microsoft Dynamics GP 2013 SP2 web client to Microsoft Dynamics GP 2013 R2 is covered, before closing with a look at troubleshooting problems.

How This Book Is Structured

Chapter 1, Introduction to the Microsoft Dynamics GP Web Client, covers the basics of the web client including a look at the pros and cons of the web client versus the classic desktop client and the deployment methods available.

Chapter 2, Prerequisites of the Microsoft Dynamics GP Web Client, covers the prerequisites for installing or upgrading the Microsoft Dynamics GP web client.

Chapter 3, Microsoft Dynamics GP Web Client Scale-Out Deployment, covers the scale-out deployment method of installing the Microsoft Dynamics GP web client.

Chapter 4, Logging In, Using and Managing the Microsoft Dynamics GP Web Client, covers the process of logging into the web client, the navigation differences between the desktop and web client versions of Microsoft Dynamics GP and then how the web client can be managed.

Chapter 5, Upgrading a Microsoft Dynamics GP Web Client Scale Out Deployment, covers the upgrade of the Microsoft Dynamics GP 2013 SP2 web client to the R2 version.

Chapter 6, Troubleshooting the Microsoft Dynamics GP Web Client, includes tips for troubleshooting and some of more common issues during the implementation.

I will be adding additional sales channels in the near future, but for now you can buy a PDF eBook, or go to CreateSpace, Amazon.com or Amazon UK for a paperback copy.

NB. The book can currently be bought on CreateSpace and Amazon UK, but not all of the Amazon sites have it listed as available; this should happen in the next few days.

Script To Copy Segments To All Companies

Microsoft Dynamics GPFollowing the last post I did, on copying segments to a new company, I did some thinking and realised that with a little more work on the script I could make it even more useful when setting up clients with multiple companies and the same chart of account structure.

I have added a cursor to the script which selects all company databases from the DYNAMICS System Database (change the highlighted DYNAMICS if you’re using a named system database) and then loops though them doing the insert from the SourceDatabase.

As before, the script checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID Cursor 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	WHERE
		INTERID <> @SourceCompany
	
	Open cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	While (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
				(SGMTNUMB
				,SGMNTID
				,DSCRIPTN
				,SEGCOUNT
				,NOTEINDX)
				
				(SELECT
					SGMTNUMB
					,SGMNTID
					,Left(DSCRIPTN, 30)
					,0
					,0
				FROM
					' + @SourceCompany + '..GL40200 AS SD
				WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
					WHERE GL.SGMTNUMB = SD.SGMTNUMB AND GL.SGMNTID = SD.SGMNTID) = 0)'

			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

Script To Copy Segments To A New Company

Microsoft Dynamics GPI have been doing some work with a client recently where we were creating a number of new companies which were to share the same chart of accounts (with only the first segment representing the company being different).

So we planned to use Integration Manager to to integrate a file containing the new chart of accounts. However, before we could load the accounts themselves we needed to get the Segments loaded.

Tis could be done by creating a SmartList in SmartList Designer to get the Segment Number, Segment ID and Segment Description and then use File Import to load the segments into the new company; or I could knock together a quick SQL script to do the job.

Obviously, as I am writing this post, I opted to go the route of writing an SQL Script. The highlighted pieces at the top are the Source and Destination Company databases; change these to the relevant databases.

The script also checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'TWOA'

SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
	(SGMTNUMB
	,SGMNTID
	,DSCRIPTN
	,SEGCOUNT
	,NOTEINDX)
	
	(SELECT
		SGMTNUMB
		,SGMNTID
		,Left(DSCRIPTN, 30)
		,0
		,0
	FROM
		' + @SourceCompany + '..GL40200 AS SM
	WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
		WHERE GL.SGMTNUMB = SM.SGMTNUMB AND GL.SGMNTID = SM.SGMNTID) = 0)'

EXEC (@SQLStatement)
GO

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

Implementing Fastpath’s Config AD: Configuration

In this series of posts I’m going to take a look at the Config AD product from Fastpath which adds single sign-on to the Dynamics GP desktop client; you can find my series index here.

With Config AD installed on the domain controller, we now need to configure it to connect to the required Microsoft Dynamics GP installation. To do this open the Active Directory Users and Groups, right click on a user and then click on Configure Dynamics User:

Active Directory Users and Groups

Continue reading

Implementing Fastpath’s Config AD: Introduction & Index

Microsoft Dynamics GP has traditionally had it’s own log on which means for a user who’s day job is based within GP, they need to log into Windows and then log into GP.

If you are using the web client Microsoft Dynamics GP 2013 R2 saw the introduction of Identity Management – Login which will provide single sign-on functionality. However, for those using the desktop client the best that Dynamics GP has to offer is the Remember User and Password introduced in Dynamics GP 2010.

However, Dynamics GP has a thriving community surrounding it which includes many ISVs (Independent Software Vendors), one of which is called Fastpath; this company has developed an add-on for Dynamics GP called Config AD which makes single sign-on with Dynamics GP.

Typically my posts have all been around the Dynamics GP core product or the add-ons from Microsoft with only a small number of posts on products from other vendors. In this series of posts I am stepping outside of this area as I am going to cover the install, configuration and use of Fastpath’s Config AD. The index for this series is below and will expand as posts are made.

Click to show/hide the Implementing Fastpath's Config AD Series Index

Installing Microsoft Dynamics GP 2013 R2: Series Index

Microsoft Dynamics GP 2013 R2 is now available. And despite note really having the time to do so at the moment, I can’t stop myself from installing it (and, as I always do, blogging about it as I go).

In this series of posts I am going to do install a stand-alone instance of Microsoft Dynamics GP 2013 R2; this post will serve as my series index so watch for the index, below, to grow as posts are submitted.

Click to show/hide the Installing Microsoft Dynamics GP 2013 R2 Series Index

Deployment Server Has Exceeded The Maximum Request Length Error Deploying Reports To SSRS

When Reporting Tools Setup is used t deploy the reports to SQL Server Reporing Services an error be produced if the default configuration of SSRS has not been amended (I thought I’d blogged about this error before, but when trying to find it to send the post to someone I couldn’t find it).

The error produced is the following:

Microsoft Dynamics GP - The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength="20690" in the web.config file and try deploying again.Microsoft Dynamics GP – The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength=”20690″ in the web.config file and try deploying again.

Continue reading

Microsoft Dynamics GP 2013 SP2 Web Client Scale-Out Deployment: Installing SSL Certificates On Client Machines

To avoid certificate errors with the Web Client, the security certificate from the web server needs to be installed onto the client machines (i.e. all of the PCs which will be running IE to connect to the website).

Before the certificate is loaded, you’ll see the following error when navigating to the GP web client:

Certificate Error: Navigation Blocked - Windows Internet Explorer

Continue reading

Microsoft Dynamics GP 2013 SP2 Web Client Scale-Out Deployment: Running The Web Client Configuration Wizard On The Session Hosts

After installing the Session Hosts you will be prompted to run the Web CLient Configuration Wizard. We ran this after installing the Session Central server to create the databases; it needs to be run again now, to tell the Session Host which Session Central Database should be used:

Microsoft Dynamics GP Web Client Configuration Wizard - Welcome

Continue reading