Lesson 9

This module discussed the usefulness of stored procedures and how to use them. Having completed this module, you should now be able to:

1. Set up servers for remote data
2. Execute remote stored procedures
3. Construct and execute queries from remote data sources

This module introduced you to the following terms:

1. Fully qualified path
2. Linked server
3. OLE DB

In the realm of SQL Server 2019, a "fully qualified path" serves as a precise navigational guide, akin to a meticulously detailed map in the vast universe of databases. Its primary purpose is to unambiguously identify a specific entity, such as a table or a view, in a landscape where numerous databases, schemas, and servers coexist. Let's dissect the anatomy of a fully qualified path. It resembles a well-structured sentence, each part carrying distinct significance:

1. Server Name: The opening chapter, it denotes the server hosting the SQL instance. This is the broadest level, akin to specifying a country in a geographical address.
2. Database Name: A level deeper, this is akin to a city within that country. It specifies the particular database within the server where our sought-after entity resides.
3. Schema Name: Further narrowing down, much like a district within a city, the schema name categorizes objects within the database. It's a layer of abstraction, often reflecting organizational structures within the database.
4. Object Name: The final destination, this is the specific table, view, or stored procedure you're seeking, akin to a street address in our geographical analogy.

A fully qualified name, therefore, might look like this:

`[ServerName].[DatabaseName].[SchemaName].[ObjectName]`.

In the context of accessing remote servers, a fully qualified path ensures precision and clarity. It eliminates any ambiguity, especially in complex environments where multiple databases might have similarly named tables or objects. Just as a traveler would need exact coordinates to navigate to a remote, obscure location, a SQL query requires a fully qualified path to accurately locate and interact with data across linked servers.
The fully qualified path in SQL Server 2019 is a beacon of clarity in the potential chaos of database environments. It provides a clear and unambiguous route to data, ensuring that operations are performed on the correct object, in the correct schema, within the correct database, and on the intended server. This precision is crucial in maintaining the integrity and efficiency of database operations, especially when venturing into the realm of remote server access.

BCP, short for bulk copy program is a command-line variation of bulk operations. BCP differs from BULK INSERT in that it is command-line executed and can import or export data. It uses many of the same options as BULK INSERT. The basic syntax is as follows:

BCP destination table direction datafile options

For the destination, use the server name along with the complete three-part name (server and database.schema.object). For a complete listing of the syntax, just type BCP at the command prompt. Because this is an external program, it needs authorization to connect to SQL Server. You have two options: Use the -P password option and hard-code your password into the batch file script, or omit the -P, in which case it will prompt for a password. Neither is a very good option. You can also use integrated security, which is usually considered the best practice.
Note: For straightforward ETL operations, I prefer using T-SQL and BULK INSERT. For complex ETL loads, Integration Services is great. To be frank, I have little use for automating ETL processes using DOS batch scripts and BCP, although Powershell may make a believer of me yet.
In the next module, learn about bulk copying data with Bulk Copy Program (BCP) and Data Transformation Services (DTS).
Do you have any questions or comments about accessing remote data? If so, click the Search button located at the top of the page.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Remote Data Sources - Quiz