Microsoft Dynamics Convergence is around the corner!

This is it! Microsoft Dynamics Convergence 2013 New Orleans is here. If you haven't registered it's never too late. All my sessions are now scheduled and I just wanted to walk through some of them and ask for your support - this is, I want to see you there!

GENGP01 - Getting Inspired with Microsoft Dynamics GP
Tuesday, March 19th from 11:00 am - 12:00 pm
Well, I finally get my 5 minutes of fame on the big stage - a chance to present at the General Session. I will be doing something pretty neat with MVP Mark Polino over at DynamicAccounting.net, together on stage. Needless to say, it involves swords and axes, but you need to come to see it.

CSGP24 - Administering SQL Server for Microsoft Dynamics GP
Wednesday, March 20th from 11:00 am - 12:00 pm
I have been asked to pickup on a topic that garnered some attention at the past Microsoft Dynamics Convergence in Houston, and for the first time I will be dedicating a full hour to SQL Server administration for Microsoft Dynamics GP. Come learn some of the tips, tricks, and considerations I used to maintain my customers' database environments running like a Swiss clock.

CSGP30 - The Microsoft Dynamics GP Support Debugging Tool
Thursday, March 21st from 2:30 pm - 3:30 pm
Someone has to carry the Support Debugging Tool flag, correct? It's too bad that my good friend David Musgrave will not be here to introduce the upcoming build 18.

Well, this time I have a dazzling array of features to show you including some exclusive ones to appear in the upcoming build 18. Also, want to see the Support Debugging Tool on the Web Client? Come and learn what will work and what won't. I have it all for you.

CSGP32 - Microsoft Dynamics GP 2013 Web Client For Smaller Companies
(R1) Wednesday, March 20th from 2:00 pm - 3:00 pm
(R2) Thursday, March 21st from 5:30 pm - 6:30 pm    
This is my second 5 minutes of fame. The good fellows over at Microsoft have invited me to present with the dynamic duo of Kevin Racer and Daryl Anderson - twice! You know you have made it in the Microsoft world if you start to hang out with these two. Nonetheless, come see us. This session promises to be very fun, while very instructive.

Well, as you see, lot's on my plate for this upcoming event, but you know what, I LOVE IT! I love seeing every single one of you in my sessions: I love to meet first time attendees just as much as I enjoy talking to my long time followers and by now friends. If you are not attending my sessions, oh well! Just make sure you attend many of the other fun and instructional ones.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Deploying Microsoft Dynamics GP 2013 on Azure - Introduction

Today is the BIG DAY! Microsoft Dynamics GP 2013 has been released!

PartnerSource: Microsoft Dynamics GP 2013 Product Release Download page
Microsoft Download Center: Microsoft Dynamics GP 2013 What's New


With that said, I wanted to move forward and start to look at some really cool stuff you can do with Microsoft Dynamics GP 2013 and the Web Client. Now that I've had a chance to deploy the Web Client on a number of sandboxes here in my test lab and pretty much have all the on-premise deployment down, it's time to show you one more cool stuff: Microsoft Dynamics GP 2013 and Web Client deployment on Microsoft Azure platform.

Windows Azure is an open and flexible cloud platform that enables you to quickly build, deploy and manage applications across a global network of Microsoft-managed datacenters. You can build applications using any language, tool or framework. And you can integrate your public cloud applications with your existing IT environment. The overall goal of Windows Azure is to provide a scalable PaaS (Platform as a Service) and IaaS (Infrastructure as a Service) to subscribers.

Examples and SDKs are available in the Developer Center at

http://www.windowsazure.com/en-us/develop/overview/.

Microsoft Dynamics GP 2013 Azure Deployment

A Microsoft Dynamics GP 2013 deployment on Microsoft Azure focuses on 4 main aspects (assuming you have already completed all the sign up and subscription process):

1. Provisioning a virtual network, which can be optionally integrated to your local network.
2. Provision an Active Directory Domain Controller to manage the virtual network environment. (optional depending on whether you integrate the virtual network to your local network or not)
3. Provisioning the different virtual machines to support the IIS web server, Web Client, and Web Management Console applications; the various services (Session Central, Session Service, Tenant Services, and the GP Web Services) if deploying each service on separate servers; and the session hosts virtual machines to host the Microsoft Dynamics GP runtime client.
4. Provisioning a Microsoft SQL Server 2012 virtual machine and storage to host the various company and system databases and deploy a Reports Server.

