Linked Excel Spreadsheet has to be local?
-
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 -
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
-
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.
-
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?
-
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)
Intro to Tally Tables and Functions -
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
-
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)
Intro to Tally Tables and Functions -
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
-
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)
Intro to Tally Tables and Functions -
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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply