Listing all eConnect Modified PRE and POST stored procedures

As of recent, I was ask of a simple method to determine which eConnect PRE and POST stored procedures had been modified. After all, with a concrete list, you are able to script just the affected ones prior to a major version upgrade or a feature pack installation.

A modified stored procedure could be simply qualified as one on which you have ran an ALTER PROCEDURE statement or have edited it using SQL Server Management Studio. With that said, the following query may not be failsafe, but provides a very accurate way of getting a list.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT name, create_date, modify_date 
FROM sys.objects 
WHERE (type = 'P') AND (name LIKE 'ta%Pre' or name LIKE 'ta%Post') AND (create_date <> modify_date)

Enjoy!

Until next post!

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

Read More

"Object Reference Not Set" error when running Integration Manager with eConnect Adapter

I have seen a number of forum posts around this subject and have even received a few calls for help in troubleshooting the issue. In the occassions I have assisted someone, I have noticed that most of the time the developer or consultant was using an event or field script of some kind, which almost always attempts to get some information from Microsoft Dynamics GP.

So, in an attempt to reproduce the problem, I have recreated the following VBScript based on a recent case:

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.



Dim objConn, objRec, cmd, sJE

 



set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "database=" & GPConnection.GPConnInterCompanyID

GPConnection.Open(objConn) 

 



Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = objConn

 

cmdString = "SELECT NJRNLENT FROM GL40000;" 

Set objRec = objConn.Execute(cmdString)



if Not objRec.Bof and Not objRec.Eof then

  objRec.MoveFirst

  CurrentField = objrec.fields(0).value

end if

  

'Close recordset when finished

Call objRec.Close

  

'Close connection when finished

Call objConn.Close

 

Set cmd = Nothing

Set objConn = Nothing

NOTE: This script purposefully contains errors and does not follow best practices. It was recreated to illustrate the issue on the subject.

In summary, the above script was added by the consultant to retrieve the next journal number for a GL Transaction integration with the eConnect Adapter. The consultant reported the script working on and off on the server and not working on the workstations. However, in each case the error reported by Integration Manager is as follows:

Opening source query...

Establishing source record count...

Beginning integration...



DOC 1 ERROR: Error Executing Script 'GLTransaction.Journal Entry#' Line 9: - 

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Integration Failed

Integration Results

    1 documents were read from the source query.

    1 documents were attempted:

        0 integrated without warnings.

        0 integrated with warnings.

        1 failed to integrate.

The error indicates the is a problem with the data source name not being found, which leads to an object reference problem when the connection is attempted. But why would this code work on the server at times and not work on the client? Then it hit me!

The GPConnection object retrieves the connection and login information for the user currently signed on to Microsoft Dynamics GP... and therein lies the issue! The GPConnection object actually requires the Microsoft Dynamics GP user interface to be active for the object to retrieve the connection information, which is typically not the case for eConnect Adapter-based integrations.

As a side note, the times the integration did work, the user interface HAD to be active, but this was not apparent to the consultant.

So, how can we adjust this integration to follow best practices and work without the Microsoft Dynamics GP user interface having to be active?

The answer is relatively simple. The above code will need to switch out the way it obtains the connection string for an actual (as in hardcoded) connection string.

'

objConn.ConnectionString = "Provider=SQLNCLI10;Server=yourSQLServerName;_

Database=YourCompanyDB; Trusted_Connection=yes;" 

Because the script uses a trusted connection to the database (a best practice), it is advisable that proper permissions be granted to the user's domain account on SQL Server in order for the integration to be successful. The domain account will also need to be added to the DYNGRP role. What many customers have done is created specific domain accounts to execute eConnect integrations under a trusted connection. This further limits the exposure to security breaches.

For a final look at a technique to implement the above script, see the following article on this site:

Integration Manager: Integrating journal entries with Analytical Accounting Information

Hope you found this post useful.

Until next post!

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

Read More

IM - Cannot Open Database error when running eConnect adapter integration

