Update problem with T-SQL OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'

Question

Hi
I am migrating on a new sql2016 sp3 server and transferring every functionality from an old SQL2005 (yes, old)

I am using OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'
for various read and insert scenarios to read and write to XLS files
(I used OLEDB.4.0 on the old 2005)

all read and insert statements are working perfectly the same as arestill working on the old server without any modifications.
but when I try the simplest update the same way as it still wokes on the old server it gives me an error on the new

here's the scripts

select * from OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\test.xls;
Extended Properties=Excel 12.0')...[Lista_i_D$]
where Nr_crt in ('Ant1', 'Ant2')
--select works OK

INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\test.xls;
Extended Properties=Excel 12.0')...[Lisat_i_D$]
select 1, 'B1A', 'M1110 V A', '--', 'PER', 7.0, 121.384, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
--Insert works OK

Update OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\test.xls;
Extended Properties=Excel 12.0')...[Lista_i_D$]
set Denumire='aaa'
where Nr_crt in ('Ant1', 'Ant2')
--Update gives error
--OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Syntax error in UPDATE statement.".
--Msg 7321, Level 16, State 2, Line 37
--An error occurred while preparing the query "UPDATE Lista_i_D$ set Denumire = 'aaa' WHERE Nr_crt='Ant2' OR Nr_crt='Ant1'" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Any suggestions what different sintax is required on sql2016 that was ok on 2005?
Thanks

Answer 1

Hi @SC BONTIMES SRL BONTIMES
How about using OPENROWSET,check this:

UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0',  
                  'Excel 12.0;DATABASE=D:\test.xls',  
                  'SELECT * from [Lista_i_D$] ')  
SET Denumire='aaa'  
WHERE Nr_crt IN ('Ant1', 'Ant2')  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @SC BONTIMES SRL BONTIMES ,

Pease try to add additional single quote for the UPDATE statement:

...  
set Denumire = ''aaa'' WHERE Nr_crt=''Ant2'' OR Nr_crt=''Ant1''  

Answer 3

Excelent LiHongMSFT-3908
You are my saver today

it workes flawlessly with openrowset
I will change all my scripts to this variant. (I have a lot)
Back when I first created these scripts at least 8 years ago, I used what I found fist working option on the internet.

big Thanks for your help