Mysterious eConnect Error: The stored procedure does not exist. Watch your schemas!

I recently upgraded a Dynamics GP Visual Studio Tools AddIn from GP 9 from GP 2010.  The AddIn reviews some GP transactions, performs some calculations, and then inserts new payroll transactions using eConnect.

The upgrade was straightforward and worked fine on my development server.  It installed smoothly on the client server and looked like it was working...right up to the point where the following eConnect error occurred:

The stored procedure 'taCreatePayrollBatchHeaderInsert' doesn't exist.

In this particular case, the AddIn was inserting an entire payroll batch, but the specific stored procedure in the error could have been any eConnect procedure.

I've never seen that type of error before, so I started with the obvious--I checked the SQL database to see if the procedure existed.  It definitely did exist in the database.

I then thought that maybe it was a permission issue, so I confirmed that the eConnect domain user was setup as a SQL Login, and that an eConnect user was properly setup for the company database.  I even checked the DYNGRP role to make sure that the stored procedure was listed with EXEC permissions.

Everything looked fine.

I did a little research on the error, and saw a post that recommended checking the database connection string.  If the database was not specified properly, and the import was somehow referencing the master database or even DYNAMICS, naturally that would cause this type of error.  So I added some debugging code, but the connection string looked fine--the proper GP company database was being referenced.

I was running out of options, so I dug out the Direct Document Sender, a tool used by GP support to troubleshoot eConnect issues.  It allows you to specify the database connection info, select an XML file containing the eConnect data to be imported, and then it sends the XML document off to eConnect.  This helps to rule out any bugs in your custom integration.

Interestingly, the Direct Document Sender received the same error message indicating that the stored procedure did not exist.  Although it helped to rule out the VS Tools AddIn and its eConnect code, and it helped me realize that there was a lower level issue with the client's environment, I still didn't have any specific clues as to the real cause.  I was thinking that maybe it was a problem with the eConnect procedures in the client's databases, or that perhaps something did not upgrade properly from GP 9 to GP 2010.

As a long shot, I ran the dbmaintenance.exe utility in the Dynamics GP application directory to recreate the Functions and Stored Procedures on the company database.  That completed successfully, but when I ran the integration and the Direct Document Sender, both still received the same error.

As a final test, I wanted to get a baseline to test against in the client's environment, so I created the TWO / Fabrikam test company database.  GP Utilities setup the company, and I then went into SQL Server Management Studio and added the eConnect login as a user on the TWO database.

To perform my test with the Direct Document Sender (which uses Windows authentication to access the SQL Server), I logged into the client's server as the eConnect domain user, just to make sure that no other user account variables were involved.  I launched the Sender and started to setup the connection string, but when I went to select the database, TWO was not listed.  I verified everything several more times, but TWO didn't show up.

I then logged out of the server and logged back in as the Administrator.  When I did that, the TWO database did show up.  Puzzled, I checked and rechecked the eConnect user for the TWO database, but it looked fine.  For some reason I couldn't get TWO to appear in the database list.

Since this didn't make any sense, I went back to the SQL Server Login window for the eConnect user and checked the settings for at least the fifth time.  While I was staring at the window, something caught my attention.

I scrolled to the right and saw that the user record for the TWO database had a Default Schema of dbo, but the records for the company databases had the eConnect user listed as the Default Schema.

The light bulb instantly came on and it all made sense.  Well almost all.

If you aren't familiar with SQL Server Schemas, you should definitely read about them just enough to understand what they are.   They are not used by GP, so you don't need to be an expert; however, because they are not used by GP, if someone happens to use them in a GP database, it will likely cause problems, so you will need to know just enough to identify, understand, and resolve the issue.

Because the eConnect SQL user was assigned a Default Schema other than dbo, when eConnect logged into the database and tried to execute the payroll stored procedure, it literally didn't exist.  In fact nothing existed--no tables, no stored procedures, nothing.

I tried changing the Default Schema for the user back to dbo, but for some reason that didn't solve the problem.  I had to delete the eConnect user and eConnect schema completely from all databases, and delete the eConnect login for good measure, and then recreate the login and users with the proper dbo default schema.

Once I did that, the TWO database showed up in Direct Document Sender, and sure enough, eConnect worked just fine.  The AddIn immediately started working properly and was able to import payroll batches.

I'm not entirely sure how the eConnect schema got created on the SQL Server or how it was assigned as the Default Schema for the eConnect user on the company databases.  And I don't understand why changing the Default Schema back to dbo didn't fix the problem.  But in the end, it was easy enough to remove the users completely and recreate them.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com