Linked Server

What is a Linked Server?

Linked servers are configured to enable the Database Engine to execute a SQL statement
that includes tables in another instance of SQL Server, or another database product such as Oracle or ACCESS.

Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

Linked servers offer the following advantages:

The ability to access data from outside of SQL Server.

The ability to issue distributed queries, updates, commands, and transactions on
other data sources .


How to create a Linked Server in SQL

1. Connect to sql server through SSMS
2. Right click Linked Server under Server Objects and Select New Linked Server option
3. Give it a name, choose Server Type as SQL server
4. Give instance name of Linked Server
5. Select Security option and Be made using this security option, add Remote login and Password
6. Select OK
7. Under the Catalog, you can see the databases created


You can also create a linked server definition by running sp_addlinkedserver.

We can get details of linked servers by these procedures
exec sp_linkedservers

or

select * from sys.servers

Oracle versus Sql Server

Description	Oracle	SQL Server
Format Date	to_date(dtt_CreatedDate,'DD-MON-YYYY') as dtt_CreatedDate,	Convert(varchar,dtt_CreatedDate,106) as dtt_CreatedDate,
Null to number	NVL(int_UserId,0) as int_UserId,	isnull(int_UserId,0) as int_UserId,
Current date	SYSDATE	GETDATE
Get substring from string	SUBSTR	SUBSTRING
Find string in string	INSTR	CHARINDEX
Concatenate string	CONCAT(str1,str2)	Str1 + str2
	if intID=0 then	if @intID=0