Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Trying to export SQL Server to Excel

Author  Topic 

twl55
Starting Member

19 Posts

Posted - 2011-08-27 : 22:22:27
I'm very new to this. I'm trying export the result of a SQL Server 2005 to Excel. Everything is 64-bit. When I execute

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'EXCEL 8.0;Database=C:\Users\admin\showtst.xls;','SELECT * FROM [SheetName$]') SELECT order_no FROM oe_hdr where order_no='1000880'

The error I receive is

Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]".


Any help would be greatly appreciated.

Thanks

twl55

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-28 : 01:47:46
see

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-08-28 : 02:52:30
If you are more comfortable with Visual Basic or Powershell , then there are some good libraries available.
For example , within Visual Basic - http://www.sqlserver-dba.com/2011/07/excel-connect-t.html


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

twl55
Starting Member

19 Posts

Posted - 2011-08-28 : 15:32:55
I followed the link from visahk16 and tried the first suggestion and it worked fine. Thank you for the help.

It is working when my SQL out has 1 column and many rows. I haven't been able to get it work once I have multiple columns. I started another post asking for suggestions for getting multiple columns out.

twl55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 00:21:59
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Vivekk
Starting Member

1 Post

Posted - 2012-04-25 : 03:46:49
Hi EveryOne ,
use the following to get the desired output ,

use databaseName
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=c:\qa.xlsx;','SELECT * FROM [Sheet1$]')
SELECT Col1,Col2,Col3 FROM [dbo].[tableName]


Before executing , please make sure to name excel file as qa .
and in its first row add
A B C
----------------
Col1 Col2 Col3

and then close the excel file and run the above statment in query window It works

Best luck:)




Vivek kakkar
Go to Top of Page

badpupsd
Starting Member

8 Posts

Posted - 2013-02-11 : 08:39:51
I've been running the export to excel for many years. Now we've updated to SQL 12 and it's failing.

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=c:\Reports\NeedDocs.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')

SELECT
[LoanNumber],
[BorrName],
[ControlNumber],
[LenderCode],
[ProcName],
[BorrowerAppointmentDate]
[DocStatus]
From @TS

If you select the columns above from @TS, you get data returned.

my error -->
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 129
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


any help is greatly appreciated
thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 10:31:38
is the file in server path or is it in your local machine?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jakeliu
Starting Member

3 Posts

Posted - 2013-02-27 : 04:12:06
use the following to get the desired output


_________________
runescape gold|wow gold kaufen|diablo 3 gold|Swtor Credits



Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 13:51:35
.xls is 2003 version, whereas "Microsoft.ACE.OLEDB.12.0" and "Excel 12.0" are for 2007. Make necessary adjustment so that OLEDB and file extentions are compatible.
Also, make sure Sheet name specified in query is same as that in excel file,
columns should be in same order, rather named them
And that the excel file should be closed during query execution.

Cheers
MIK
Go to Top of Page

sqlsogmen
Starting Member

3 Posts

Posted - 2013-03-19 : 22:37:38
It worked fine. Thank you for the help.





________________________
http://www.meinwowgold.de
Go to Top of Page
   

- Advertisement -