I am communicating with a sql server based ERP system to manage work orders. I can write my own queries, functions,stored procedures etc and build views but that leaves me periodically polling the db for the information I need. I want it to feed my application when an insert or update is made without having to poll it. I think Im talking fewer than 50 insert/updates/day but sporadically throughout with no pattern.
My first thought was I could set a couple of triggers on the table and write out the affected rows and monitor the folder it writes to but that seems so bogus. I just hate āhot foldersā.
Im looking for is someone who can help me use CDC, SSIS or other sql server (not limited to) tools to push data to a windows xojo app. Im also trying to understand the relative burden of various appraoches to 2 way communication with linked servers and openquery vs running them all in the hosting db.
There must be an efficient, light burden way to do this. Is there a way to connect CDC to a webhook from sql server or other such. I feel like there must be something Im not findingā¦
Sounds like a job for Query Notifications and Service Broker?
Iāve not tried this in Xojo to be fair, I donāt know whether the Xojo SQL plugin would allow you to do this - I went to the MBS SQL plugin pretty early and stayed with thatā¦
These are what I was hoping to find. Thank you Paddy! I use MBS too. Is there a particularly simple route and method(s) you are using in MBS? Do you happen to know if MBS impliments āSqlDependencyā ? That looks like the simplest ticket for me. I will start reading these articles more and looking in the MBS docsā¦
After a quick read I am assuming you must be using the mbs method, SQLExexcute? Do you/anyone happen to have an example of how youāre calling the service/queue? I would be extrememly greatful if anyone does or has any tips! Iām going to start down the road of execute/SqlDependency and see if I can figure it out.
Please do elaborate a tich Norm, thanks! I see MSSQL and MicrosoftSQL and the former seems to only be a few basic calls. Whats the 3rd? What do I need to know or look out for or how to read up on it?
You can mostly interoperate between these but I found its better to try & stick to one style or the other
But you CAN use them to connect to just about anything you can find a driver for since they support ODBC
So I can easily connect to MS SQL server from macOS
Hi - to be clear Iāve not done this method in Xojo. If I were I would go straight to the MBS plugins to attempt rather than use the Xojo plugin - Iāve always had far better results with MBS.
Are there significant issues with Xojoās MS SQL Server plugin?
At work it looks like we are going to go with an ERP that uses MS SQL serverā¦ I donāt know if I am going to be allowed to access it from an Xojo app, or even if Iām going to need toā¦ but I might, and I really would not want to spend more money on a plugin for that!
It does not work at all on a Mac!!! That is outrageous!!!
I obviously never used itā¦ At work they are switching everyone to PCsā¦ I have been given special dispensation to stay on my Mac until it canāt be supported by Office (they donāt want me to retire right now! )ā¦ Which means about 2 more yearsā¦ the one I have at work (intel) canāt go beyond Monterey.
Wait a minute ā¦ you canāt use Sql Server with a MacOS client? WTAF?
What HAVE I gotten myself into ā¦
Oh well I guess if I ever need that Iāll just lay out a few more large for MBS. I get the sense that without some of these 3rd party vendors, the whole ecosystem would be in complete collapse.
)ā¦ Which means about 2 more yearsā¦ the one I have at work (intel) canāt go beyond Monterey.