I need help exporting to excel file

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need help exporting to excel file

Author

Topic

Cwm
Starting Member

39 Posts

Posted - 2008-11-12 : 04:29:31

Hi all,
I am trying to export from sql 2005 to an excel sheet...here is my stored procedure and the error i am getting..

CREATE PROCEDURE CanolaLog
(
@LogInID int
)
AS
insert into openrowset ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Test\Book4.xls;','select OrderNumber, Customer, Remarks, NetWeight, DateShipped FROM [Sheet1$]')
select c.OrderNumber as "Order Number",
c.DateCreated as "Date Shipped",
s.ShipToCustomer as "Customer",
r.Remarks as "Remarks",
w.NetWeight as "Net Weight"
from [CanolaMealTicket] c
inner join [ShipTo] s on s.ShipToID = c.ShipToID
inner join [Remarks] r on r.RemarkID = c.RemarkID
inner join [Weights] w on w.WeightID = c.WeightID
where LogInID = @LogInID
RETURN

here is the error when i try to save..

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" supplied invalid metadata for column "OrderNumber". The data type is not supported.

The datatype for "OrderNumber" is nvarchar(10). I had a similar stored procedure work before but now that one doesn't work either.

So does anyone know what is going on? or another way to export data to excel?

Thanks for all the help

Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 04:33:21

[code]CREATE PROCEDURE CanolaLog
(
@LogInID int
)
AS

SET NOCOUNT ON

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Test\Book4.xls;', 'SELECT OrderNumber, Customer, Remarks, NetWeight, DateShipped FROM [Sheet1$]')
SELECT c.OrderNumber AS [Order Number],
c.DateCreated AS [Date Shipped],
s.ShipToCustomer AS Customer,
r.Remarks AS Remarks,
w.NetWeight AS [Net Weight]
FROM CanolaMealTicket AS c
INNER JOIN ShipTo AS s on s.ShipToID = c.ShipToID
INNER JOIN Remarks AS r on r.RemarkID = c.RemarkID
INNER JOIN Weights AS w on w.WeightID = c.WeightID
WHERE LogInID = @LogInID[/code]

E 12°55'05.63"
N 56°04'39.26"

Cwm
Starting Member

39 Posts

Posted - 2008-11-12 : 05:12:43

Hi I just tried what you replied with and still get the same error

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 05:14:58

You have not the same order of columns for OPENROWSET as in your SELECT statements.
Maybe there is a datatype clash?

E 12°55'05.63"
N 56°04'39.26"

Cwm
Starting Member

39 Posts

Posted - 2008-11-12 : 05:28:58

I changed somethings around, put the right order of things but still get the same error as before, I am not sure why. Not sure if there is a clash or what not. Even when i take out the ordernumber,i still get the same error on the next column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 05:49:41

Try to use te ODBC provider temporarilty to see more detailed error messages.

E 12°55'05.63"
N 56°04'39.26"

Cwm
Starting Member

39 Posts

Posted - 2008-11-12 : 08:58:27

I figured out what i was doing wrong....When I was creating the excel workbook i was not saving it as a 97-2003 workbook. I am using office 2007, so when i saved it as a workbook it wasn't working until i changed the format.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources