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.
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'
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'
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\
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:
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.