"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/

New Article on MSDynamicsWorld: When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?

It’s been a couple months since I’ve written a business-driven column over at MSDynamicsWorld. This time, my new article looks into an all too common issue: upgrades from older, customized versions of Microsoft Dynamics GP systems.

By “older”, I am not referring to – believe it or not – version 7.0 or 7.5, even though those are very outdated releases. I’m referring to you, still running version C/S+ 3.17, or 4.0 or even GPA. I know you have milked the product as much as you can and you feel it still does what you need it to do, but you are missing out on a unique opportunity to bring your systems up to date and take advantage of the wide array of ISV solutions that will once and for all get you off the development threadmill.

Since it’s Friday, the article is meant to serve as food for thought.

When It’s Time to Upgrade an Outdated, Customized Microsoft Dynamics GP System…Who You Gonna Call?

Leave your comments and feel free to discuss with your peers.

Until next post!

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

Default Printer not ‘sticking’ when running Microsoft Dynamics GP in Terminal Services RemoteApp

A bit of theory…

RemoteApp programs are programs that are accessed remotely through Terminal Services and appear as if they are running on the end user’s local computer. Users can run RemoteApp programs side by side with their local programs. A user can minimize, maximize, and resize the program window, and can easily start multiple programs at the same time. If a user is running more than one RemoteApp program on the same terminal server, the RemoteApp programs will share the same Terminal Services session.

The following Microsoft TechNet article explains in more detail:

Terminal Services RemoteApp (TS RemoteApp)

Background

Microsoft Dynamics GP (versions 10.0 and 2010), is currently supported in a Terminal Server RemoteApp environment, but a number of my clients and forum users have reported in numerous occasions that the Default Printer settings are not ‘sticking’ or simply saving when loging out of the application (which also closes the RemoteApp session) and returning to it.

Print Setup window

The Solution

Puzzled by this, I began doing some digging and found out that the default printer “problem” is actually not a problem, but rather the way RemoteApp decides how the session disconnection will occur. Playing along with the disconnection, there was a new Terminal Server group policy setting that was introduced to control time limits for disconnection and there lies the dirty little secret.

If you are experiencing the issue I described above, please take a look at the following article by the Remote Desktop Services (Terminal Services) Team Blog:

Terminal Services RemoteApp™ Session Termination Logic
http://blogs.msdn.com/b/rds/archive/2007/09/28/terminal-services-remoteapp-session-termination-logic.aspx

The article unveils the steps required to change this behavior and make your Microsoft Dynamics GP printer ‘stick’.

Also, if you are using Named Printers with Microsoft Dynamics GP in a Terminal Services RemoteApp environment, take a look at the following articles over at Developing for Dynamics GP:

Using Named Printers with Terminal Server
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/08/15/using-named-printers-with-terminal-server.aspx

Named Printers application default printer selections not “sticking”
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/06/24/named-printers-application-default-printer-selections-not-quot-sticking-quot.aspx

Troubleshooting Named Printers Issues
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/05/26/troubleshooting-named-printers-issues.aspx

Please let me know with your comments if any of the above recommendations by the Remote Desktop Services team worked for you.

Until next post!

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

Cannot insert the value NULL into column ‘CONTACT’ error when clicking on Items List in Navigation Pane

Moving on from my previous article on a similar subject – see Cannot insert the value NULL into column ‘BASEUOFM’ error when clicking on Items List in Navigation Pane, I recently came across this error, Cannot insert the value NULL into column ‘CONTACT’ when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2.

All Purchasing Transactions list error – Purchasing Navigation List

The name of the global temp table – in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that does not exist in the Vendor Master table (PM00200), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM POP30300 WHERE VENDORID NOT IN (SELECT VENDORID FROM PM00200);

Once you have identified the record(s) causing the failure, you can use the Vendor Maintenance window to add the missing vendor or further study the issue to remove the offending receipts if necessary:

Vendor Maintenance window

