We solved this!
When then drivers got updated, the ACE & MSDASQL provider settings got reset to the default, which had the Allow Inprocess function enabled.
We simply unchecked it for the providers that use OPENROWSET, and has been working fine since.
@SeanGallardy-MostlyRetired I guess people use whatever option they have. If MS doesn't provide anything else then that's what they'll do. SQL Server has poor bulk-loading tools, so people will use whatever hack they can.
@Charlieface I get it, but it is supported for SSIS or import/export. You can also use it to write your own apps to do bulk loading. People don't want that, they want linked server stuff and then complain when it isn't as fast as bulk load/app/etc or that it crashes/doesn't work for every possible feature.
So could MS put something out that does it? Sure. Will they? No.
No money to be made there and tons of other options in that space.
@ErikDarling I thought it was just me, I was trying to paste some screenshots into chat and couldn't. I blamed it on MS Edge.
"No money to be made there" meaning SSIS doesn't have an Express or a Web licence.... Sorry, just being cynical, everyone needs to make money I suppose.
Yup, and I'm primarily a developer so I also wouldn't use ACE, I would just write my own automation system. I do understand people who do use it though.
It's not like there aren't a ton of other undocumented procs and quirky update thingys that people do.
My whole point was, while using 90 proof alcohol to run your car might work for a little bit, don't complain when it kills your engine and expect it not to when it's not supported
Having said that, the amount of MS people that ping me and say, "Is this really you?" and I say it is, tell me how much they laugh at the picture and think it's great and hilarious.
Reminds me of the supermarkets rearranging products every couple of months so the customers spend more time in there searching for the things they want.
We solved this!
When then drivers got updated, the ACE & MSDASQL provider settings got reset to the default, which had the Allow Inprocess function enabled.
We simply unchecked it for the providers that use OPENROWSET, and has been working fine since.
