Accessing Linked Excel Server

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

Hi, David,

The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:

== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again

== If you are using NT authentication, try using SQL authentication to see if this changes the effect

== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Access to SQL2000

What is the best way to import or transfer a Access database
to sql2000, I tried that wizard and for the life of me I cannot get
it to work correctly.

TIA

Michael

Please answer in the newsgroups so that others may see thie
information also.On Sat, 29 Nov 2003 07:05:35 -0500, "Michael, "
<ptbarnum@.nycap.rr.com> wrote:

>What is the best way to import or transfer a Access database
>to sql2000, I tried that wizard and for the life of me I cannot get
>it to work correctly.
>TIA
>Michael
>Please answer in the newsgroups so that others may see thie
>information also.|||"Michael, " <ptbarnum@.nycap.rr.com> wrote in message
news:5o2hsv84fvtgb11ifl5smh3fqr28qkbu7f@.4ax.com...
> What is the best way to import or transfer a Access database
> to sql2000, I tried that wizard and for the life of me I cannot get
> it to work correctly.
> TIA
> Michael
> Please answer in the newsgroups so that others may see thie
> information also.

Your best bet would be to use DTS. Is that what you mean by wizard? If so,
what is the failure specifically?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

Access Linked Server: Works in SQL2000 but not SQL2005

I am runing Windows 2003 which has both SQL2000 and SQL2005.

The following works in 2000 but not 20005

Code Snippet

DECLARE @.sql nvarchar(4000)

set @.sql = 'sp_addlinkedserver @.server = N''dbAccessPO'',

@.provider = N''Microsoft.Jet.OLEDB.4.0'',

@.srvproduct = N''OLE DB Provider for Jet'',

@.datasrc = N''C:\Temp\popts.mdb'''

exec sp_executesql @.sql

set @.sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'

exec sp_executesql @.sql

Running this:

Code Snippet

select * from dbAccessPO...MyTable

Produces this error:

Code Snippet

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".

I login using sa for both instances.

I could really use your help.

Thanks.

Could someone please help me with this issue?

I am sure it is a permission error, but it couldn't be file permission.

I created a CLR stored procedure as well and when I tried to open the connection using oledb it gave me an unspecified error as well. Even odbc errored out.

2005 must have more strict permissions. What is funny is that I had the stored procedure working in 2005 and all of a sudden it stopped working. I even tried rebooting the machine.

I have seen sites that suggest you give read/write to the temp folder of the user account that the sql service runs under and that did not work either.

The SQL service is running under a domain account, so that is not an issue either.

Drives me crazy that it works in 2000, but not 2005. If anything, you would think it would be the other way around.

|||

Well, I tried rebooting the machine yesterday and it did not work like I said before, but today I stopped the sql service and then restarted. Now it works.

That is messed up. I sure hope M$ fixes this problem as MANY people are having the same issue.

This will be fine for now, but I plan to change the way we do backups for next year.

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
AlanI can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>

Access Linked Server Breaks

Hi,
1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
couple of weeks. If I do a SELECT statement to the Linked server in Query
Analyzer,, even on a small table of 3 records, QA seems to be working, and
no error messages come up. However, the SELECT command never completes.
2. This problem can always be cured by a reboot of server, but then it
happens again a few days or a few weeks later.
3. This exact same setup worked on another server for 1 3/4 years, but the
problem is on a newly set up SQLServer box.
4. Has anyone experienced such symptoms and found a resolution for sporadic
Linked Server to mdb failures?
5. When a Linked Server connects to an Access mdb, is this being done via an
IP connnection?
I believe a couple of years ago I had viewed the linked server process in
motion, showing among other things some IP connections, but I don't remember
how I did that.
Thanks for any help.
Alan
I can't put my finger on it, but this feels like a SQL Server "mem-to-leave"
issue (the portion of SQL Server process memory that is not the buffer
pool.). Linked server info is stored there. Don't know how to help other
than suggesting running DBCC FREEPROCCACHE instead of restarting sql server
(or do you have to reboot the whole box?)
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
> Hi,
> 1. I have a SQL2000, SP3 Linked Server to an Access mdb that breaks every
> couple of weeks. If I do a SELECT statement to the Linked server in Query
> Analyzer,, even on a small table of 3 records, QA seems to be working, and
> no error messages come up. However, the SELECT command never completes.
> 2. This problem can always be cured by a reboot of server, but then it
> happens again a few days or a few weeks later.
> 3. This exact same setup worked on another server for 1 3/4 years, but the
> problem is on a newly set up SQLServer box.
> 4. Has anyone experienced such symptoms and found a resolution for
> sporadic Linked Server to mdb failures?
> 5. When a Linked Server connects to an Access mdb, is this being done via
> an IP connnection?
> I believe a couple of years ago I had viewed the linked server process in
> motion, showing among other things some IP connections, but I don't
> remember how I did that.
> Thanks for any help.
> Alan
>
|||Kevin,
Thanks very much for your reply.
I will try that next time Linked server breaks.
Whole box has had to be rebooted to cure this.
Regards,
Alan
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>I can't put my finger on it, but this feels like a SQL Server
>"mem-to-leave" issue (the portion of SQL Server process memory that is not
>the buffer pool.). Linked server info is stored there. Don't know how to
>help other than suggesting running DBCC FREEPROCCACHE instead of restarting
>sql server (or do you have to reboot the whole box?)
>
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:%23SR7gC0PHHA.4280@.TK2MSFTNGP02.phx.gbl...
>
|||You may also want to test SP4...lots of memory leaks were fixed between 3
and 4
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e1YnGx8PHHA.4924@.TK2MSFTNGP05.phx.gbl...
> Kevin,
> Thanks very much for your reply.
> I will try that next time Linked server breaks.
> Whole box has had to be rebooted to cure this.
> Regards,
> Alan
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23n6Vrm7PHHA.992@.TK2MSFTNGP06.phx.gbl...
>