Linked Server is one of the cool features of Microsoft SQL Server especially for those who want to access remote database, remote database could be of any type SQL Server, Sybase, Access etc. you can access those databases with OLEDB provider. Let us see how can we do it?
Before you move further I would like to request my readers that please create one Access MDB file named “Testing.MDB” and create at least one table named “empDetails” in that MDB.
Now, we will link “Testing.MDB” database to SQL Server 2005.
Note: You can do it by wizard and by query but I prefer query so will show you that path only.
Below query will cover file steps to link the server.
1.) Add linked server
2.) Add linked server credential, if any
3.) Check sys.servers whether database is added as linked server
4.) List all available tables in MDB file
5.) Perform SELECT on “empDetails” table.
--add link server
exec sp_addlinkedserver
@server='LinkToAceess',
@srvproduct='AccessDatabase',
--if you have older version of access then kindly use old jet provider
--Microsoft.Jet.OLEDB.4.0
@provider='Microsoft.ACE.OLEDB.12.0',
@datasrc='C:\Testing.mdb'
GO
--add MDB's credental, if any
EXEC sp_addlinkedsrvlogin 'LinkToAceess', 'false'
--check whether 'LinkToAccess' has been added
select * from sys.servers
--list all tables available in Testing.MDB
exec sp_tables_ex 'LinkToAceess'
--perform SELECT on empDetail table.
SELECT * FROM [LinkToAceess]...empDetails
So, finally you have used Linked Server from within your SQL Server
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
4 comments:
Does not work.
Gives errros message:
Msg 7302, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server ....
which Access version are you using?
I couldn't get it to work on SQL 2008:
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToAceess".
Access 2003. I was able to get it to work on SQL 2005 but not on SQL 2008.
Post a Comment