NOTE: SQL Server 2012 will need to be reconfigured to support SQL authentication. The standard galleries provided on the Azure platform are configured by default to support Windows authentication. SQL Azure is currently not supported with Microsoft Dynamics GP 2013 on Windows Azure.

A simple Azure deployment environment is shown in the above picture.

Once the environment has been provisioned, the Microsoft Dynamics GP installation process is fairly comparable to that of an on-premise installation. The installation process will start by installing Microsoft Dynamics GP desktop client on the web client server (or on the SQL Server) and running GP Utilities to set up the databases. The installation is done via Remote Desktop Client which is used for management of the environment only.

Up next is the installation of the web client and web management console applications on the IIS web server. An endpoint will need to be created using the Azure Management Portal for Internet access to the virtual machines. Upon completing the deployment of the Azure environment, access to GP is achieved via Internet Explorer from outside the Azure virtual network.

This is just the proverbial tip of the iceberg. In the next few days, I will be whipping out the credit card to setup an Azure subscription and walk through all the different provisioning steps and the Web Client installation process. I will detail all of these in a series of articles to come.

Special thanks to Daryl Anderson, Senior Program Manager and Aaron Donat, Senior Escalation Engineer at Microsoft for their input and editorial contribution to this article

Downloads

Microsoft Dynamics GP 2013 and Windows Azure (D109GPandAzure.pptx)

Resources

To read more about Microsoft plans for the Dynamics applications and Azure, click on the following link over at MSDynamicsWorld:

Microsoft Shares Updated Azure Plans for Dynamics NAV 2013, GP 2013, AX

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Extracting data from Microsoft Dynamics GP company databases using SQL Server FOR XML and XMLNAMESPACES

I truly love what I do. Really!

My job takes me just about everywhere around this great country of ours and beyond its borders in the quest of helping clients and partners get the best out of their Microsoft Dynamics GP application and data.

In reference to the latter - data - I had been asked recently by a client how they could produce XML formatted data from their Microsoft Dynamics GP databases to be consumed by some web services applications they had developed. Some conditions around this request:

• The client did not want to implement eConnect Requester, though I have to admit this would have been a slam dunk with MSMQ queues. 
• The XML documents needed to be rather available and changeable very quickly to serve other needs.
• No additional investments in third party products, middlewares or the likes could be suggested since budget was pretty tight.

In other words, no eConnect, no third party products, and lots of flexibility...

The answer could only be one: use the powerful XML capabilities of T-SQL to get data out in the format required by the client.

Some theory

There's a powerful option when querying data from SQL Server for use with third party applications and/or web services. You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions.

The FOR XML clause has some great benefits:

• It allows a SQL Server developer of Microsoft Dynamics GP consultant to write critical pieces of integration architecture without having to learn the destination system's schema.
• Additional table columns - pieces of data, if you will - can be added to the results with relative ease.
• It's an efficient way to process data and reduces the number of components that must be developed.
• It can be formatted to match target schemas in order to simplify mapping and/or middleware configuration.

There are a number of options related to using the FOR XML clause in SQL Server. The most appropriate way I have found - best practice, if you will - is to declare your own namespace using the WITH XMLNAMESPACES clause and to format the XML specifically as expected with the PATH mode.

Keep in mind that SQL Server has an AUTO mode which allows it to automatically format the XML document for you, relinquishing some control from you the developer or consultant.

For more information on SQL Server FOR XML and the WITH XMLNAMESPACES clauses, please take a look at SQL Server Books Online:

MSDN - FOR XML clause - http://msdn.microsoft.com/en-us/library/ms178107.aspx
MSDN - WITH XMLNAMESPACES clause - http://msdn.microsoft.com/en-us/library/ms177400.aspx

Now a practical application...

This is a simple example on how to implement all of it together. Let's take the case of a Customer with multiple addresses. The following query should produce XML data with our customer master (RM00101) and address master information (RM00102).

CustomerExtract.sql

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

