SQL Server & Xojo - server local app (cross posted TOF)

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…

I am open to consulting on this too.

I’ve pinged @Paddy who I think might be able to help


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…

Another article

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.

Just be aware that MBS kind of has 3 APIs for accessing the DB in one plugin :slight_smile:

I use it for client projects and that took me a while to figure out

1 Like

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?

Oh I mean in terms of the classes that are possible to use to interact with the database

hmmm … maybe only 2 really

You’ll find classes & methods that are subclasses of Xojo’s “Database” and related API’s

As well you will find ones that are similar to ADO

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 :slight_smile:
So I can easily connect to MS SQL server from macOS :slight_smile:

1 Like

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.

Its a far more complete SQL plugin IMHO.


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!


You mean besides the fact that it never worked from macOS ?
That was kind of a roadblock being on a Mac :slight_smile:

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! :wink: )… 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.