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

Linked server to Access MDB

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	"ron paii" <none@nospam.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Linked server to Access MDB
Date	Wed, 4 May 2011 12:36:04 -0500
Organization	A noiseless patient Spider
Lines	2
Message-ID	<ips2q7$1ot$1@dont-email.me> (permalink)
Mime-Version	1.0
Content-Type	text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding	7bit
Injection-Date	Wed, 4 May 2011 17:36:07 +0000 (UTC)
Injection-Info	mx01.eternal-september.org; posting-host="CzRErDNbRazHAY/a5dIFZQ"; logging-data="1821"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18dWFQoh/i4EXMJot6Qeu3I"
X-MimeOLE	Produced By Microsoft MimeOLE V14.0.8117.416
X-Newsreader	Microsoft Windows Live Mail 14.0.8117.416
Importance	Normal
Cancel-Lock	sha1:sy4HJVZOAkeyKDtsQwdnzlgk/hA=
X-Priority	3
X-MSMail-Priority	Normal
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:281

Show key headers only | 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