Exporting data to excel from sql server is not working

Question

I am using the below code to export data to excel file. This is working fine when the excel file is blank. But when I update the filed to null then the insert query is executed successfully but the excel is showing blank.

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 
'select Column1,Column2,Column3 FROM [Sheet1$]')
set  Column1=null,Column2=null,Column3=null


insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 'SELECT * FROM [Sheet1$]') 
select Column1,Column2,Column3 from table_Name

I want set blank the file first before writing to that.

Kindly help.

Stephan · Accepted Answer · 2015-03-16 14:01:44Z

0

From what I've read online, openrowset is read only so you need to use OPENDATESOURCE instead. Try something like this.

UPDATE OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
    SET Column1=null,Column2=null,Column3=null


INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
   SELECT Column1,Column2,Column3 from table_Name

answered Mar 16, 2015 at 14:01

Stephan

6,0581 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KousiK Over a year ago

Its working for first time but during update for second time it's showing OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Bookmark is invalid.". Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Stephan Over a year ago

Here look at this forum. It should have all the answers you need. stackoverflow.com/questions/13888082/…

Collectives™ on Stack Overflow

Exporting data to excel from sql server is not working

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related