WITH XMLNAMESPACES('http://sql.customer.extract' as "ce0")
SELECT NULL
    , (  SELECT RM00101.CUSTNMBR AS [ce0:CustomerNumber]
   ,RM00101.CUSTNAME AS [ce0:CustomerName]
   ,RM00101.CHEKBKID AS [ce0:CheckbookID]
   , ( SELECT RM00102.ADRSCODE AS [ce0:AddressCode]
    , RM00102.ADDRESS1 AS [ce0:Address1]
    , RM00102.ADDRESS2 AS [ce0:Address2]
    , RM00102.CITY AS [ce0:City]
    , RM00102.[STATE] AS [ce0:State]
    , RM00102.ZIP AS [ce0:Zipcode]
    FROM RM00102 
    WHERE RM00102.CUSTNMBR = RM00101.CUSTNMBR
    FOR XML PATH('ce0:Addresses'), TYPE)
   FROM RM00101
  FOR XML PATH('ce0:Customer'), TYPE)
 FOR XML PATH ('ce0:CustomerExtract'), TYPE

The results are pretty straight forward:

FOR XML output (formatted for display purposes only)

Nothing but XML greatness!

The above query could have been encapsulated in a stored procedure with a parameter for customer number, which could have driven the results displayed. As you can tell, getting the data you need for any destination, will depend on you specific requirements, but it's doable with the power of SQL Server and T-SQL.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP 2010 now compatible with Microsoft SQL Server 2012

For those of you who have been wondering about Microsoft Dynamics GP 2010 compatibility with Microsoft SQL Server 2012, it's now official! You can find the hot topic article on CustomerSource and PartnerSource at:

CustomerSource - Microsoft Dynamics GP and Microsoft SQL Server 2012 Compatibility
PartnerSource - Microsoft Dynamics GP and Microsoft SQL Server 2012 Compatibility

Click on the image above to find out more about Microsoft SQL Server 2012 and to download the evaluation version. I just found something to play around with during my Spring break.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Management Reporter - The operation could not be completed due to failure on the server

What would be the New Year without a little drama?

I don't often write about Management Reporter, however, we just went live at a client site this past Tuesday, with everything working just as expected - by us and the client. However, just a few days working with their new environment, the client decided to run some of the financial reports we'd created with Management Reporter. Upon attempting to generate the report, the client received the following error:

Attempting to outrule any permissions issue, I ran the report with the SQL Server system admin (sa) account and got the same error.

Since the error was not descriptive enough, I turned to the Windows Applications Event Viewer and immediately spotted 3 entries. The first entry clearly stated the problem:

"Length of LOB data (678233) to be replicated exceeds configured maximum 65536. The statement has been terminated"

Because the exception was generated by a SQL client component, I could definitely rule out an issue with Management Reporter.

The second entry shows the service model fault exception messaging component that trapped the error and relied it to the Management Reporter component.

Application Event Viewer - 2nd Entry

The third entry showed the Management Reporter component that was directly affected by the cascading errors.

The original error message in the Event Viewer pointed to the length of the LOB data used for replication. This prompted me to think back for a bit and I realized that the hosting provider for my clients infrastructure and data had configured transactional replication in the past days based on the instructions provided in Microsoft Support article 926490.

Hence, it was not a surprise to me the error was referencing an issue with replication. In doing some extra search, I came across a SQL Server setting in the Advanced tab that allows you to set the Max Text Replication Size. By changing this value to -1, SQL Server would acknowledge no limit to the size of any character, XML, or image data type that could be replicated with a single insert or update operation.

The good thing also was, after setting the value, I did not have to restart the SQL Server service which made the client pretty happy as well. We re-ran the MR report and problem was gone!

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Granting Access and Binding Defaults when recreating SQL Tables: a follow up

In his most recent article, Granting Access and Binding Defaults when recreating SQL Tables, my good friend, David Musgrave, points out how to drop and re-create a Microsoft Dynamics GP object with the SQL Server Management Studio (SSMS) database object scripting capabilities.

His article overall addresses issues with objects not being properly upgraded from previous releases of Microsoft Dynamics GP to the version you are trying to upgrade to, and how to deal with these issue, so please read the articles as they contain valuable upgrade tips.

However, in his article, David points out that SSMS does not generate the object permissions and bindings necessary, hence the reason for the script he created and attached to his article. The bottom line is, these are configurable settings under SSMS's SQL Server Object Explorer options. To activate scripting of permissions and bindings in SSMS, follow these steps:

1. Open SSMS and go to the Tools | Options menu. This will open the Options window. Locate the SQL Server Object Explorer options and expand, then highlight Scripting to activate all scripting options.

SQL Server Management Studio Options window

2. Under Object Scripting Options, change Script Permissions to True.

Script permissions option

3. Under Table and view options, change Script bound defaults and rules to True.

Script bound defaults and rules

4. Click Ok to acknowledge all changes and exit the Options window.

Now let's test this... if we choose to script the SOP10110 table referenced in the sample script provided by David in his article, the following are the results:

USE [TWO]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__ENDDAT__41320FDC]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__ENDDAT__41320FDC]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__STRTDA__42263415]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__STRTDA__42263415]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOP10110]') AND type in (N'U'))
DROP TABLE [dbo].[SOP10110]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SOP10110](
 [PRCSHID] [char](15) NOT NULL,
 [DESCEXPR] [char](51) NOT NULL,
 [NTPRONLY] [tinyint] NOT NULL,
 [ACTIVE] [tinyint] NOT NULL,
 [STRTDATE] [datetime] NOT NULL,
 [ENDDATE] [datetime] NOT NULL,
 [NOTEINDX] [numeric](19, 5) NOT NULL,
 [PROMO] [tinyint] NOT NULL,
 [CURNCYID] [char](15) NOT NULL,
 [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKSOP10110] PRIMARY KEY NONCLUSTERED 
(
 [PRCSHID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[PRCSHID]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[DESCEXPR]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[NTPRONLY]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[ACTIVE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[STRTDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[ENDDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[SOP10110].[NOTEINDX]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[PROMO]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[CURNCYID]' , @futureonly='futureonly'
GO

GRANT DELETE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT INSERT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT SELECT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT UPDATE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[ENDDATE])=(0) AND datepart(minute,[ENDDATE])=(0) AND datepart(second,[ENDDATE])=(0) AND datepart(millisecond,[ENDDATE])=(0)))
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[STRTDATE])=(0) AND datepart(minute,[STRTDATE])=(0) AND datepart(second,[STRTDATE])=(0) AND datepart(millisecond,[STRTDATE])=(0)))
GO

Note that the name of your constraints may be different, however note that all permissions are granted to the DYNGRP role and that all defaults are properly binded for each currency, date, char, and integer data types.

As usual and if nothing else, you now have 2 methods of dropping and creating SQL Server objects, granting permissions and binding defaults.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Running Fixed Assets Depreciation causes Microsoft Dynamics GP to "hang"

I just completed a full upgrade of Microsoft Dynamics GP from version 9 to version 2010 R2 for a client and they were going through their first month-end closing in the upgraded system. 3 weeks ago, after the upgrade, they reported experiencing an issue running Fixed Assets depreciation from two laptop computers, where apparently, when running depreciation the system would hang. The only option to recover would be to terminate the Dynamics.exe process from Task Manager. Nonetheless, we did not pay much attention to this at the time since the process was completed successfully from another machine, just in time to close the month of August - more on this later.

The client called back on Thursday morning, letting me know they were ready to run Fixed Assets depreciation again, and this time I offered to be onsite to see the problem first hand. So effectively this past Friday morning I drove to their location and stood behind the Sr. Accountant to see the process in action and spot any possible issues while there. The accountant proceeded to log into the company database for which he would run the depreciation, entered his September cutoff date and clicked on the Depreciate button... as luck would have it with some support cases, nothing happened and the process completed successfully. Well, after some chuckles and the typical apologies from the client, I was back in my car on the way home.

Fixed Assets - Depreciation Process Information


Halfway through, I received an email saying that as soon as I left, they logged into another company and were able to reproduce the hung up issue.

Now, I began playing all the typical troubleshooting plots in my head... the problem happens only in one company, the problem can be reproduced by all users, the problem can be reproduced on all machines. Typically, when an issue is constrained to one company, it's related to some problem with the data or the way that company is configured. Not a bad proposition since I was only dealing with some 300 assets... but I am in my car, remember? So I offered the client to look at the issue when I was back in front of my computer, since I had discarded a user or workstation being the culprits.

