My application consists of a Master Excel workbook WB, and any number of Client Excel Workbooks. All contain reports driven by SELECT QUERYs driven by Excel Data Connections to SQL Server via SQLOLEDB and now MSOLEDBSQL. Those QUERYs in SQL Server include data from the Master WB which is accessed by SQL Server via Microsoft.ACE.OLEDB.12.
The Master WB also creates Stored Procedures in SQL Server using DROP PROCEDURE and CREATE PROCEDURE, whose SQL is configured by data in Excel. Reports in Client WBs are driven by Data Connections that EXEC those SPs. This way I can "hide" file structures of data being picked up from Excel, and also provide a single point of control of the SP SQL. I expect this is a not-unusual structure.
This all worked quite well until sometime last year, 2019. Research now shows that Excel broke this in Version 1907. We're well beyond that now, I'm on version 2004. Now all the SELECTs still work, and EXECs still work as long as they invoke SELECT in an SP. But DROP and CREATE from the Master WB no longer function, and calling an EXEC from Excel to invoke an SP to do the DROP or CREATE also fails.
Others have encountered related issues:
A discussion at Microsoft Tech Community indicates this was broken in Excel Version 1906 and fixed in 1908 - but I haven't found it to fix my configuration. (For reference, found an enumeration of Excel Versions; I'm on Version 2004 of Office 365 ProPlus/Microsoft 365 Apps for Enterprise) (Other config info: Win10 Pro, SQL Server Express 2019, Visual Studio Community 2019, everything x64);
Another Microsoft Tech Community discussion on Disabling warnings on Native SQL QUERYs seems to be helpful, but is not a solution by itself. Also discussed on StackOverflow;
A discussion at Power BI Community indicates that following a Native SQL QUERY with "SELECT 1 FROM someExistingTable" solves the problem. I've found that to be helpful too but also not a solution by itself.
I've found that I can make DROP PROCEDURE work if I have
Disabled Native SQL QUERY Warnings as above;
Followed the DROP statement with a SELECT 1... statement as above;
And the Connection builder in Excel now forces me to connect to a Table in Excel, even if the SQL in the Connection does not return data (e.g. DROP or CREATE). Even if I don't select a Table when defining the Connection, I am required to assign the connection to a table in Excel when exiting the builder. I hate this, but it does make DROPs work. I've been re-using a single Data Connection for DROP and CREATE and to execute some EXECs, updating the SQL commandtext by VBA to the appropriate SQL as needed.
However... Even with all these little workarounds, CREATE PROCEDURE still doesn't work. So I'm looking for help on how to make a Native SQL QUERY to CREATE PROCEDURE work in the current Excel Version.
If there's broader advice and outlook on command-oriented (i.e., not returning data) Native SQL QUERYs in Excel Data Connections, that's also of interest! Thanks.
