1

I have a table Test with column id (int) and column label (varchar(512)) and an excelfile test.xlsx with sheet test and the two columns. I use the ACE-provider to write from sqlserver to the excelfile. There are values longer than 255 characters in the column "label".

Scenario 1

IMEX=0, no values longer than 255 characters; I trunc label to 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=0;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       cast(label as varchar(255)) label
FROM   Test

This works fine.

Scenario 2

IMEX=0, values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=0;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       label
FROM   Test

Since there are values longer then 255 characters in label I get the message

String or binary data would be truncated.

This is normal for IMEX=0.

Scenario 3

IMEX=1, no values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=1;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       cast(label as varchar(255)) label
FROM   Test

I get this message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.". Msg 7399, Level 16, State 1, Line 5 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation. Msg 7343, Level 16, State 2, Line 5 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]".

Scenario 4

IMEX=1, values longer than 255 characters

insert into OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=D:\test\test.xlsx;HDR=NO;IMEX=1;ReadOnly=FALSE',
    'SELECT * FROM [test$]')
SELECT id,
       label
FROM   Test

I get the same message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only." etc.


Now I made sure the excel-file has all the persmission for the users (sqlserver-service-user, user running the script, ...) involved (otherwise it wouldn't write any lines in the very first scenario with imex=0), I even tried with "everyone", no succes.

My guess is, if IMEX=1 is involved, there is another folder or file that needs acces?

Note that I already gave read/write permission to the user running the query on "C:\Users\userThatRunsServiceOfSqlServer\AppData\Local\Temp".

Any suggestions?

Sql server 2012 sp3 / Excel 2010

  • have you tried to write to the excel file using the import/export wizard? – James Jun 7 '18 at 14:03
  • James, I used SSMS, and Microsoft.ACE.OLEDB.12.0 is in the provider-list of the linked servers. But, as I tried the import-wizard (directly on the server), I can choose excel 2007, but then I get an error:The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data) – luc Jun 7 '18 at 15:56
  • so the ace provider has been installed for 64 bits (with Microsoft Access Database Engine 2010 Redistributable) I guess, and the import/export tool of ssms itself is 32bit, But this has nothing to do with my initial problem, no? or does it? – luc Jun 7 '18 at 16:07
  • Maaaybe. These drivers can be a bitch. In the SQL Server Export wizard, when you choose a destination, what do you see? I've got 3 that might work on my end: Microsoft Access (microsoft Access Database Engine), Microsoft Access (Microsoft Jet Database Engine) and Microsoft Excel. I always use the 3rd one, since the other two argue with me so much! – James Jun 7 '18 at 16:27
  • I have the last two options, and I also choose excel. Well thanks anyway (if i have to use the export wizard, I will install the 32 bit version of the ace provider, but openrowset in my ssms-query uses the 64bit version and that one is installed) – luc Jun 7 '18 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.