Here comes one of those puzzling errors that can have you spinning your wheels for a bit until you resolve it. Just recently, I was helping a client building a Fixed Assets integration. The requirement called for importing a number of asset records and have a method that could be reused as new assets are added to the organization's 15 different locations... that's the easy part!

We were getting ready to run our first integration when the dreadful error came up:

ERROR: System.Data.SqlClient.SqlError: Cannot open database "XYZAB" requested by the login. The login failed"

Of course, here comes the troubleshooting aspect of the process. With the client running eConnect 10, there are a few places to look for issues that may trigger this error:

Component Services:

The most likely cause of this error message is a problem with the eConnect COM+ configuration. Open the Component Services by clicking Start > Run and type in dcomcnfg.

Expand Component Services > Computers > My Computer > COM+ Applications. Right click on "eConnect 10 for Microsoft Dynamics GP" and choose Start. If you receive an error, that means the COM+ component is not configured properly.

To configure, right-click and choose Properties. Click on the Identity tab and make sure that the domain user account configured here is setup in SQL server and is at least a member of the DYNGRP role for the company and dynamics databases.

If you have System Account selected, select This User instead and enter your Domain\User account and password.

SQL Sercurity

Open Microsoft SQL Server Management Studio (SSMS) and in Security verify that you have a SQL user that is the same Domain\User and the user is part of the DYNGRP role.

That should do!

Until next post!

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

Read More

Microsoft Dynamics GP "12" Multi-tenant Services Architecture

Finally, my new post on Microsoft Dynamics GP "12" Multi-tenant Services Architecture has been released on the Community site under my In My Humble Opinion column. After much debating with my buddy Aaron Donat (thanks Aaron for your patience!) on the previous article I released under the same title, it was deemed that that article should have been changed to reflect the Named System Databases architecture change that the Development team in Fargo was working on.

This new article highlights the changes that the Microsoft Dynamics GP web client, web services, eConnect, and Integration Manager will undergo to support various customer deployments under one single application instance. Now, this is true optimization! Hosting partners rejoice!

Until next post!

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

Read More

IM: Integrating Timesheet Line Items not associated to a project

Just recently, I was following a thread on the Microsoft Dynamics GP Partner Forum where the partner was attempting to integrate timesheets using the eConnect Adapter for Integration Manager, but kept getting the error:

DOC 1 ERROR: eConnect The 'NONE' start tag on line 1 does not match the end tag of 'PAPROJNUMBER'. Line 1, position 2316.

When entering timesheets in the Timesheet Entry window, you can enter a line item that isn’t for a specific project by pressing TAB to default the project number to "". This instructs Microsoft Dynamics GP Project Accounting that there will be no project associated to the timesheet line.

As logic would have you believe, if you are integrating a timesheet line that is not associated to a project, it would be enough to pass the same "" string value to the timesheet line and things should be fine, right? Wrong! Passing in the "" tag caused the timesheet integration to fail with the error above.

Now to the error...

The error clearly indicates that there is a problem with an XML tag - presumably when Integration Manager serializes the source data into its XML representation.

Since IM has to marshall the source data (also known as serialization), the "" string value is being interpreted and converted to an XML tag within the serialized document. This will cause the XML document to be inaccurate. This is a representation of what I assume is happening after the conversion:


<taPATimeSheetLineInsert>
<PATSTYP>1</PATSTYP>
.
.
<PAPROJNUMBER><NONE></PAPROJNUMBER>
.
.
</taPATimeSheetLineInsert>

As shown above, the "" string value is interpreted as a new tag rather than the actual string value causing an open tag in the XML value, hence causing the integration fail. The partner confirmed that by calling eConnect's taPATimeSheetLineInsert stored procedure directly and passing in the "" string value in the project number field directly, that eConnect would process the document without any issues.

This is actually a good thing!

Furthermore, Microsoft has identified this to be an issue with the eConnect Adapter for Integration Manager and has scheduled this problem to be resolved in Service Pack 3 - no, it did not make the cut for 2010 R2/SP2.