Patrick Roth, Escalation Engineer with Microsoft and blogger at Developing for Dynamics GP, provides a full explanation of his troubleshooting method for this error at the Partner Online Technical Community forum:

GP2010 Purchasing List Error – Partner Online Technical Community forum

Until next post!

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

Integration Manager for Microsoft Dynamics GP 2010 hangs when running an integration – Follow up

After some additional testing to find the route cause of Integration Manager 2010 hanging, it seems the issue has been narrowed down to a glitch in one of the slides cycled by the Connect gadget on the home page, and not an issue with the Microsoft .NET Framework 3.5 as originally thought.

For those of you who follow closely on the community news through the Connect gadget, you may have noticed the service being down since last Friday – around the same time Microsoft Support started receiving reports on the issue. Sources tell me that this was done as part of the standard testing protocol to discart new functionality causing the problem.

The slide causing the issue was removed this afternoon and the Connect service has been restored. If you were getting prepared to apply a new hotfix or service pack, you will be glad to know there may be no need to do so.

Stay tuned for further updates.

Until next post!

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

Integration Manager for Microsoft Dynamics GP 2010 hangs when running an integration

The forums are hot with users and partners reporting an issue with Integration Manager for Microsoft Dynamics GP 2010 hanging when running an integration. Everyone seems to agree the issue started sometimes this past Friday, August 5, 2011, with most users reporting a normal behavior and the ability to run trouble free integrations prior to that date.

This issue has been written up under the following hot topic article

Integration Manager for Microsoft Dynamics GP 2010 is Unresponsive at the Beginning or End of an Integration

According to a Microsoft representative with the Escalation Engineering team:

Our development team has identified the issue to be the result of a change in .Net Framework 3.5. At this point our development team is working on creating a new Integration Manager build which will then undergo testing to verify the build and that the issue has been resolved. As soon as we have a new IM build that resolves this issue, we will post the install on CustomerSource/PartnerSource so you can download the update and begin updating your IM installations to correct this issue.

Microsoft .NET Framework 3.5 was released in 2,007 and has since undergone a Service Pack 1 release in November of 2,008, and some security fixes just in July of this year.

Other releases of Integration Manager appear not to be affected by this as they use earlier versions of the .NET Framework.

Until next post!

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

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client’s identity)

This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.

As a result, I created the following script:

-- Created by Mariano Gomez, MVP

--  This code is licensed under the Creative Commons 

--  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

use DYNAMICS;

go



DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);

DECLARE @sqlStmt varchar(255);



DECLARE c_reportsecurity CURSOR FOR 

	SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a

		LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)

	WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')

	ORDER BY a.USERID;



OPEN c_reportsecurity;

FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;



WHILE @@FETCH_STATUS = 0

