How can I bcp a table out, ignoring one or more columns?

Posted on 2008-11-08
Medium Priority
Last Modified: 2012-05-05

I'm in the process of verifying the contents of replicated tables in ASE12.5. Sometimes, the source and the target table will have identical rowcounts, and the bcp out fles will have identical filesizes. It is still possible however that a source table will contain a 'Y' in a certain field, amd the target table a 'N' in the same field. In that case, both rowcount and bcp out filesize will be identical, but there will still be a difference in the tables.

We have about 2100 replicated tables, the largest one being over 20M rows. I considered several alternatives for checking the consistency, and decided the bcp out file comparison was the easiest, and fastest. Comparing the tables in TSQL is really unpractical, bordering on impossible, especially since the source and target tables reside on different servers. Proxy database could help, but it would be ever so slow.

So I decided to compare bcp out filsizes, and add an md5 check for good measure. I then hit upon the timestamp field, which is not replicated, and therefore varies. Hence different checksums, and no certainty that the tables are identical.

The only other alternative is manually resyncing all the tables, but that;s even more work.

So, can I bcp out a table, omitting the timestamp field?

Many thanks for any suggestions

Johan Genbrugge
Question by:Jojodyne
  • 2
LVL 24

Accepted Solution

Joe Woodhouse earned 2000 total points
ID: 22914559
You cannot directly exclude columns when bcping out of a *table*.

You can however create a view which is a SELECT of the columns you want, and then bcp out of the *view*.

Have you looked into the rs_subcmp tool that ships as part of RepServer? With proper definitions and properly indexed tables its performance isn't... too awful. I was told a while back that one of the RepServer futures involved a version of rs_subcmp that internally used md5 and it had been benchmarked as being a lot faster. You might be able to use a 15.x rs_subcmp even with the older versions of ASE and RS.

However that's a bit of work to set up. For what you need, views and bcp from the views. You could even script that - build the view programatically to exclude timestamp columns...

Author Closing Comment

ID: 31514747
I feel a bit stupid for not thinking of the views! that would of cource work, and can easily be scripted. I'll also look into the rs_subcmp SP which I didn't know existed. I might work very well for the smaller tables! Great answer! Many thanks
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 22915666
rs_subcmp is actually an O/S tool, not a stored procedure, you'll find it under the REP% directory. Definitely read up on it first!! ::

Good luck!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question