Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #281

Linked server to Access MDB

Show all headers | View raw

I am trying to create a Linked Server from MS SQL server Management Studio
to a MDB file on a 2nd Server.
Using Windows authentication I can query a remote table in the Studio but
not from a remote application. Using SQL authentication I get the following
error "Cannot start your application. The workgroup information file is
missing or opened exclusively by another user." If the MDB file is moved to
the C: drive of the SQL server, everything works, using both authentication
methods. There is no security setup on the MDB file.
SQL server is NOT using a Domain account.

SQL server 2008, 64Bit.
Provider: Microsoft.ACE.OLEDB.12.0
                 Allow inprocess = True

msjet40.dll version is 4.0.9755.0

I got Access is denied from
exec xp_cmdshell 'dir \\pack01\data\paisys\Data-V10\Backup\X4\*.*';
go

The script generated by management studio is as follows
/****** Object:  LinkedServer [PARTSDB]    Script Date: 05/04/2011 09:15:41
******/
EXEC master.dbo.sp_addlinkedserver @server = N'PARTSDB',
@srvproduct=N'PARTSDB', @provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\pack01\Data\PaiSys\Data-V10\backup\X4\CS.mdb'
 /* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'PARTSDB',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'rpc',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'rpc out',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'use remote
collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARTSDB', @optname=N'remote proc
transaction promotion', @optvalue=N'true'
GO

 

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-04 12:36 -0500
  Re: Linked server to Access MDB Erland Sommarskog <esquel@sommarskog.se> - 2011-05-04 23:37 +0200
    Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-05 06:56 -0500
      Re: Linked server to Access MDB Henk van den Berg <hvandenberg@xs4all.nl> - 2011-05-05 16:37 +0200
        Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-05 13:30 -0500
          Re: Linked server to Access MDB "ron paii" <none@nospam.com> - 2011-05-06 14:51 -0500

csiph-web