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 1
Insert Error: Column name or number of supplied values does not match table definition.
How do I get multiple columns exported.
Thanks
twl55 |
|
|
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 MVP
http://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 help
twl55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-29 : 10:25:29
|
it wont overwrite. it will append
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
|
|
|
Can only export 1 col from SQL Server to Excel