Aylık arşivler: Mayıs 2014

Bir SQL Server üzerine başka sql server tanıtma & sp_addlinkedserver

Dear readers, sometimes there is a need to make common queries through two different sql servers while working in an environment where there are multiple sql servers. This is sometimes happening to me, and I’m solving my job by connecting the other sql server to one of the existing sql servers. If there is a difference in version between the two servers, it is correct to connect to the lower version. This can be done in several different ways, depending on the version of SQL Server used.
If you want, you can connect another sql server from New Linked Server by right-clicking on Linked Servers under Server Objects on SQL Server management studio.
The following example usually works for those who want to do it with code like me

A new SQL Server connection is added to an existing SQL server using the Sp_Addlinkedserver and Sp_Addlinkedsrvlogin procedures.

exec sp_addlinkedserver @server='NewSqlServer', @srvproduct='SQL Server'
exec sp_addlinkedsrvlogin @rmtsrvname='NewSqlServer', @locallogin=NULL, @useself='False', @rmtuser='sa', @rmtpassword='123'

 

After these commands work successfully, you can type the name of the sql server before the DB names for the guest server in your queries. If there is a problem, try typing ip on the server name.

Sample;
Select MusteriKodu from MYDB.dbo.MUSTERILER A
LEFT JOIN  NevSqlServer.MYDB.dbo.MUSTERILER B
ON A.MusteriKodu=B.MusteriKodu

Thus, we have made a common query on two different server databases on a single server.

For more information check out Cashity.co.uk Payday