Working at DataServer1
Creating a linked server to DataServer2, which has the default SQL Server instance installed.
EXEC sp_addlinkedserver
@server='Linked_to_DataServer2',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='DataServer2'
Or,
EXEC sp_addlinkedserver @server='DataServer2', @srvproduct='SQL Server'
====
If you have a named instance:SQLExpress installed, you could create a linked server as:
EXEC sp_addlinkedserver @server='Linked_to_DataServer2_Express', @srvproduct='', @provider='SQLNCLI', @datasrc='DataServer2\SQLExpress'
Or,
EXEC sp_addlinkedserver @server='DataServer2\SQLExpress', @srvproduct='SQL Server'
Notes: make sure that SQL EXPRESS accepts connection remotely.
===
How to create a linked server to ORACLE database?
- Provider Name:MSDAORA --Microsoft OLE DB Provider for Oracle
- SQL*Net alias for Oracle database --how to?
Oracle home--Net8 Assistant --Net8 Configuration > Local > Service Naming
- Enter a Net Service Name:MyOracle
- Select TCP/IP as the protocol
- Enter the hostname of the database server machine. The port number will be 1521 by default.
EXEC sp_addlinkedserver
@server='DataServer2 Oracle',
@svrproduct='oracle',
@provider='MSDAORA',
@datasrc='MyOracle'
===
If you connect to an access database (C:\msoffice\test.mdb), you run the following:
EXEC sp_addlinkedserver
@server = 'Connect_to_MyAccess',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\test.mdb'
GO
Explicit User Mapping
You cannot create a user mapping for a domain account.
EXEC sp_addlinkedsrvlogin 'Norton','FALSE','VANSTUDENTS\TEST','VANSTUDENTS\TEST','password'
EXEC sp_droplinkedsrvlogin 'Norton','VANSTUDENTS\TEST'
You should map a domain user to a SQL Server account in the other SQL Server. The following statement works fine for the distributed query, as long as the User alice has the necessary permission on the database objects.
EXEC sp_addlinkedsrvlogin 'Norton','FALSE','VANSTUDENTS\TEST','alice','alice'
