SQL Server OPENROWSET errors only when DELETE/UPDATE

[pgfiore]

Sorry for invading this area with an issue that's probably not related to the driver code. Unfortunately, I've got stuck using your Firebird ODBC 3.0.1.18 (x64) on a Win Server 2019 running SQL Server 2022.
And trying to reduce variables, I've focused on the T-SQL OPENROWSET with only the (machine) DSN including all parametrs.
My test statement is something like: select * from openrowset('MSDASQL', 'DSN=TESTDB', 'select * from myTABLE')
OK - SELECT and INSERT do work like a charm!
KO - UPDATE and DELETE always fire the error message 7345.

As you know, there are many different layers to go through. Even the MSDASQL provider is adding a further ODBC/OleDB conversion.
A few more clues:
DNS user = SYSDBA - other parameters = default
MyTable does have a primary key
MSDASQL provider = nested queries, dynamic parms and allow inprocess all ON
(BTW, I've tried linked servers and/or OPENQUERY too, with exactly the same error.)

Error messages:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 7345, Level 16, State 1, Line 9
The OLE DB provider "MSDASQL" for linked server "(null)" could not delete from table "select * FROM myTable". User did not have sufficient permission to delete the row. (my comment: I use SYSDBA!)

Did you ever work out the reason(s) or can you point me in the right direction to investigate? I'd appreciate it a lot.

[irodushka]

Hi I think it's not a good idea trying to delete or update from select statement, for such a constructions are not supported by FIrebird SQL. Can you try smth like this - openrowset('MSDASQL', 'DSN=TESTDB', 'myTable') ? I haven't tested it myself but suppose it may work. Regards пт, 1 авг. 2025 г., 15:57 bingereading ***@***.***>:

…

*pgfiore* created an issue (FirebirdSQL/firebird-odbc-driver#260) <#260> Sorry for invading this area with an issue that's probably not related to the driver code. Unfortunately, I've got stuck using your Firebird ODBC 3.0.1.18 (x64) on a Win Server 2019 running SQL Server 2022. And trying to reduce variables, I've focused on the T-SQL OPENROWSET with only the (machine) DSN including all parametrs. My test statement is something like: *select * from openrowset('MSDASQL', 'DSN=TESTDB', 'select * from myTABLE')* OK - SELECT and INSERT do work like a charm! KO - UPDATE and DELETE always fire the error message 7345. As you know, there are many different layers to go through. Even the MSDASQL provider is adding a further ODBC/OleDB conversion. A few more clues: DNS user = SYSDBA - other parameters = default MyTable does have a primary key MSDASQL provider = nested queries, dynamic parms and allow inprocess all ON (BTW, I've tried linked servers and/or OPENQUERY too, with exactly the same error.) Error messages: *OLE DB provider "MSDASQL" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Msg 7345, Level 16, State 1, Line 9 The OLE DB provider "MSDASQL" for linked server "(null)" could not delete from table "select * FROM myTable". User did not have sufficient permission to delete the row. (my comment: I use SYSDBA!)* Did you ever work out the reason(s) or can you point me in the right direction to investigate? I'd appreciate it a lot. — Reply to this email directly, view it on GitHub <#260>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAKZQLV6CXKMT7MPSDL6TLD3LNP47AVCNFSM6AAAAACC4WNQ2OVHI2DSMVQWIX3LMV43ASLTON2WKOZTGI4DGOBUHAZDOMQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[pgfiore]

Thank you irodushka
I've tried and got a generic 7321 "An error occurred while preparing the query".
AFAIK the e.g. delete from openrowset('MSDASQL', 'DSN=TESTDB', 'select * from myTABLE') is a bit of a standard (well, Microsoft's standards) construction. I even use it against old .dbf dbase files: OPENQUERY Examples.

Is there anything I can test or try IYHO?

ciao

[irodushka]

Hi @pgfiore

I've tried to reproduce you issue - as best I understand it.
I used the standard employee.fdb from FB installation and accessed it from MSSQL via MSSQL Managent Studio, via a System DSN EMPL_SYS (SYSDBA).

All these statements work fine and I can see the corresponding changes in isql, running in another terminal:

select * from openrowset('MSDASQL', 'DSN=EMPL_SYS', 'select * from COUNTRY') where COUNTRY = 'Japan'
update openrowset('MSDASQL', 'DSN=EMPL_SYS', 'select * from COUNTRY') set CURRENCY='Yeng' where COUNTRY='Japan'
insert into openrowset('MSDASQL', 'DSN=EMPL_SYS', 'select * from COUNTRY') values ('Nagonia', 'Lua');
delete from openrowset('MSDASQL', 'DSN=EMPL_SYS', 'select * from COUNTRY') where COUNTRY = 'Nagonia';

So I have to ask you to clarify what your problem is, since I suspect I didn't got a point.

Regards
Yuri

[pgfiore]

Many thanks, Yuri, and sorry for being late with this note.
I feel like you actually got the point and my issues could arise from some system config...

The below screenshot shows my DNS (sorry, despite Windows being in English, ODBC displays Italian; I hope you can match the fields which are mostly defaults).

• Firebird 5.0.3.1683
• My databases are .GDB
• Confirmed using the FBCLIENT 5.0.3.1683 dated 2025 July 14
• The connection test works

Furthermore, I'm working on a multi-server network. SQLSrv and Firebird are two different HW running Windows Server 2019.
I ran exactly the statement you provided and got:

Msg 7399, Level 16, State 1, Line 5
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7343, Level 16, State 4, Line 5
The OLE DB provider "MSDASQL" for linked server "(null)" could not UPDATE table "[MSDASQL]". Unknown provider error.

That's weird. And I have no idea how to move next. Sorry for pesting you.

EDIT: Kindly could I hv more info about yr environment? Are FB & SQLSrv on the same machine or any remote communications is involved? I'll try to reproduce a similar setting on my laptop. Win, SQLSrv, FB version?