Jojodyne
asked on
How can I bcp a table out, ignoring one or more columns?
Hi,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
Good luck!
ASKER