SQL Script To Return Functional Currencies For All Companies

● Ian Grieve ●  ● 1 Comment  ● 

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
● Categories: Dynamics, GP, Microsoft, Multicurrency, Web Services ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 3 Comments  ● 

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

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 2 Comments  ● 

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.

With the web services installed, the next step is to configure them for the companies being used. Do this by launching the Web Services Configuration Wizard from the Start screen.

On the Welcome screen, click Next:

Web Services for Microsoft Dynamics GP Configuration Wizard: Welcome

Continue reading → Hands On With Microsoft Dynamics GP 2015 R2: Web Services Configuration

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

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

● Ian Grieve ●  ● 2 Comments  ● 

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.

In the past, we used the Web Services for Microsoft Dynamics GP mainly for integration, but since Microsoft Dynamics GP 2013 R2 introduced Workflow 2.0 we have been installing it for more and more clients.

In this, the fifteenth post of the Hands on with Microsoft Dynamics GP 2015 R2 series, I’m going to step through the installation process and following it up in the next couple of posts with the configuration and verification of the installation.

To install the Web Services for Microsoft Dynamics GP launch the setup.exe from the downloaded installation media and, under Additional Products click Web Services Runtime:

Microsoft Dynamics GP setup utility

Continue reading → Hands On With Microsoft Dynamics GP 2015 R2: Install Web Services

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Management Tools Installation

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

In the last few posts, I’ve covered the installation and configuration of the web services and rounded off with verifying that they are working correctly. The final item I’m going to cover is the optional installation of the Web Services Management Tools; which means the installing the Dynamics Security Console on another machine. Doing this is best practice as it means you don’t have to give access to the server running the web services themselves to all users who might maintain security.

To install the Web Services Management Tools, open the setup.exe from the installation media and, under Additional Products, select Web Services Management Tools:

Microsoft Dynamics GP 2015

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Management Tools Installation

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Security Verification

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

In the last post, we verified that the web services service was running and that the web services were working; in this post we’re going to verify the security was correctly deployed.

To do this open the Dynamics Security Console:

Windows Start screen

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Security Verification

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Service Verification

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

After installing and configuring the web services, I would always recommend verifying the install to ensure they are working correctly.

The first step, is to check the Windows Service is running; to do this open the Services control panel applet (which can also be accessed by using Win+R and typing services.msc).

Scroll down the list and find Microsoft Dynamics GP Service Host and make sure it is Running and set to Automatic:

Services

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Service Verification

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Configuration

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

With the web services installed, the next step is to configure them using the Web Services for Microsoft Dynamics GP Configuration Wizard. Launch the wizard and click Next on the welcome screen:

Web Services for Microsoft Dynamics GP Configuration Wizard - Welcome to the Web Services for Microsoft Dynamics GP Configuration Wizard

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Configuration

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Installation

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPNow that Microsoft Dynamics GP 2015 RTM is available, I thought I could follow my usual pattern of posting a series of posts on how to install and configure both Dynamics GP, but also the additional products which ship with it. You can find the series index for these posts here.

With the web client, installed and tested it’s time to move onto the next of the additional tools that come with Dynamics GP 2015. As I intend to make use of the Workflow 2.0 I need to have the web services installed to be able to make use of the email actions.

So the next five posts will look at the installation and configuration of the web services. To begin the installation of the web services, open the setup utility and select :

Micvrosoft Dynamics GP 2015

Continue reading → Hands On With Microsoft Dynamics GP 2015 RTM: Web Services Installation

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Web Services Management Tools Error – Could not access the SecurityService service

● Ian Grieve ●  ● 0 Comments  ● 

While I was working on the series of posts for how to install the Web Services for Microsoft Dynamics GP, I encountered a couple of problems which I thought it might be worth covering; in the last post I looked at resolving a problem with the eConnect Integration Service not running. The second error I encountered was after I installed the Management Tools on a client machine and tried to start it;

Could not access service: Could not access the SecurityService service. The request failed with HTTP status 404: Not FoundCould not access service: Could not access the SecurityService service. The request failed with HTTP status 404: Not Found

Continue reading → Web Services Management Tools Error – Could not access the SecurityService service

● Categories: Dynamics, GP, Microsoft, Web Services ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●