title: How to use BCP to dump query (cdc function ) retrieved data to text file
body: <p>Im trying to use BCP to dump data from CDC function into a .dat file. Im using the following query (which works in Server 2008 R2):</p>
  
  <pre><code>USE LEESWIJZER
  
  DECLARE @begin_time datetime
  , @end_time datetime
  , @from_lsn binary(10)
  , @to_lsn binary(10)
  
  SET @end_time = '2013-07-05 12:00:00.000';    
  
  SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',  @end_time);
  SELECT @from_lsn = sys.fn_cdc_get_min_lsn('dbo_LWR_CONTRIBUTIES')
  
  SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS ChangeDTS
  , *
  FROM cdc.fn_cdc_get_net_changes_dbo_LWR_CONTRIBUTIES (@from_lsn, @to_LSN, 'all')
  </code></pre>
  
  <p>(edited for readability, used in BCP as single string)</p>
  
  <p>my BCP string is:</p>
  
  <pre><code>BCP "Query above" queryout "C:\temp\LWRCONTRIBUTIES.dat" -w -t ";|" -r \n -T -S {server\\instance} -o "C:\temp\LWRCONTRIBUTIES.log"
  </code></pre>
  
  <p>As you can see I want a resulting .dat file in unicode, and a log file. I'm guessing the "ChangeDTS" column added to the function outcome is causing my problem. Error message reads: "[Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server". </p>
  
  <p>It may be resolved using a format file, but since this code needs to run daily, likely more than once a day, and the tables are subject to change, I'm reluctant to constantly adjust my format files (there are 100's of tables needing the same procedure). </p>
  
  <p>Furthermore, this is run on a clients database, who wont like me creating views in their database. </p>
  
  <p>Anybody got any idea how I can create a text file (.dat) with a selected number of columns from a cdc function?</p>