Link server under Server Objects in Sql server

While working on the different applications. Some times we need to link other servers with our local server so that we can execute some queries. We can link any online server with our local sql server or one VPS to another VPS. Even with this we can link Azure Sql server to any one.

Here are the queries which will help us to link the Server. 

EXEC sp_addlinkedserver

@server='NameOfServer',  

@srvproduct='',

@provider='sqlncli',

@datasrc='Server e.g 192.168.1.1,1433',

@location='',

@provstr='',

@catalog='Database Name'

EXEC sp_addlinkedsrvlogin

@rmtsrvname=' NameOfServer ',

@useself='false',

@rmtuser='User Name',

@rmtpassword='Password'

EXEC sp_serveroption ' NameOfServer ', 'Collation Compatible', true;

The above queries will help to link the server and linked server will be showing under Server Object of your sql server.

 

For executing the queries you can use the simple query method, but if your tables having foreign key relation then you have to execute you query as given below. 

declare @sql as nvarchar(max) =  'SET IDENTITY_INSERT info ON ' +

'INSERT into info ([Id], [FirstName], [LastName], [Description], [Note]) VALUES (2, N''Harsh'', N''Gupta'', NULL, N''Test1'') ' +

'SET IDENTITY_INSERT info Off'

EXEC [ServerName].DatabaseName.dbo.sp_executesql @sql

 

We have to create a simple string of all the queries and then we have to execute with the help of sp_executesql on linked server.