Tip: Beware of Using the QUERYOUT Option with Command-Line Bcp

Bulk copy program (bcp) lets you copy data out of SQL Server based on the result set of a query that's specified on the bcp command line. The Q&A "Does the SQL Server 7.0 version of bulk copy program (bcp) let you create an output file from a query without having a VIEW to put the result set in?" August 1999, InstantDoc ID 5867, explains how you can use the QUERYOUT option to accomplish this kind of data copying. But a SQL Server Magazine reader recently told me about a bug in bcp that forces the query specified in QUERYOUT to execute twice. (For a full description of this bug, see the Microsoft article "BUG: Bcp.exe Executes Query Twice When QueryOut Argument Used" at http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;q309555.)

Of course, executing a query twice is inefficient, especially when you're dealing with large data sets. You can still selectively bcp out data by defining a view in SQL Server that encapsulates the query you'd otherwise specify with the QUERYOUT option. You'll get the same output data set, but the query won't run twice.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish