x

How to use BCP to dump query (cdc function ) retrieved data to text file

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):

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')

(edited for readability, used in BCP as single string)

my BCP string is:

BCP "Query above" queryout "C:\temp\LWRCONTRIBUTIES.dat" -w -t ";|" -r \n -T -S {server\\instance} -o "C:\temp\LWRCONTRIBUTIES.log"

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". It may be resolved using a format file, but since this code needs to run daily, likely more than once daily, and the tables are subject to change, im reluctant to constantly adjust my format files (there are 100's of tables needing the same procedure). Furthermore, this is run on a clients database, who probably wont like me creating views in their database. . Anybody got any idea how I can create a text file with a selected number of columns from a cdc function?

cheers Wouter
more ▼

asked Aug 09, 2012 at 02:00 PM in Default

wouterp gravatar image

wouterp
0 3 3 3

Make sure you are using the correct BCP version. The same error message is going to be prompted if you are running the BCP of lower version e.g. SQL 2005's 9.00.5000.00 version. You can check the version through this command

bcp -v
The workaround is to either make the correct version-ed BCP as default OR change the directory to the respective directory of the SQL Instance e.g. For SQL 2008 it could be DEFAULDIRECTORYTPATH\100\Tools\Binn and then run the same command.
Aug 10, 2012 at 05:46 AM Usman Butt
Thanks Usman, however, while i have 2 versions of Server, these are 2008R2 and 2012. Confusingly, when i check bcp's version nr (apparently server2008r2's version is standard used), turns out to be the version you mentioned, 9.00.5000.00. When i call bcp from server 2012 directory (or from the 2008r2 directory for that matter), BCP doesnt return any error message (that is to say, no log file containing the error message is generated). When i use the argument -V110, i get the error message "Invalid format file version", when I use -V100, again no log file is generated. Any ideas? Wouter
Aug 10, 2012 at 07:57 AM wouterp
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Found the answer, regardless of which version of bcp used (still not sure why calling any version didnt work), bcp cant handle declarations, it seems. If i edit those out, works like a charm.
more ▼

answered Aug 10, 2012 at 08:27 AM

wouterp gravatar image

wouterp
0 3 3 3

@wouterp Can you please explain what is meant by the declarations here? If you meant declaring variables, then I guess it is incorrect. BCP can handle it. But in such situations I always use stored procedures to start with.
Aug 10, 2012 at 10:33 AM Usman Butt
thats correct, i did mean the declarations of variables. If BCP can handle it, im wondering why it didnt work with the declares, but does work when i get rid of them (now im using C# for the variable declarations). Stored procedures are of course the easy way to go, but in this particular case im stuck with a client who doesnt want me to add anything to their database, even stored procs.
Aug 13, 2012 at 07:44 AM wouterp
@wouterp It may be something with your configuration that was not allowing it OR a problem like that. But I was against the general statement that BCP cannot handle the declared variables which could be misleading to the future users. Statements like sometimes they would not work with BCP OR better way is to handle them in a stored procedure seems more valid ;) But glad to know you solved your problem yourself :)
Aug 15, 2012 at 10:30 AM Usman Butt
@Usman Butt, Fair enough :P Thanks for your comments anyway
Aug 15, 2012 at 12:04 PM wouterp
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x42
x18

asked: Aug 09, 2012 at 02:00 PM

Seen: 1588 times

Last Updated: Aug 15, 2012 at 12:04 PM