BEGIN

	SELECT @ssrsrole = 

		CASE 

			WHEN @securityroleid = 'ACCOUNTING MANAGER*      ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'AP CLERK*                ' THEN 'rpt_accounts payable coordinator'

			WHEN @securityroleid = 'AR CLERK*                ' THEN 'rpt_accounts receivable coordinator'

			WHEN @securityroleid = 'BOOKKEEPER*              ' THEN 'rpt_bookkeeper'

			WHEN @securityroleid = 'CA AGENT*                ' THEN ''

			WHEN @securityroleid = 'CA MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CA STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CERTIFIED ACCOUNTANT*    ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'CL AGENT*                ' THEN ''

			WHEN @securityroleid = 'CL DISPATCHER*           ' THEN 'rpt_dispatcher'

			WHEN @securityroleid = 'CL MANAGER*              ' THEN ''

			WHEN @securityroleid = 'CL STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'CUSTOMER SERVICE REP*    ' THEN 'rpt_customer service rep'

			WHEN @securityroleid = 'DP MANAGER*              ' THEN ''

			WHEN @securityroleid = 'DP STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'DP TECHNICIAN*           ' THEN ''

			WHEN @securityroleid = 'FA MANAGER*              ' THEN 'rpt_accounting manager'

			WHEN @securityroleid = 'FA STAKEHOLDER*          ' THEN 'rpt_certified accountant'

			WHEN @securityroleid = 'IT OPERATIONS MANAGER*   ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER ADMIN       ' THEN ''

			WHEN @securityroleid = 'MBS DEBUGGER USER        ' THEN ''

			WHEN @securityroleid = 'OPERATIONS MANAGER*      ' THEN 'rpt_operations manager'

			WHEN @securityroleid = 'ORDER PROCESSOR*         ' THEN 'rpt_order processor'

			WHEN @securityroleid = 'PAYROLL CLERK*           ' THEN 'rpt_payroll'

			WHEN @securityroleid = 'PM AGENT*                ' THEN ''

			WHEN @securityroleid = 'PM MANAGER*              ' THEN ''

			WHEN @securityroleid = 'PM STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'POWERUSER                ' THEN 'rpt_power user'

			WHEN @securityroleid = 'PURCHASING AGENT*        ' THEN 'rpt_purchasing agent'

			WHEN @securityroleid = 'PURCHASING MANAGER*      ' THEN 'rpt_purchasing manager'

			WHEN @securityroleid = 'RT AGENT*                ' THEN ''

			WHEN @securityroleid = 'RT MANAGER*              ' THEN ''

			WHEN @securityroleid = 'RT STAKEHOLDER*          ' THEN ''

			WHEN @securityroleid = 'SHIPPING AND RECEIVING*  ' THEN 'rpt_shipping and receiving'

			WHEN @securityroleid = 'WAREHOUSE MANAGER*       ' THEN 'rpt_warehouse manager'

			WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS*  ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''

			WHEN @securityroleid = 'WENNSOFT SMS SETUP*      ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTANT*         ' THEN ''

			WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''

			WHEN @securityroleid = 'WSJC ADMIN*              ' THEN ''

			WHEN @securityroleid = 'WSJC BILLING CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSJC POWERUSER*          ' THEN ''

			WHEN @securityroleid = 'WSJC PROJECT MANAGER*    ' THEN ''

			WHEN @securityroleid = 'WSTT PAYROLL CLERK*      ' THEN ''

			WHEN @securityroleid = 'WSTT POWERUSER*          ' THEN ''

		END

	

	IF (@ssrsRole <> '') 

	BEGIN

		SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');

		EXEC(@sqlStmt);

	END

	FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

END



CLOSE c_reportsecurity;

DEALLOCATE c_reportsecurity;

The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time… phew!

Until next post!

MG.-

Mariano Gomez, MVP

IntellPartners, LLC

http://www.IntellPartners.com/

Cannot insert the value NULL into column ‘BASEUOFM’ error when clicking on Items List in Navigation Pane

Just recently a few partners began reporting getting the error Cannot insert the value NULL into column ‘BASEUOFM’ when clicking on the Items list under the Inventory Navigation Pane option.




Items list error

  The name of the global temp table – in this case, tempdb.dbo.##0251007 – varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2.

Upon further review, the issue is due to bad data in the Unit of Measure Schedule (UOMSCHDL) column in the Item Master table (IV00101). In summary, if you have an item record with a blank Unit of Measure Schedule or a Unit of Measure Schedule that does not exist in the Unit of Schedule Master table (IV40201), it will cause the Items list to fail with the error above.

The following query should help in identifying the offending record(s):

' Created by Mariano Gomez, MVP

'  This code is licensed under the Creative Commons 

'  Attribution-NonCommercial-ShareAlike 2.5 Generic license.

SELECT * FROM IV00101 WHERE UOMSCHDL NOT IN (SELECT UOMSCHDL FROM IV40201);

Once you have identified the record(s) causing the failure, you can use the Item Maintenance window to correct the problem:

Item Maintenance window

Until next post!

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