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


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
Who is Participating?
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
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...
JojodyneAuthor Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.