However, the workaround is as follows:

1. Pass/Map a value of NONE to the Project Number field for the timesheet line in Integration Manager. If your source data includes the actual tags (< and >) you can use a simple field script to remove them.

2. Edit the eConnect taPATimeSheetLineInsertPRE stored procedure to include the following T-SQL code:

-- Created by Mariano Gomez, MVP

-- This code is licensed under the Creative Commons 

-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.

ALTER PROCEDURE dbo.taPATimeSheetLineInsertPRE

.

.

AS

IF UPPER(@I_vPAPROJNUMBER) = 'NONE'

  BEGIN

    SELECT @I_vPAPROJNUMBER = '<NONE>';

  END

Since the PRE stored procedure executes before the rest of the taPATimeSheetLineInsert code, the proper value will be passed in to the timesheet line, hence preventing the error.

Until next post!

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

Read More

Custom Serialization Assemblies for eConnect 2010

Yesterday it was Bryan Prince demonstrating in-memory XML serialization, today is Chris Roehrich over at Developing for Dynamics GP. Chris shows a technique to create a Custom Serialization Assembly for eConnect outside of the method exposed in the eConnect Programmer's Guide.

Please be sure to visit Developing for Dynamics GP to read Chris' article.


Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

In-Memory XML Serialization with eConnect 10

Over at MBS Guru, my friend Bryan Prince demonstrates a technique to perform in-memory XML serialization when working with eConnect. Bryan's technique is very helpful especially when working in environments where disk access and/or disk permissions can become an issue.

If you ever needed a cool piece of code for your eConnect projects, this is it! On a personal note...I had a chance to work on a project briefly with Bryan and I won't be surprised he will be publishing some other cool life saving techniques he used at our client.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

Recovering missing eConnect stored procedures

A frequently asked question among developers is, what to do when eConnect stored procedures go missing from a Microsoft Dynamics GP company database.
As for a background, eConnect stored procedures are typically prefixed by the letters "ta", and are encrypted at the database level.

Object Explorer view of eConnect stored procedures

Because of their encryption, the CREATE function cannot be scripted as with unencrypted stored procedures, rendering the following error message:

But because things can go wrong (and may go wrong), what can you do to recover a missing eConnect stored procedure? Fortunately, the answer lays in a previous topic I covered a few months aback: The Microsoft Dynamics GP Database Maintenance Utility. The Database Maintenance Utility can also recover missing eConnect stored procedures and any other stored procedure that matter. Because eConnect is not a listed product in the DYNAMICS.SET file, your only choice of product is Microsoft Dynamics GP.

As with all maintenance operations, be sure to backup your database before performing this action. Safe eConnect stored procedures recovery!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More

eConnect Integration Service for Microsoft Dynamics GP 2010

One of the biggest improvements in eConnect 2010 is the addition of the new eConnect Integration Service. The eConnect installer now creates a new Windows Service application named eConnect for Microsoft Dynamics GP 2010 integration Service. The eConnect Integration Service is a Windows Communication Foundation (WCF) service that replaces the (very unstable) eConnect COM+ object available in previous versions of eConnect.

The eConnect Integration Service supports the operations of the eConnect .NET assemblies, the BizTalk adapter, and MSMQ interfaces. In addition, you can use the service directly from an application that adds a service reference to the eConnect Integration Service.




If you add a service reference, you do not need to add the Microsoft.Dynamics.GP.eConnect assembly and namespace to your development project.



To add the eConnect Integration Service to an application, you must first add a service reference to the Visual Studio project for that application.

To add a service reference to a Visual Studio project, the properties for the project must specify the target framework as .NET Framework 3.5. The following is a typical URL for the eConnect Integration service.

net.pipe://localhost/Microsoft/Dynamics/GP/eConnect/


The interface provided by the service reference includes the same methods you find in the Microsoft.Dynamics.GP.eConnect assembly and namespace. Before you use a service reference to access eConnect Integration Service, you should become familiar with WCF development concepts.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Read More