Accessing Remote Data  «Prev  Next»

Lesson 9

SQL-Server Stored Procedure Conclusion

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

Glossary terms

This module introduced you to the following terms:
  1. Fully qualified path
  2. Linked server
  3. OLE DB

BCP - bulk copy program

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.

Remote Data Sources - Quiz

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