| 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 isMsg 7343, Level 16, State 2, Line 1The 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.Thankstwl55 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
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.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-29 : 00:21:59
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
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 |
|
|
|
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 @TSIf 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 129Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".any help is greatly appreciated thanks |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
jakeliu
Starting Member
3 Posts |
|
|
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.CheersMIK |
|
|
|
sqlsogmen
Starting Member
3 Posts |
Posted - 2013-03-19 : 22:37:38
|
| It worked fine. Thank you for the help.________________________http://www.meinwowgold.de |
|
|
|
|
Trying to export SQL Server to Excel