Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any ideas how to connect to an Informix DB?

I need to use the an OLE DB Provider to create a Linked Server using a ODBC-Connection (Informix). I know that I cannot see many things in the Providers drop-down list. Has anyone created a linked server like this one? I mean SQL 2005 (Standard Edition) x64 and Informix. 10x in advance.

Hi sageata2002,

I've got the same problem, and still searching the solution.

Perhaps we have to downgrade to SQL-Server 2005 x86 (32bit), where the MSDASQL-Provider (OLEDB Bridge for ODBC) is available....

|||

Hello.

I am waiting for an answer from a company named Openlink, since they told me they will provide the solution in couple of days (their driver is still in beta). I will keep anyone posted about this, and, if successful, I will provide the details (my understanding is that the same procedure will apply to Oracle, for example).

|||

Can you inform me, as soon as the Openlink-Solution is available?
That would be greatful!

Thank you very much.

Heiko Stahl
Systemadministrator
TQ-Systems GmbH

Heiko.Stahl@.tqs.de
http://www.tq-group.com

|||Of course I'll provide any useful news as soon as they will arive. I want this solved and shared with the rest of us, so we don't have to be at Microsoft's mercy; or any other giant for that matter|||

In the meantime I have contacted OpenLink, too.

I got a evaluation-version of an OLEDB-Provider for ODBC.

(download here:
http://download.openlinksw.com/uda/open60/wil6zole.msi ;; IA64
http://download.openlinksw.com/uda/open60/wal6zole.msi ;; AMD64
)

Unfortunately this Bridge works only with 64bit ODBC drivers, but our Informix SE is only 32 bit.

I must downgrade the SQL-Server to 32bit, no other way can help.

Regards,

Heiko