problem with export to excel - SQL Server Forums

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

problem with export to excel

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-10-09 : 10:21:20

Hello

iam learning how to use the openrowset and exporting data to other
sources.

I have create an excel doc with headings called SQLtoExcel.xls
The table i have in sqlserver is excelTasks

and the code iam using is

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Access\SQLtoExcel.xls;',
'SELECT ID, FName, LName, PostalArea, Town from sheet1@')
SELECT ID, FName, LName, PostalArea, Town from excelTasks
GO

the errors i am getting are

Msg 7399, Level 16, state1 Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"(null)" reported an error.
the provider did not give any information about the error.
Msg 7303, level 16 State1 Line 1
cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

is this tell me that i need to create a linked server for provider
'Microsoft.Jet.OLEDB.4.0'

I have done with task with Intergrated Services but i just want to
keep learning. Also when exporting data with wizard, and it asked if
you would like to saved the task in SSIS Package. Where does it save
the task. I have trouble finding it.

Kind regards

Rob

MCTS certified

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-09 : 10:40:34

Hi

Refer this...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926,

-------------------------
R...

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-10-09 : 10:52:36

I have tryed using the example

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

but i am still getting the same errors

what could it be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 11:40:43

http://www.mssqltips.com/tip.asp?tip=1202

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-10-11 : 08:11:47

Thank you for your informative reply.
Before i use an openrowset statement, do i need to create a linked server first, or does it not matter?

Regards

Rob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:23:07

you dont need linked server setup for OPENROWSET.Actually its used for one time (adhoc) access.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-12 : 02:45:16

Make sure the file is closed when querying

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-10-12 : 06:14:53

Still trying and im still getting

cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)"

even when i select from another excel sheet with data useing select * from openrowset.

Cant think of anything else to do. Im searching all over the place and i come across this in other forums but

can not find any other solutions

Regards

Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources