Linked Server

[Tom Ellison]

I need to create a Linked Server to an Access MDB from my SQL Express.

In the past, I have always used EM to set up Linked Servers. EM does not
work with Express.

The Access MDB is not passworded. There is no workgroup.

I have attempted to use QA to create the Linked Server:

sp_addlinkedserver @server = 'MyJet', @srvproduct = '', @provider =
'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:\Documents and Settings\All
Users\Documents\TData.mdb'

I cannot then access the Jet table:

SELECT * FROM MyJet...TData

Server: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "MyJet" reported an error.
Authentication failed.
Server: 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
"MyJet".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyJet" returned
message "Cannot start your application. The workgroup information file is
missing or opened exclusively by another user.".

I have created an MSDE instance and run the same script, with slightly
different error messages.

When I do this, I can then see the MSDE Linked Server in EM, and I can fix
it there:

Linked Server Properties
Security
For a login not defined in the list above, connections will:
Be made without using a security context

When I check the above box, the linked server then functions properly.

Now, the trick would almost certainly be to know just what option setting
this change in EM creates and make that selection change in the original
sp_addlinkedserver call. Make sense? OK, but what change is that?

Tom Ellison

 

[Tom Ellison]

Dear friends:

The question has been answered. What is required is a call to:

sp_addlinkedsrvlogin

Thanks, oj.

Tom Ellison