SQL Script to Update Web Services Server in Workflow Setup When Copying Live To Test

Microsoft Dynamics GPAll clients will at some point copy their live company into a test one, whether or not the test system is on the same server or a different one. If on the same machine, they will usually use an automated process to perform the backup and restore.

There is usually other tasks which will need to be undertaken such as changing the output location of the EFT Payment Register Report or prefixing email message subjects with “TEST” or setting all email addresses to internal ones.

I’ve recently encountered a couple more fields which need to be reset to avoid problems when copying live to test. The one prompting this script, was when the live company was copied to the standalone test system, the Web Services server needed to be changed to a different value:

/*
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).
*/
UPDATE WF00100
	SET
		Web_Service_Server = '{servername}'
WHERE
	SETUPKEY = 0
GO

The highlighted section, above, needs to be changed to your Web Services server.

In addition, a repair needed to be run using the Web Services Configuration Wizard, but at least the script removes one manual step.

As always with scripts, please make sure you have a good backup before using the script.

Hands On With Microsoft Dynamics GP 2016 R2: Web Services Runtime SQL Server Prerequisite

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components; the index for this series can be found here.

I’ve encountered this particular issue around the prerequisites so rarely, that I usually forget to mention it.

When installing the Web Services for Microsoft Dynamics GP there is a step where the SQL Server Connection Information is entered. This is the server where the Dynamics Security data will be stored.

You will receive the following message when clicking Next if the Windows user account you’re logged in as, is not a SQL Server sysadmin:

Invalid SQL Information - The current user must be a member of the sysadmin SQL role.

Invalid SQL Information

The current user must be a member of the sysadmin SQL role.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Web Services Runtime SQL Server Prerequisite”

Hands On With Microsoft Dynamics GP 2016 R2: Verify Web Services

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components; the index for this series can be found here.

Over the last couple of posts, I have installed and configured the Web Services for Microsoft Dynamics GP, it is now time to verify that they are working correctly; there are four items I’d recommend checking.

Firstly, open the Services applet from Computer Management and make sure the Microsoft Dynamics GP Service Host is set to a Startup Type of Automatic and that the service is Running:

Services

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Verify Web Services”

Hands On With Microsoft Dynamics GP 2016 R2: Configure Web Services

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components; the index for this series can be found here.

In the last post, I installed the web services; in this post, I’ll cover the configuration.

If, when you installed the web services, you left the Run Configuration Wizard checkbox marked, the Configuration Wizard will have started automatically. if you didn’t do this, the launch the Configuration Wizard from the Start menu.

On the Welcome screen, click Next:

Web Service for Microsoft Dynamics GP Configuration Wizard: Welcome to the Web Service for Microsoft Dynamics GP Configuration Wizard

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Configure Web Services”

Hands On With Microsoft Dynamics GP 2016 R2: Install Web Services Runtime

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components; the index for this series can be found here.

Over the last few posts, I have covered the installation of the web client as a scale-out deployment. After a few different Hands On… posts I have finally got the order of posts right by posting the web client installation before that of the Web Servies; this is important as there is a step of the Web Services which asks if you are using the Web Client.

If you are installing the Web Services and are using the Web Client, or plan to, then go back to this post, and it’s successors, and install the web client before proceeding with this post.

If you’re ready to install the Web Services for Microsoft Dynamics GP then launch the setup utility and, under Additional Products, click on Web Services Runtime:

Microsoft Dynamics GP 2016 setup utility

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Install Web Services Runtime”

Hands On With Microsoft Dynamics GP 2016 R1: Web Services Verification

Microsoft Dynamics GPOnce the Web Services have been installed and configured, it is recommend that they be verified to ensure everything is working as it should. There are four items which need to be checked.

The first check, is to make sure that the Windows service is running; check this by opening the Services Control Panel applet (easiest to do by hitting Win+R and typing services.msc; scroll down and find Microsoft Dynamics GP Service Host. Ensure it is running and set to Automatic:

Services

Continue reading “Hands On With Microsoft Dynamics GP 2016 R1: Web Services Verification”

Hands On With Microsoft Dynamics GP 2016 R1: Web Services Configuration

Microsoft Dynamics GPWith the Web Services for Microsoft Dynamics GP installed, it is time to do the configuration. The final step of the installation allows you to open the Web Services Configuration Wizard automatically, or it can be launched from the Windows start menu:

Web Services for Microsoft Dynamics GP Configuration Wizard

Continue reading “Hands On With Microsoft Dynamics GP 2016 R1: Web Services Configuration”

Hands On With Microsoft Dynamics GP 2016 R1: Web Services Installation

Microsoft Dynamics GPSince the introduction of Workflow 2.0 in Microsoft Dynamics GP 2013 R2, I have been doing a lot more installs of the web services. There are one or two gotchas (more at the configuration stage than the installation stage though) which I will highlight as we go through these next few posts.

Start the setup utility on the installation media. As web serices are likely to be installed on a server which does not already have any Dynamics GP components installed you will see the Bootstrapper Setup; click Install to install the required components:

Hands On With Microsoft Dynamics GP 2016 Bootstrapper Setup

Continue reading “Hands On With Microsoft Dynamics GP 2016 R1: Web Services Installation”

SQL Script To Return Functional Currencies For All Companies

Microsoft Dynamics GPI have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.

However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.

That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.

CREATE TABLE #FUNLCURR
	(
	INTERID VARCHAR(5)
	,FUNLCURR VARCHAR(15)
	)

DECLARE @SQL_Statement VARCHAR(1000)

DECLARE
	cursor_InterID CURSOR 
FOR
	SELECT INTERID FROM SY01500
	
	OPEN cursor_InterID

	DECLARE
		@INTERID VARCHAR(100)

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@INTERID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
				SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')'
				exec (@SQL_Statement)
				SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + ''''
				exec (@SQL_Statement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@INTERID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

SELECT
	#FUNLCURR.INTERID
	,SY01500.CMPNYNAM
	,#FUNLCURR.FUNLCURR
FROM
	#FUNLCURR
INNER JOIN
	SY01500
		ON SY01500.INTERID = #FUNLCURR.INTERID
/*WHERE
	LEN(FUNLCURR) = ''*/
ORDER BY
	#FUNLCURR.INTERID

DROP TABLE #FUNLCURR

Hands On With Microsoft Dynamics GP 2015 R2: Web Services Verification

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

Once the Web Services have been installed and configured, it is recommend that they be verified to ensure everything is working as it should. There are four items which need to be checked.

Firstly, open the Services Control Panel applet (easiest to do by hitting Win+R and typing services.msc; scroll down and find Microsoft Dynamics GP Service Host. Ensure it is running and set to Automatic:

Services

Continue reading “Hands On With Microsoft Dynamics GP 2015 R2: Web Services Verification”