asked
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
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.
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
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.
@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.
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.
@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 :)
8 People are following this question.
