Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect vs Multicompanies – Not able to integrate

Myblueskiez asked 7 years ago
Our company has been having this issue since we bought SmartConnect.  We have multi-companies.  We have a main company where majority of our integrations are being performed and we have been working to get SmartConnect to integrate in our other companies with no success.  We have serveral integrations that are working utilzing the GP Rolling Columns to integrate journal entries into our main company.    We duplicated one of those integrations and linked it to a new company and constantly, we get the message:

Errir in "MsGpDestination'
Line 'taGLTransactionHeaderInsert____1'
Parameter 'Journal entry number'
Parameter set value event
Could not get next number ofr Journal entry number:  Could not retrieve the next number in the sequence

Our partner/vendor has not been able to figure out the issue.  We have been able to manually type in a local contact for the journal number and the integration loads the journal entry but setting up a GP rolling column, it will not integrate.  I can use a custom rolling column and it will integrate but again, GP rolling column, I can't seem to get it to work.

–I have already used a working integration/map, used the duplicate fuction to create our map and the error generates

–I have already created the map from "scratch" from the start by setting up a brand new GP rolling column and the error generates

–I have already ran system maintenance several times and rebooted the server, also stop/started econnect services and the error still generates


I can only assume that smartconnect can integrate into multiple companies since I have the ability to select from my different companies but how can I get this specific map to read the next GP journal entry number and integrate using that number?

Can you please assist? 
Najera replied 7 years ago

Myblueskiez – Are both companies posting setup identical?

patrick replied 7 years ago

I would assume there is a SQL error when running the stored procedure.

the E1 procedure would call the eConnect proc in order to get the next JE number.  This must be failing for the other companies.

Do a SQL Profile trace on the SQL Server.  Make sure that you mark the "Errors and Warnings" because you want to see any SQL errors that are happening.

The next steps to fix depends on the error.

myblueskiez replied 7 years ago

Both companies posting setup are identical.

myblueskiez replied 7 years ago

When I ran the trace, I saw the errors:

Could not find stored procedure 'MDCTY.dbo.E1_SC_GetNextNumber'.
Error number: 2812

I don't understand why the it can't find the stored procedure get the next journal entry number when I can select a map/integration thats linked to our main company and it assigns the next journal entry number.

Answers
Best Answer
patrick answered 7 years ago
well, based on the error you are getting – in the “MDCTY” database you don’t have that stored procedure. You should, but you don’t. I’d start then looking in SQL and verify that you have this proc. If not, then you should be able to script it out from another db where it exists and then create it in the MDCTY database.
@GP_Beat replied 5 years ago

We found out by the hard way that when you test your smartconnect maps on a test system that was refreshed from a Live system which don’t necessarely carry the same version of SC or no SC at all, that you have to redo the setup of all the SP’s on the GP companies.. The dumb part of this is that is SC Maintenance, you can’t select the company that was refreshed.. it has to run across all GP Companies.. thus taking a very long time.. BTW, how long is that process supposed to last ? we had ours run for over an hour before we decided to kill SC completely, since it was apparent that the stored procs had all be re-created in the SQL DB’s.. this is very frustrating not being able to selectively re-install the SC objects for a selected company.

Beat Bucher replied 3 years ago

So Here I sit two and half year later and this issue still hasn’t been fixed by eOne in the latest SC 20.18…
When adding a new company to the GP instance, despite re-running the SC System Maintenance, the missing SQL objects, like E1_xxx SP’s don’t get created.. On top of that, if you have 50 companies, this is just plain stupid, as you don’t your entire system being bogged down by re-creating SQL ojbects that are working perfectly fine.
Please add a way to select which companies you want to run the system maintenance against.

@GP_Beat answered 3 years ago
It took me finally some research into SQL security to understand why a new company would not receive all the E1 stored procs in the company Database.. Permissions !
If you check the GP Connector setup in SC and notice which user account is used there, you may realize by checking the SQL security that this user account doesn’t have all the permissions to create new SQL objects.. 
Most of the time, IT admins set up SmartConnect with the least possible privileges to secure their SQL servers and not leave some 3rd-party software with full permissions to access their databases. 
So, all it takes is to assign the SmartConnect user (or whatever you called it in SQL) to the proper SQL security role for that company DB : db_ddladmin
This forum discussion explains why this all it needs, no reason to assign that user full db_owner rights to create those missing objects.
This is something I couldn’t find any hint about in the eOne SC documentation, so you wanna sure to put this on your SC setup checklist.

If you would like to submit an answer or comment, please sign in to the eOne portal.