Accessing Remote Data  «Prev  Next»

Lesson 5Accessing remote data with a linked server
ObjectiveDescribe how to access remote data with a linked server.

Accessing Remote Data using linked Server

To access remote data in Microsoft SQL Server through a linked server, you must employ a fully qualified path in your SQL query. This method involves specifying the linked server's name, the database on the linked server, the schema, and finally, the specific object (like a table or view) that you want to query. Here is the general syntax and steps to follow:
  1. Syntax for a Fully Qualified Path in a Query: 
    SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName]

    Where:
    • `LinkedServerName` is the name of the linked server you have set up in SQL Server.
    • `DatabaseName` is the name of the database on the linked server.
    • `SchemaName` is the schema within the database (commonly `dbo` for default schema).
    • `TableName` is the name of the table (or view) you want to query.
  2. Example Query: Assuming you have a linked server named `RemoteServer`, a database named `RemoteDB`, a schema named `dbo`, and a table named `Employees`, your query would look like this: 
    SELECT * FROM [RemoteServer].[RemoteDB].[dbo].[Employees]

    This query selects all columns from the `Employees` table located in the `RemoteDB` database on the `RemoteServer` linked server.
  3. Executing the Query:
    • You can run this query in SQL Server Management Studio (SSMS) or any other query tool that connects to your SQL Server instance.
    • Ensure that your SQL Server instance has the appropriate permissions to access the linked server.
  4. Considerations:
    • Network and security configurations are crucial. The SQL Server instance must have network access to the linked server, and proper security credentials need to be configured for accessing the remote data.
    • Performance can be a concern when querying large datasets across linked servers. It's often advisable to minimize data transfer by filtering data as much as possible in the query.
    • Error handling and testing are important, as linked server queries can fail due to network issues, permission problems, or schema mismatches.

By following these steps and considerations, you can effectively query remote data in SQL Server using a linked server with a fully qualified path.

After Linking the Server

Once you link the server, accessing data is easier than you might think. All you have to do is write a query by using a fully qualified path.
A fully qualified path allows you to indicate to SQL Server where the query is located and who owns it so that SQL Server can locate the resources used in the query. A fully qualified path follows the syntax shown below:

Extract filename from fully qualified path

Given a varchar(255) column that contains the fully qualified path for a file. For example: 
C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyFile.txt

You need to break this data into the path, filename and extension. 
@Path = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP'
@Filename = 'MyFile.txt'
@Extension = 'txt'

Solution:
A double reverse will work here; Break the code it into a couple of steps to make the solution more obvious: 
declare @filename varchar(1000)
set @filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyFile.txt'
select
substring(reverse(@filename),1,charindex('\',reverse(@filename)) ), --txt.eliFyM\
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ), --\MyFile.txt
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) -1 ) ), --MyFile.txt,
--returns: C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
substring(@filename,1,charindex(reverse(substring(reverse(@filename),1,
charindex('\',reverse(@filename)) ) ),@filename) )

As an example of a relational database, in Microsoft SQL Server the fully qualified name of an object is the one that specifies all four parts: 
server_name.[database_name].[schema_name].object_name.

Fully Qualified Name

To distinguish a fully qualified name from a regular name, C++, Tcl, Perl and Ruby use two colons (::), and Java uses dots (.), as does Visual Basic .NET. and C#. In Java,and other object-oriented languages the use of the dot is known as "dot syntax". In Perl, a fully qualified scalar ($scalar) that is in the package package2 would be referred to as $package2::scalar
In the sense of domain names, a fully qualified domain name is explicitly suffixed with a dot, to eliminate the step of resolving, and to ensure that no additional suffix is added: sqlconfiguration.com. for instance. This is an example of a fully qualified domain name.

Pass-through distributed queries

A pass-through query executes a query at the external data source and returns the result to SQL Server. The primary reason for using a pass-through query is to reduce the amount of data being passed from the server (the external data source) and the client (SQL Server). Rather than pull a million rows into SQL Server so that it can use 25 of them, it may be better to select those 25 rows from the external data source. Be aware that the pass-through query will use the query syntax of the external data source. If the external data source is Oracle or Access, then PL/SQL or Access SQL must be used in the pass-through query. In the case of a pass-through query that modifies data, the remote data type determines whether the update is performed locally or remotely:
  1. When another SQL Server is being updated, the remote SQL Server will perform the update.
  2. When non.SQL Server data is being updated, the data providers determine where the update will be performed. Often, the pass-through query merely selects the correct rows remotely. The selected rows are returned to SQL Server, modified inside SQL Server, and then returned to the remote data source for the update.
Two forms of local distributed queries exist, one for linked servers and one for external data sources defined in the query; likewise, two forms of explicitly declaring pass-through distributed queries exist as well. OpenQuery() uses an established linked server, and OpenRowSet() declares the link within the query.
In the next lesson, you will practice accessing remote data.