May 10, 2010 at 9:11 pm
I have two servers - server A and server B. Both are running Win2K8 Server. Server A is our Web server and Server B is our Intranet server where SQL Server Express is running. There's a linked server set up (server B) that points to an MS Access database on server A. The web app (ASP classic...but that shouldn't really matter) running on Server A is trying to connect to the linked server on Server B. So it's kind of a loop. I know it's not very efficient but I need to set it up this way right now for reasons that I'm not going to explains at this point. Let's just say that I'm in the midst of migrating to SQL Server and there are several linked Access tables involved. 🙂
I'm getting this error message:
[indent]
Microsoft OLE DB Provider for SQL Server error '80040e14'
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "login" returned message "Unspecified error".
[/indent]
So...pretty much useless. However, if I run the exact same code on Server B (where SQL Server is located) it connects without a hitch. I currently have the SQL services set to log in using the network admin account (I know that's not ideal but I'm trying to eliminate all possible roadblocks).
I have a feeling that it has something to do with the SECURITY settings in the Linked Server Properties window but I'm not sure. Specifically, I'm guessing that I need to set up a login under the "Local server login to remote server login mappings".
I've tried a bunch of things to no avail. I'm fairly new at this so I'm not sure what information I need to provide to try and get past this but it's driving me batty. Does anyone have any idea as to what I can try?
If you need more info, please let me know...I appreciate any and all suggestions.
Thanks.
May 11, 2010 at 1:42 pm
OK, I got this error figured out. It was the permissions to the TEMP folder on the Web server. However, I'm now getting this error which is much more common..
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXXXX" returned message "The Microsoft Jet database engine cannot open the file '\\XXXXWEB\c\inetpub\database\login.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
This seems a little more manageable since I'm now sure it's a permissions issue...but I've tried some things without success yet.
Any suggestions would be greatly appreciated. 😀
June 1, 2010 at 8:45 am
Strangely, I rec'd two email notifications over the weekend saying that someone has replied to this topic yet there are no replies. What's up with that?
(And I still have yet to solve the permissions issue. I'm pretty close to calling MS on it.)
June 10, 2010 at 2:59 pm
So...really? 173 views and not one reply? :unsure: Not even "sorry I can't help you" ?
I ended up installing SQL Express Server 2008 on a new machine that's running Win2K3. Same result. I can do everything but connect to a linked server table. I get this message...
Microsoft OLE DB Provider for SQL Server error '80040e14'
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "RTWEBLOGIN" returned message "The Microsoft Jet database engine cannot open the file '\\webservername\c\inetpub\database\login.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
/test/sqlserver-sqlexpress.asp, line 27
Both the IIS user account and the SQL Services user account are domain accounts with proper permissions to the access database file. There's got to be something missing from the SQL config. I'll consider any suggestions at all (except snarky ones that tell me all about how we shouldn't be using Access for this 🙂 ).
Thx!
June 11, 2010 at 2:46 am
Try google the fault code with the text of the error you are getting, just delete your specific filename. Google is my best friend when it comes to these problems. There is lots of material out there by each error code, on SQLServerCentral & others.
I googled your code / text and the very first link showed lots of promise. Have you explored these?
http://xlinesoft.com/asprunnernet/faq.htm#exclusively
I liked the comment about DSN & Exclusive...
June 14, 2010 at 9:26 am
I googled the heck out of it...for the better part of 2 weeks. 🙂 I did not, however, come upon that link. At a glance it looks like I've tried or examined everything in that list but I'll take a closer look at some point today.
Thanks for the feedback, bbryce!
June 14, 2010 at 11:50 am
bbryce (6/11/2010)
http://xlinesoft.com/asprunnernet/faq.htm#exclusivelyI liked the comment about DSN & Exclusive...
I was looking at that too. I'm not using a DSN (tried it but it still didn't make a difference). I'm not sure what it means to have a file marked "exclusive" though. I just did a little googling but I'm not coming up with anything useful. Any idea what, exactly, that means?
June 15, 2010 at 2:18 am
Try MS-Access' Help and key in 'Open Exclusive'... It's pretty basic. When you go to open an MS-Access file you have the option to open as multi-user or 'exclusive'. The latter gives you sole access to the database at any one time.
Normally you would only open exclusive if you wanted to change the design of the database - opening in this way may be blocking SQL Server from opening / connecting with more than one connection... There is an option (I can't remember where) to mandate SQL connects mulit user (Use SQL / Access 'Help' to find the default / options here)...
June 15, 2010 at 2:00 pm
Oh, OK, yeah, I know how to do that. I thought that article meant that the file could be "designated" as "exclusive" or something like that. I checked all options and everything is set to open in shared mode.
I think what I'm going to do is simply move the mdb file(s) that I need to add as linked servers to the same server where SQL Server is installed. I'm hoping that won't cause any connectivity problems from the web server but I'll test it out. I know for sure that the linked server will work from there.
Thanks for taking some time to try to help me out, bb. I appreciate it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply