Adding Linked Servers
-
This is my first post so if I get it wrong, please let me know.
I am attempting to add two linked servers. One is a link to a SQL Server on another server in our network and the other is to an Access DB . So far all I have proved is that I must be crazy. I keep trying the same things over and over again hoping for different results.
I've searched the Internet and have come up with the following. All the videos I have seen go the other direction, making SQL Tables available in Access. I need the Access tables available in SQL.
Here is the code to add the connection to the other SQL server
exec sp_addlinkedserver
@server=N'bcm'
, @srvproduct=N''
, @provider=N'SQLNCLI11'
, @datasrc=N'\\srv-apps01\BCMMaster'
exec sp_addlinkedsrvlogin'bcm','true','<network>\<userid>','<password>'
select * from bcm.BCMMaster.dbo.ContactMainTable
result:
OLE DB provider "SQLNCLI11" for linked server "bcm" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "bcm" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 87, Level 16, State 1, Line 22
SQL Server Network Interfaces: Connection string is not valid [87].
I checked and both servers are configured to allow remote connections.
For the Access DB we set up a .DSN (after several failed attempts at other types of connections) and here is that code.
exec sp_addlinkedserver @server=N'AccessSchedule'
,@srvproduct=N''
,@provider=N'Schedule'
,@datasrc=N'LocalServer'
exec sp_addlinkedsrvlogin AccessSchedule,False,NULL,Admin,NULL
Select * from [Access].master.sys.databases;
with the result of the Select query.
Msg 7202, Level 11, State 2, Line 10
Could not find server 'Access' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Any help will be greatly appreciated.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o. -
Does your remote SQL Server capture failed logins in its errorlog? If so, do you see anything in there after you attempt to connect? Can you connect to the remote server by other means (SSMS, sqlcmd)?
Your Access query will fail for two reasons. First, the linked server is called AccessSchedule, not Access. And second, Access does not (as far as I know) have a master database.
John
Edit - I've just noticed "Connection string is not valid" in your error message. I think this is because you're using a connection string to a file, not a SQL Server. I don't often encourage this, but try creating the linked server in the GUI, but instead of pressing OK, press the Script button to generate a script to create the linked server.
-
since you've set up teh DSN and the linked server, do you get a list of tables from this command that you could then query?
exec sp_tables_ex [AccessSchedule]
you get results that looks something like this:
assume a table appeared named "TheSongsTable"
for access, i believe you would query it one of two ways:
SELECT * FROM [AccessSchedule]...[TheSongsTable]
SELECT * FROM OPENQUERY([AccessSchedule],'SELECT * FROM TheSongsTable')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Error for SQL server, is invalid connection string
\\srv-apps01\BCMMaster
are you trying to connect a named instance? then it should probably be srv-apps01\BCMMaster.
-
I think I have a configuration issue with the network.
BTW I have recreated the AccessSchedule linked server using..
exec sp_addlinkedserver @server=N'Access'
,@srvproduct=N''
,@provider=N'MSOLAP'
,@datasrc=N'SRV-APPS01'
,@Catalog='BrochsteinsMaster'
Now when I run exec sp_tables_ex @table_server='AccessSchedule'
I get:
OLE DB provider "MSOLAP" for linked server "AccessSchedule" returned message "The following system error occurred: No connection could be made because the target machine actively refused it. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 47
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "AccessSchedule".
I've only been here a couple of months and really don't deal with network issues so I will have to wait for my boss who does the hardware/network work to get here. Maybe we can open the correct port and this will then work.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o. -
I don't think that's to do with the network. The MSOLAP provider is for Analysis Services, not Access.
John
-
For Access I went back to my original setup.
exec sp_addlinkedserver @server=N'AccessSchedule'
,@srvproduct=N''
,@provider=N'Schedule'
,@datasrc=N'LocalServer'
exec sp_addlinkedsrvlogin AccessSchedule,False,NULL,Admin,NULL
Where Schedule is the name of a DSN file on the SQL server
Now when I run
exec sp_tables_ex @table_server='AccessSchedule'
I get
Msg 7403, Level 16, State 1, Procedure sp_tables_ex, Line 56
The OLE DB provider "Schedule" has not been registered.
We have installed both the Access runtime and 2007 Office system drivers. I have not been able to find a download specific to Microsoft.ACE.OLEDB.12.0 or .15.0 but they both appear as providers under my Linked Servers now.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o. -
I'm out of my depth now, I'm afraid. I have the immense good fortune very rarely to have worked with Access.
John
-
TexasJohn (6/7/2016)
For Access I went back to my original setup.exec sp_addlinkedserver @server=N'AccessSchedule'
,@srvproduct=N''
,@provider=N'Schedule'
,@datasrc=N'LocalServer'
exec sp_addlinkedsrvlogin AccessSchedule,False,NULL,Admin,NULL
Where Schedule is the name of a DSN file on the SQL server
Now when I run
exec sp_tables_ex @table_server='AccessSchedule'
I get
Msg 7403, Level 16, State 1, Procedure sp_tables_ex, Line 56
The OLE DB provider "Schedule" has not been registered.
We have installed both the Access runtime and 2007 Office system drivers. I have not been able to find a download specific to Microsoft.ACE.OLEDB.12.0 or .15.0 but they both appear as providers under my Linked Servers now.
that is not the right commands ot set up an access linked server.
there is no provider named "Schedule", as your error reports.
it should look something like this, assuming you've installed the ACE drivers from MicroSoft and set up a DSN:
--#################################################################################################
--Linked server Syntax for Access with ACE 64 driver
--#################################################################################################
EXEC sp_addlinkedserver
@server = N'MyAccessDB1',
@srvproduct=N'Access',
@provider=N'MSDASQL',
--@provider = N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'MS Access Database', --Name of DSN from odbc 64 administrator
@provstr=N'MSDASQL',
@catalog=N'c:\data\Music.accdb'; --pathname to accdb file
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'MyAccessDB1',
@useself = N'TRUE',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = NULL;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Changed code to the following...
/* Add the Access database to a named linked server */
EXEC sp_addlinkedserver
@server=N'AccessSchedule'
,@srvproduct=N'Access'
,@provider=N'MSDASQL'
,@datasrc=N'Schedule64'
,@provstr='MSDASQL'
--,@Catalog='srv-fs01\common\Departments\Master schedule Back End.mdb'
,@Catalog='Z:\Departments\Master schedule Back End.mdb'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'AccessSchedule'
,@useself=N'TRUE'
,@locallogin = NULL
,@rmtuser=N'Admin'
,@rmtpassword= NULL;
SELECT * FROM [AccessSchedule]...[Brochsteins Schedule]
Results of SELECT statement...
OLE DB provider "MSDASQL" for linked server "AccessSchedule" returned message "[Microsoft][ODBC Driver Manager] Invalid connection string attribute".
OLE DB provider "MSDASQL" for linked server "AccessSchedule" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "AccessSchedule" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "AccessSchedule" returned message "[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 25
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "AccessSchedule".
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o. -
mapped drives like the z: drive are per user.
if you run xp_fixeddrives, do you have a Z:\ drive?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Nope. C,D & E only. So there are two things going on here. The catalog is wrong and the DSN file Schedule64 was saved to the Z drive as well. I'll have to move that and try again.
the correct syntax for @Catalog is
,@Catalog='\\srv-fs01\Common\Departments\Master schedule Back End.mdb'
or
,@Catalog='srv-fs01\Common\Departments\Master schedule Back End.mdb' ? (I've seen both on the Internet)
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o. -
TexasJohn (6/8/2016)
Nope. C,D & E only. So there are two things going on here. The catalog is wrong and the DSN file Schedule64 was saved to the Z drive as well. I'll have to move that and try again.the correct syntax for @Catalog is
,@Catalog='\\srv-fs01\Common\Departments\Master schedule Back End.mdb' [highlight="#ffff11"]this one: the path must be a UNC path \\servername or a drive like E:\...[/highlight]
or
,@Catalog='srv-fs01\Common\Departments\Master schedule Back End.mdb' ? (I've seen both on the Internet)
so the issue is whether the account that SQL runs under can actually get to the database itself. remember when SQL accesses anything outside of a database, it uses an account that you might not expect...doesn't matter if you are domain admin or anything, it's the account the service runs under that tries to access external resources.
if you run this xp_cmdshell do you get an error or a listing or files?
exec xp_cmdshell 'dir \\srv-fs01\common\Departments\'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
TexasJohn (6/8/2016)
The catalog is wrong and the DSN file Schedule64 was saved to the Z drive as well.saved to file? i am pretty sure you need a system DSN and not a file DSN for your linked servers; you mean the path to the database?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Access is denied.
the DSN is a system DSN, sorry.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply