Skip to content
+1-888-319-3663

COMMUNITY FORUM

Integration feed of GP data into external db

Eugene asked 4 years ago
hi,
 
I need to implement real time feed of Dynamics GP SOs and Invoices into external MySQL database and I am using SmartConnect real time datasource on tables SOP10100, SOP10200, SOP30200 and SOP30300 (i.e. SOP work header, SOP work detail, SOP history header and SOP history detail).
I am subscribing my map for CREATE and UPDATE events.
Before going live I am capturing my “inserts” and “updates” in some staging table (I am interested only in limited number of GP fields from these tables) to get an idea how much data I would be getting during regular weekday vs. weekend (when some work is done occasionally) etc.
For some strange reason, I see GP pushing down the pipe ~16000 SO insert and update events while the actual count of real SO’s touched in the system is 86. After I have restricted number of columns in my real time data source only to those I am interested in, I am still getting literally hundreds of “insert” or “update” events GP is pushing down the pipe while I know for sure users did not really create or update ANY SOs in GP at all!
This does not make any sense to me, unless GP has some internal processes touching SOP10100, SOP10200, SOP30200 and SOP30300 rows that run without user involvement and invoke triggers generated by SmartConnect on “real time SQL server data source” tables.
While I look at the actual data pushed by GP (I collected it in staging tables of my destination db) – majority of the data are simple duplicated rows with exactly same values. Why is this happened? Does only Microsoft know the answer? Besides users creating or updating SOs – are there any internal GP processes touching data in the GP tables I have mentioned? 
 
Thank you very much!
 
 
 
Eugene replied 4 years ago

Any feedback?

Eugene replied 4 years ago

Why nobody is answering?

Answers
Patrick Roth Staff answered 4 years ago
I don’t have a good answer for you on this.
If you have SQL Db triggers on those tables for update/insert and you get “hundreds of records” or even “thousands of records” then you can be assured that some process is indeed updating the records in that fashion.
 
From a GP perspective, there aren’t any processes that I can think of that would “just go through and touch all your SOP documents – especially the History ones”
 
But as you work with the SOP documents such as posting them – i could perhaps expect multiple touches across the SOP10100 and SOP10200 as posting a line item might update a status in the SOP10100 table.  And I know that GP also touches the SOP10100 and updates a status field with a “how far have i gotten” so that posting a SOP Invoice for example would update that status several times – maybe 4 to 6 times each possibly.
 
And what would make matters worse for you is that the SOP10100 table has a GP trigger on it – zDT_SOP10100U which updates the DEX_ROW_TS on the SOP10100 table.  So that might cause your other trigger to run multiple times (but may be filtered out on your trigger).
 
So yes i would expect multiple triggers hitting your data – but i wouldn’t have an explanation for 86 actual updates to about ~16000 events.
 
You could maybe add to your trigger a way to get the statement that updated GP
 
https://dba.stackexchange.com/questions/46508/sql-server-audit-query-that-fires-a-trigger
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/11/tsql2sday-using-sys-dm-exec-sql-text-to-get-the-calling-statement.aspx
 
You could also get the user and session to know who did the update – and i’m assuming if the applicationmame is set (GP does not) then you could get the application.
 
Some of this information might be useful to piece together what is doing all the updates. 
However even you find out what is causing all the updates – most likely they are “legit” and something is doing that for a valid reason.  And if so, you’d still have to deal with the consequences of that which is many many records. 
 
Eugene replied 4 years ago

There is more critical issue I am running into – besides many rows generated by my real time data sources on SOP10100 and SOP30200 (which is the problem I can get around) I something worse. When GP orders are invoiced and new rows are written into SOP30200, not every new row invokes my map!
My map is very simple – it is hooked up to SOP30200 defined as real time data source and it is bound to stored proc which writes data into my staging table I would expect every new INSERT into SOP30200 which I assume occurs every time order is invoiced to trigger my map and as a result data would feed down to my staging table. But this is not the case!
It does work but not every time and I don’t understand why. I tried to compare the data on invoiced SO that fed down through the map vs. the ones that did not – and I don’t find any pattern. At the same time I know for sure every order I have checked got invoiced in GP successfully and I see it in SOP30200 table!

I don’t seem to be having same issue with SOP10100 GP table.

What is going on with this?
It is much more critical for us that large number of rows generated by the map.


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