Back at home I VPN'd into their system, then RDP'd to the SQL Server. I had the Sr. Accountant log into GP and start the depreciation process again. In troubleshooting the issue, I could see that the depreciation process was being correctly added to the Process Monitor and that the process showed Active, but it did not seem to complete.

Process Monitor

I also ran a SQL Profiler and noticed that the same set of T-SQL instructions would appear to be processed over and over at the database level. This told me the depreciation process was in an endless loop of some kind and something was preventing it from finishing.

SQL Profiler Trace

I then offered to run the process from the server with the 'sa' user and noticed that the depreciation was stopping on a particular asset ID (by clicking on the Progress button). This was now promising, because I now had a piece of data to look at.

Fixed Assets Progress window

I queried the Asset Master table and noticed that this particular asset had an acquisition cost of zero. In looking at the Asset Book, I noticed that the Cost Basis was USD $.01 (1 penny). Not sure why this grabbed my attention, but I asked the Sr. Accountant why had they set this asset up this way and he replied that they did it only to record the asset and keep track of its location, but that it had been fully depreciated in the past.

Asset General Information

Asset Book

He also added that the process was working fine in GP 9.0

So I figured I would try something by changing the Depreciation Method to "No Depreciation". After all, if the asset had an acquisition cost of zero and a Cost Basis of 1 penny, what was there to depreciate? I ran the following statement to change the Depreciation Method to "No Depreciation":

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

-- Remove the lock for the book being depreciated
DELETE FROM FA40203;

-- Change the depreciation method
UPDATE FA00200 SET DEPRECIATIONMETHOD = 15 WHERE COSTBASIS <= .01;

I then asked the Sr. Accountant to re-run the process and this time it completed in less than 10 seconds and produced the reports he was expecting.

Since it was not enough to fix this issue, I went out to the Microsoft Dynamics GP Partner Online Technical forum and found a case where a partner reported having the same issue at her client's site. It seems Microsoft has identified and logged this as a problem report, but no concrete fix date has been given for it. So for now, the above query should do.

Also, you could end up with a cost basis of 1 penny at the end of the useful life of an asset, which would throw the system into an endless loop if you attempt to depreciate such assets once more. If you feel this is your case, the above script should also correct the problem.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Getting the Next Voucher Number for a Payables Transaction Integration

At the beginning of the year, I wrote a 2-part series on retrieving document numbers assigned by Microsoft Dynamics GP when a field rule is set to Use Default in Integration Manager, see:

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 1

IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 2

Back then, I was addressing an issue faced by many working with integrations that require you to pass back some value to a source system and the complexities involved in retrieving those values, especially when the field rule is set to Use Default, this is, Microsoft Dynamics GP is assigning the field value, not the integration (via source file or otherwise).

Today, I am looking at a slightly different issue, nonetheless, related.

In this occasion, the client wanted to retrieve the next voucher number before hand for a payables transaction integration and assign it to the voucher number field, but did not want to have to write their own script. In essence, they wanted to leverage whatever mechanism built already in Microsoft Dynamics GP's business logic to get the next voucher number.


Payables Setup Options - Next Voucher Number field

 Indeed, writing their own code would involve retrieving the field value and incrementing the numeric part of the string. This sometimes can be a nightmare, especially when having to deal with record collisions and users accessing the system while the integration was running.

In doing some additional reading, I realized that eConnect already included this method, so all I had to do was find the SQL Server stored procedure to get the next voucher number. That stored procedure is conveniently named taGetPMNextVoucherNumber. One down, 2 more to go!

The second challenge with eConnect stored procedures is to determine the parameters that need to be passed in, but all eConnect stored procedures are created with encryption, so editing them was not an option. However, I remembered that in SQL Server Management Studio, you have the ability to execute a stored procedure from the Management Studio UI and that this would in effect display a window with the parameters, furthermore detailing data types and whether they are input or output type parameters.

Execute Stored Procedure option (Right-click)

Execute Procedure window

The good thing about this window is you can enter values for your input parameters and click on OK, and SQL Server will automatically generate a template for executing the stored procedure, with variable declarations, types, and all. The construct looks something like this:

USE [TWO]
GO

DECLARE @return_value int,
  @O_vCNTRLNUM varchar(21),
  @O_iErrorState int

EXEC @return_value = [dbo].[taGetPMNextVoucherNumber]
  @O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT,
  @I_sCNTRLTYP = 0,
  @O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_vCNTRLNUM as N'@O_vCNTRLNUM',
  @O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value
GO

This was fantastic, because now I did not have to struggle with understanding what needed to be passed in. It so happens that the control type parameter, @I_sCNTRLTYP, requires a zero to retrieve the next voucher number. In essence, I played with the parameter value and compared to what I was seeing in the GP interface (above), so here are the parameter values accepted for control type:

0 - Next Voucher Number
1 - Next Payment Number
2 - Next Alignment Number

Two down, 1 more to go.

Finally, the rest is putting the scripts together in Integration Manager to call the stored procedure.

As a best practice,  I tend to make the connections to the database persistent throughout the integration. This assures me that connections are only opened once, and closed at the end of the integration, improving the overall performance of the integration and reducing the points of failure. So, as you can imagine, a before document or a field script aren't the places to open and close connections, as these events occur over and over, based on the number of records being integrated.

I typically open the connection in the Before Integration event script, so this is what this script looks like:

' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.
' 
' Persisting ADO connection

Const adUseClient = 3
Dim oCn

Set oCn = CreateObject("ADODB.Connection")
With oCn
  .ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
  .CursorLocation = adUseClient
End With

GPConnection.Open(oCn)
SetVariable "gblConn", oCn

Note that the connection object is stored in a global variable, gblConn, using the SetVariable statement in Integration Manager.

Once we have the connection piece sorted out, now we can focus on adding code to the Voucher Number field script to call the eConnect stored procedure, as follows:

'
' Created by Mariano Gomez, MVP
' This code is licensed under the Creative Commons 
' Attribution-NonCommercial-ShareAlike 2.5 Generic license.

' Prepare the SQL statement and retrieve the next voucher number
Const adCmdStoredProc = 4
Const adVarchar       = 200
Const adInteger       = 3
Const adParamInput    = 1
Const adParamOutput   = 2
Const PMVoucher       = 0

Set oCmd = CreateObject("ADODB.Command")
With oCmd 
 .ActiveConnection = GetVariable("gblConn")
 .CommandType = adCmdStoredProc
 .CommandText = "taGetPMNextVoucherNumber" 'the eConnect stored proc
 
 .Parameters.Append .CreateParameter ("@O_vCNTRLNUM", adVarchar, adParamOutput, 25)
 .Parameters.Append .CreateParameter ("@I_sCNTRLTYP", adInteger, adParamInput)
 .Parameters.Append .CreateParameter ("@O_iErrorState", adInteger, adParamOutput, 4)
 
 oCmd.Parameters("@I_sCNTRLTYP").Value = PMVoucher 
 .Execute 
 NextVoucher = oCmd.Parameters("@O_vCNTRLNUM").Value
 CurrentField.Value = NextVoucher
 'MsgBox NextVoucher
End With

Set oCmd = Nothing

Note how in this occasion, we are using the GetVariable function to retrieve a pointer to the connection object stored in the global variable. We then access the Parameters object to add the different parameters and set the value for the input parameter to the stored procedure.

When this script is executed within the context of the integration, it effectively returns the next voucher number for the transaction being integrated, from which you can proceed to update this information in your source system, if needed.

Note that by using standard Microsoft Dynamics GP business logic, your integration can now be supported if you need to open a support incident. Indeed another method for retrieving a document number for your transaction.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft's upcoming products available for download

A bit away from the Microsoft Dynamics GP world, but at the same time so close, here are some very good news on the upcoming release of Microsoft SQL Server "Denali", Office 2010 and SharePoint, and Internet Explorer.

Download SQL Server Code Name "Denali" CTP3

SQL Server Code Name "Denali" Community Technology Preview
(CTP3) provides the foundation for the cloud-ready information
platform, and will help customers unlock insights across the
organization and quickly build solutions that extend data across
on-premises and public cloud.

Office 2010 and SharePoint 2010 Service Pack 1 Now Available

Service Pack 1 is now available for the 2010 family of products, including Office, SharePoint, Project Server, FAST, Search Server, and Groove Server. For more on deployment, see these articles: Apply Office 2010 Service Pack 1 and Deploy Service Pack 1 for Project Server 2010. Also, download the Service Pack 1 for SharePoint Foundation 2010 and SharePoint Server 2010 White Paper.

