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
 Can only export 1 col from 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/28/2011 :  11:42:13  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 08/29/2011 :  00:28:50  Show Profile  Reply with Quote
do you have two columns created in sheet ?

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

Go to Top of Page

twl55
Starting Member

USA
19 Posts

Posted - 08/29/2011 :  09:13:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/29/2011 :  10:25:29  Show Profile  Reply with Quote
it wont overwrite. it will append

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

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.06 seconds. Powered By: Snitz Forums 2000