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.
| Author |
Topic |
|
twl55
Starting Member
19 Posts |
 Posted - 2011-08-28 : 11:42:13
|
| I am attempting to export the results of a query in SQL Server 2005 to Excel. When I issue 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 everything works perfectly.When I issue INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'EXCEL 8.0;Database=C:\Users\admin\showtst.xls;','SELECT * FROM [SheetName$]') SELECT order_no,order_date FROM oe_hdr I get the following error:Msg 213, Level 16, State 1, Line 1Insert Error: Column name or number of supplied values does not match table definition.How do I get multiple columns exported.Thankstwl55 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-29 : 00:28:50
|
| do you have two columns created in sheet ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
twl55
Starting Member
19 Posts |
Posted - 2011-08-29 : 09:13:09
|
| When you ask if I have two columns created, I assume you mean do I have 2 columns with headings. The answer was no, but now is yes and I eventually figured out late last night that those columns must have the exact same names as the columns produced by the SQL statement.When I export into the area under those column headings in Excel, must the cells all be cleared. It does not overwrite cells with data does it?Thanks for the helptwl55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-29 : 10:25:29
|
| it wont overwrite. it will append------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Can only export 1 col from SQL Server to Excel