Internet Explorer 10: Platform Preview 2

The latest preview build of Internet Explorer 10, Platform Preview 2,
is now available for download. With the second Platform Preview,
developers can start working with several site-ready HTML5
technologies.

Remember... should you decide to try any of this code, do so in a test environment.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Violation of PRIMARY KEY constraint 'PKSY60100'

Just recently, I assisted a partner with an issue they were having creating a new company in Microsoft Dynamics GP 10 - though, I supposed the same could happen with any other version. In the process of creating the company record, almost at the end of all the routines executed by Dynamics Utilities they were getting the error:

Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'

KB Article 871699  suggests the problem could be that the DYNAMICS database is associated with a database owner (dbo) other than DYNSA. After running the sp_helpdb system stored procedure, it was determined that the database owner of the DYNAMICS database (and other company databases) was indeed 'sa'. Knowing this obviously helps, and the solution is as simple as changing the database owner back to DYNSA.

The partner then ran the sp_changedbowner system stored procedure to reset the database owner to DYNSA and got the following error:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database

Having gotten this error, we proceeded to drop the DYNSA from the DYNAMICS database as follows:

USE DYNAMICS;
GO
DROP USER DYNSA;
GO

Having dropped the user from the database, we needed to re-add DYNSA as the database owner of the DYNAMICS database. This time, I decided to try the new ALTER AUTHORIZATION statement as the customer is running Microsoft SQL Server 2008 R2, as sp_changedbowner will be deprecated from SQL Server sometimes soon.

ALTER AUTHORIZATION ON DATABASE::DYNAMICS TO DYNSA;

Once we executed this command, we restarted the company creation process in Utilities and the error was no longer.

If you find yourself in a similar situation then this should definitely help.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft Dynamics GP "12" Named System Database Architecture

I received a lot of feedback from the community as a whole on the 3-part series of articles on Microsoft Dynamics GP "12" Web Client Architecture and I was pleased to know that many of you are embracing the fact that there will be a Web client version of the product and are asking numerous questions about readiness.

While these articles addressed the client portion of the solution, I really did not mention anything about changes in the database architecture and how these will impact the future deployment options. So, I have released a new architecture article on Microsoft Dynamics Community, this time addressing Microsoft Dynamics GP "12" Named System Database Architecture.

In this article I look at the named system database capabilities to be released in GP "12". This is, the ability to set any name to the traditional, hardcoded DYNAMICS database. Hope you enjoy the article and if you have any comments or questions please feel free to post back.

For convenience sake, I will be adding a new link to the architecture series.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Edits:

06/06/2011 - Changed article title to fit instructions provided by Microsoft Escalation team and current developments out of Fargo.

Read More

Upgrade Strategies for Microsoft SQL Server 2008

Back in June of last year, I talked about Microsoft SQL Server 2008 Upgrade Advisor and how it can help in identifying potential upgrade issues when moving from earlier versions of SQL Server - see Microsoft SQL Server 2008 Upgrade Advisor.

Bottom line, upgrading your database server requires careful planning. If you are still in the process of considering upgrading to SQL Server 2008 and are trying to workout whether to do an In-Place Upgrade versus a Side-by-Side Upgrade, then you will want to read Arshad Ali's article on the subject over at SQL Server Central.

Upgrade Strategies for SQL Server 2008

Arshad outlines why you should upgrade, the different strategies available, how to choose an upgrade method, putting a risk mitigation plan in place for your upgrade, the sequence of events, and much, much more, including how to read the results of the Microsoft SQL Server 2008 Upgrade Advisor tool. Go and take a look.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Read More

Microsoft SQL Server performance boosting settings for Microsoft Dynamics GP - Part 1

Part 1 of 2 - Microsoft SQL Server performance boosting settings for Microsoft Dynamics GP.

---

Disclaimer: this is not a "one-size-fits-all" recipe for improving Microsoft SQL Server performance and you will hear me say numerous times that you must consult with your Microsoft SQL Server administrator to understand how these settings may (or may not) apply to your environment.

