Linked Servers allows us to submit a TSQL statement on one SQL Server instance, which retrieves data[Table\View] from a different SQL Server instances.
In fact, linked server can be used to join data from multiple SQL Server instances using a single TSQL statement.
When you have databases on multiple SQL Server instances, you might find it useful to use linked servers in your application to retrieve data from more than one instance.
By using a linked server your application will only need to connect to one SQL Server instance to retrieve data from multiple SQL Server instances.
On that single SQL Server instance, we would define linked servers so your application could retrieve data from the databases that reside on a different SQL Server instance.
Advantages of Linked Servers:
•The ability to access data from outside of SQL Server.
•The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
•The ability to address diverse data sources similarly.
Linked Servers work flow:
Linked server is a mechanism that allows a query to be submitted on one server and then have all or part of the query redirected and processed on another SQL Server instance, and eventually have the results set sent back to the original server to be returned to the client.
When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB.
For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
Querying a Linked Server:
Way1:By using fully qualified, four-part anme.
Ex: Select * from linked_server_name.DBName.schema.object_name.
Way2: By using Open query
Ex: SELECT * FROM OPENQUERY(linked_Server_Name, 'SELECT * FROM DBname.Schema.Object_Name')
How to execute remote server stored procedure by using linked server
SELECT * FROM OPENQUERY([linked_server_name],DatabaseName.DatabaseOwner.StoredProcedureName)
In fact, linked server can be used to join data from multiple SQL Server instances using a single TSQL statement.
When you have databases on multiple SQL Server instances, you might find it useful to use linked servers in your application to retrieve data from more than one instance.
By using a linked server your application will only need to connect to one SQL Server instance to retrieve data from multiple SQL Server instances.
On that single SQL Server instance, we would define linked servers so your application could retrieve data from the databases that reside on a different SQL Server instance.
Advantages of Linked Servers:
•The ability to access data from outside of SQL Server.
•The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
•The ability to address diverse data sources similarly.
Linked Servers work flow:
When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB.
For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
Querying a Linked Server:
Way1:By using fully qualified, four-part anme.
Ex: Select * from linked_server_name.DBName.schema.object_name.
Way2: By using Open query
Ex: SELECT * FROM OPENQUERY(linked_Server_Name, 'SELECT * FROM DBname.Schema.Object_Name')
How to execute remote server stored procedure by using linked server
SELECT * FROM OPENQUERY([linked_server_name],DatabaseName.DatabaseOwner.StoredProcedureName)
No comments:
Post a Comment