Skip to content
+1-888-319-3663

COMMUNITY FORUM

Map with real time MS SQL data source out of Dynamics GP bound to the stored procedure

Eugene asked 5 years ago
Hi,
 
I hope you can help me out with this problem.
I have SmartConnet map with Real Time MS SQL data source (Item Quantity Master Dynamics GP table) and MS SQL stored procedure as destination.
My objective is to be able to feed updates to GP Item Quantity Master to my MySQL database server. In order to do that, I have setup MySQL db as Linked Server in GP MS SQL Server.
Also, I have stored procedure in MySQL that writes data to my destination table. In my MS SQL stored proc (map destination) I call this MySQL proc (using linked server syntax call) thus passing data from the SmartConnect map thru MS SQL stored proc to the final MySQL proc.
I realize it is not a simplest of setups, but my requirement is to get GP data flowing real time to MySQL database.
Individually, all parts of my flow work – I can invoke my MS SQL stored proc and see data updated in MySQL db. I also have my Real Time data source registered and I see triggers created on IV00102 table in GP (Item Qty Master). I am able to update GP Item Qty Master table directly or via creating sales order and I see how my map gets invoked and runs without any error.
The issue, however, is when I get no errors and map seems to return success, my update do not get down to the MySQL destination table.
I have tried debug level logging on my map but did not see anything in it. Also, I have added some logging into my MS SQL stored proc Рsimply writing input parameters into log table so I can be sure map works. That also seems to be ok, but the end result Рdata update in MySQL table is not happening.
 
Any advice on debugging or troubleshooting the problem is greatly appreciated.
 
Thank you!
 
 
Eugene replied 5 years ago

Apologies about the long post. Please disregard my question. I have got this working. The issue was in the data type of one of the parameters being char instead of varchar and hence my code was not finding the rows in the table, but that data was flowing all the way through to MySQL.

Patrick Roth Staff replied 5 years ago

Good to hear that you have it working.

My only thought would be since we know the map is running and that the SQL proc is running (since you said you wrote to the log table) was that we had a query/update issue. Looks like that was it.

Eugene replied 5 years ago

hi Patrick,
Yes, I have added logging into MySQL procedure as well after adding logging into MS SQL proc and this is how I have discovered what was going on. Thank you!

However, I have more general follow up question about using SmartConnect real time data sources bound to maps as means to replicate Dynamics GP data to other data storage/databases.

Ultimately “under the hood”, this approach is based on triggers created on GP tables and if SmartConnect service is down for any reason and map is not running, the triggers will be failing and the data changes made to GP tables will be lost. I.e. if my SmartConnect is not running for any reason, there is no way to “replay” changes made to GP tables my SmartConnect real time data sources “monitor”. Architecturally, I see this as serious limitation for using SmartConnect as means to reliably bring GP data to another database.

Conceptually, this may be my only choice unfortunately, since many GP tables simply do not have Dexterity last updated timestamp (at least some master tables like Item Qty Master), so the more basic approach to look for anything in any GP tables that was changed since last run based on Dexterity Timestamp column would not work for a lot of GP data.

I think “replay” functionality is a must for SmartConnect real time data sources, i.e. something like persistent queue that could hold any data generated by triggers while SmartConnect is down and map is not running. So at least when SmartConnect service is back up – all queued changes would flow down the pipe. This does not have to be based on messaging technology, but can be implemented using files.
This may be a feature request I suppose – it should make SmartConnect a “bullet proof” option for someone with my requirements.

Lorren Zemke Staff replied 5 years ago

Eugene, The nature of Real-Time event means the source is what triggers the event and not technically anything in SmartConnect. SmartConnect cannot retry an event that happens in another application such as SQL, Dynamics GP or CRM or Salesforce. If the data doesn’t get to the SmartConnect Web Service, there is no method to have SmartConnect know about the data that was attempted to be sent. If the map does run but errors, then you can turn on the Log Error Data Source Option for the map and it will log the data sent so the map could be executed again with that data but that data has to get to SmartConnect for that to happen.
If you feel the SmartConnect Web Service would not be up and running for whatever reason, then your option is to switch to Changes Only Data Source to track changes and run the maps on a scheduled basis.


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