Linked Excel Spreadsheet has to be local?

Question

Linked Excel Spreadsheet has to be local?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

MDAC no longer contains the Jet drivers (after version 2.2, I believe). You have to get them separately from the Microsoft download site. Go to http://www.microsoft.com/downloads and search for Jet. This link takes you to the latest Jet driver service pack, which has the different version by OS: http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;829558 This could be why your Jet version did not change.

Mark

Charles Barnhart-229428 SSC-Addicted Points: 435 More actions · Answer 2

That's what I meant to convey. This morning I tried to apply the Jet driver update but it failed because I already have the most recent version--which was applied with Win2003 SP1.

My version of Msjet40.dll is: 4.00.9025.0

The recommended version for Win2003 is: 4.0.9025.0

I suspect that trying to apply SQLSvr2000 SP4 would not help; but, I admit, I haven't tried it yet.

cjb

peterhe SSChampion Points: 11469 More actions · Answer 3

Mark is right. I checked my machine. It turned out that IT department rolled out windows 2000 SP4 recently in our network. The SP installed the latest version msjet40.dll and moved the older one into the SP uninstall folder.

udaykrao Newbie Points: 4 More actions · Answer 4

I am having the same issue and have tried all the items mentioned here. I can access the Excel file using OPENDATASOURCE if it is on the server that is running SQL Server. Also, I can access it if it is on another UNC path as long as I run the query from the server that is running SQL Server, but if I try to run the same from another computer using the same account (it is the account that runs the SQL server and is an SA), I get the error.

Did you ever find a solution that works?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

In order to use non-local UNC's, the server services must start with a windows login that has access to the UNC's desired. Our DBA's setup a special "SQLDBA" user (with a very complicated 14 character password, I may add) on our Windows Servers that have access to all files, directories, and drives on all boxes including local desktop machines.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

udaykrao Newbie Points: 4 More actions · Answer 6

Do you know which server services start with the login that has super-access? In our case, the MSSQLSERVER and SQLSERVERAGENT services start with a domain account that has full access to the server that is running the SQL Server database AND also has full access to the server that has the excel spreadsheet. If there are other services I need to start with this account, I can. Thanks for the information.

--UK

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

It would usually be someone who has a role of "Domain Admin" (I think that's what they call it but not sure... I'm not a network type of guy).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

udaykrao Newbie Points: 4 More actions · Answer 8

This morning we did what you suggested. Gave the account that runs SQL server full control access to all files, directories, services, etc. and added it to the "Domain Admin" group on the two servers - the one that runs SQL server and the one that has the spreadsheet on it. We then tried to use OPENDATASOURCE from the server that has the sheet on it with no luck. We then reversed our changes to keep our security intact. Will keep looking for a solution. thanks. UK

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

I don't know what I'm missing on this for you... I'll check with my network guys on Tuesday. Sorry I wasn't able to help you without getting help myself

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

udaykrao Newbie Points: 4 More actions · Answer 10

You have helped a LOT. With your help, I have at least narrowed it down to a security/permissions issue AND most importantly, proved that it does work. thanks. UK