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

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.

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.