SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Trying to export SQL Server to Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

twl55
Starting Member

USA
19 Posts

Posted - 08/27/2011 :  22:22:27  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 08/28/2011 :  01:47:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 08/28/2011 :  02:52:30  Show Profile  Visit jackv's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 08/28/2011 :  15:32:55  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 08/29/2011 :  00:21:59  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

Vivekk
Starting Member

India
1 Posts

Posted - 04/25/2012 :  03:46:49  Show Profile  Reply with Quote
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

USA
8 Posts

Posted - 02/11/2013 :  08:39:51  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 02/11/2013 :  10:31:38  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 02/27/2013 :  04:12:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  13:51:35  Show Profile  Reply with Quote
.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 - 03/19/2013 :  22:37:38  Show Profile  Reply with Quote
It worked fine. Thank you for the help.





________________________
http://www.meinwowgold.de

Edited by - sqlsogmen on 03/19/2013 22:38:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000