Lined Server in SQL Server Management Studio
What is Linked Server ?
Linked Servers are a method by which a SQL Server can communicate with another ODBC compliant database, such as another SQL Server instance or an Oracledatabase, or PostgresQL with a direct T-SQL query.
For Example
Our Client has two WebApplication Website A and Website B. Each has different Databases in Different Servers SQL Server and Postgresql.
Website A is connected to SQL Server and Website B is connected to PostgreSQL.
SQL server needs to connect with PostgreSQL to generate a particular report in website A. This can be done by Linked Server.
How to start Linked Server
Linked Server Concept with PostgreSQL.
Install ODBC driver for PostgreSQL.
Configure the PostgreSQL server in ODBC Data Source(64 Bit)
SelectSystem DSN
Click Add button and fill Postgresql server details.
Choose PostgreSQL ODBC Driver and Click Finish
Fill the all fields
Data Source is default value
Enter Database Name
Enter Server Name or IP of Server
Enter User Name
Enter Port
Enter Password
Click Save button
Click Ok button.
In Object Explorer of SQL Server in Management Studio, Look for a Server Objects. Under Server Objects Linked Server is available.
Right Click the Linked Server and Choose New Linked Server…
New Popup for Linked Server opens
In General Section
Enter the Name for Linked Server.
Choose Other data source in Server Type
In Provider Choose Microsoft OLE DB Provider for ODBC Drivers
Enter Product Name :
Enter Data Source created in ODBC driver system DSN
Enter Catalog (Database Name), it is optional
In Security Section
Choose Be made using this security Context:
Enter user Name and Password for PostgreSQL server
In Server Options
Choose like above.
Click OK button
Now Linked Server Created
Open Query Window in SQL Server
Server Name : OFFICESERVER (linked server name)
Database : NewReXXXXXXXXX
Tablename : brandmaster
SELECT * FROM OFFICESERVER.NewReXXXXXXXXX.[public].brandmaster
Thanks