In addition, I have recommended (and implemented) some of these changes in different customer environments on a case by case basis, but have never had to make them all at the same time, so please don't take this as a "Mariano said I must make all these changes to have my Microsoft Dynamics GP SQL Server fly" message. To the contrary, this post is intended to present some settings that may contribute to improve SQL Server performance, based on your specific environment. So here is my list:

Minimum memory per query
Increasing the value of this option can improve the performance of queries that use hashing or sorting operations, like those find in SmartList and SmartList Builder. Also, most table auto generated stored procedures -- the stored procedures prefixed with zDP, see David Musgrave's article, What do the zDP_ Auto Generated Stored Procedures do?, for more information-- will benefit from this change if your SQL Server has a lot of memory available and there are many queries running concurrently on the server.


Server Properties | Memory



SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default value for this option is 1024 KB.

Network Packet Size
Use the network packet size option to set the packet size (in bytes) used across the entire network. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. If an application does bulk copy operations, or sends or receives large amounts of text data, a packet size larger than the default may improve efficiency because it results in fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers.


Server Properties | Network Packet Size


The default packet size set by Microsoft SQL Server is 4096 bytes. Microsoft does not recommend changing the packet size unless you are certain that it will improve performance.

Default Index Fill Factor
The Default Index Fill Factor option specifies how full each index page will become. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each index page.

Server Properties | Database Settings

The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty. The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created. If the table contains read-only data (or data that very rarely changed, for example like Master tables), you can set the 'fill factor' option to 100. When the table's data modified very often, for example like MRP tables or SOP and GL transaction tables, you can decrease the 'fill factor' option to 70 percent, for example.

Recovery Interval
The 'Recovery Interval' option specifies the maximum number of minutes per database that SQL Server needs to complete its recovery procedures. The default value of this option is 0. It means that SQL Server will automatically configure this option. SQL Server issues a checkpoint using the 'Recovery Interval' option. Microsoft does not recommend changing this option in general case, but sometimes you can improve performance by changing this option. You can monitor disk-write activity on the data files, and if you see periodic spikes that send disk utilization to 100 percent, you can increase the recovery interval. In this case, Microsoft suggests setting the 'Recovery Interval' option to 5 and continuing monitoring.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More

Why shouldn't I shrink my Microsoft Dynamics GP databases?

A client recently approached me with the question of whether they should shrink their Microsoft Dynamics GP databases to reclaim hard disk space, but instead of telling you what I think, I will demonstrate some of the issues arising from shrinking your databases:

Now to what I think...I have never been a big fan of shrinking databases to reclaim hard disk space -- though, if you are running a dev environment where space is critical, then this may only be the one time. The problem arises from the way the shrink process occurs, and applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and the Auto Shrink setting in the database properties.

In summary, SQL Server goes to the end of a dabatabase file, picks up each individual page, then moves them to the first available empty space in the file. This process may reverse the order of your pages, turning perfectly defragmented indexes into perfectly fragmented ones.

So, let's take a look with a test database in one of my client's environments:

1. The first thing we will do is take a look at the stats on the GL00100 table by running the Microsoft SQL Server sys.dm_db_index_physical_stats function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'TWO'), OBJECT_ID(N'dbo.GL00100'), NULL, NULL , 'LIMITED');

The following are the results from those stats:

Original database stats

 Now, in particular, I have highlighted the Average Fragmentation in Percent and the Average Fragment Size in Pages columns. Also, note that I have executed the function in 'LIMITED' mode, which combines information from all the columns that form part of each index. So, while these fragmentation levels would indicate low defragmentation rates -- which is always desirable -- it means there is still room for improvement. So, let's see what happens after rebuilding the indexes on GL00100 for our test database, then rerunning the stats:

Stats after rebuilding indexes

As you can tell now, we have no fragmentation and our page size utilization went up - this is what we would expect after rebuilding indexes. So let's see what happens when we run the shrink process on the database:

Stats after DBCC SHRINKDATABASE

You can now tell that perfectly defragmented indexes now appear fragmented and even to a higher degree than what we started out with. These levels of fragmentation can cause serious performance issues in a production envrionment where database maintenance procedures are not properly planned and executed.

If you must reclaim hard disk space in your Microsoft Dynamics GP environment, please consult with your database administrator, but also keep in mind that storage is dirt cheap.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

Read More