data content comparison

Hello:
I'm looking for a free tool or procedures
to compare data content of two tables
which belongs to different servers.
Each table does not have any indexes or primary key. I need this as
verification procedures for my
log shipping process.
Thanks,
GBTake a look at Red Gate SQL Data Compare
(http://www.red-gate.com/products/SQ...mpare/index.htm)
http://sqlservercode.blogspot.com/|||I ask it for FREE.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135192364.052961.258020@.z14g2000cwz.googlegroups.com...
> Take a look at Red Gate SQL Data Compare
> (http://www.red-gate.com/products/SQ...mpare/index.htm)
> http://sqlservercode.blogspot.com/
>|||when you say "each table does not have any indexes or primary key" do
you mean:
-- No tables have indexes/pks or
-- Some tables do, but not all of them
and
-- They don't have a pk [or unique?] constraint defined, but they do
have a logical pk or
-- They don't even have a logical pk defined
if it's that they don't even have a logical pk defined, then how would
you know what you're comparing? you'd have to be able to determine what
data constitutes being the same between the two similar tables on
different servers. [an identity/guid is most likely irrelevant in this
situation, as the chances that two tables in different servers have the
same identity value for the same data is extremely small]
even if it's that the pk (or unique?) constraint is not defined, then
you still might have duplicates within a singe table.
most comparison tools i've seen pull the pk dynamically, so if it's not
defined, you'll also need a tool that lets you specify the pk column(s).
GB wrote:
> Hello:
> I'm looking for a free tool or procedures
> to compare data content of two tables
> which belongs to different servers.
> Each table does not have any indexes or primary key. I need this as
> verification procedures for my
> log shipping process.
> Thanks,
> GB
>
>|||Some tables do, but not all of them
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:up2tgcmBGHA.2040@.TK2MSFTNGP14.phx.gbl...
> when you say "each table does not have any indexes or primary key" do
> you mean:
> -- No tables have indexes/pks or
> -- Some tables do, but not all of them
> and
> -- They don't have a pk [or unique?] constraint defined, but they do
> have a logical pk or
> -- They don't even have a logical pk defined
> if it's that they don't even have a logical pk defined, then how would
> you know what you're comparing? you'd have to be able to determine what
> data constitutes being the same between the two similar tables on
> different servers. [an identity/guid is most likely irrelevant in this
> situation, as the chances that two tables in different servers have the
> same identity value for the same data is extremely small]
> even if it's that the pk (or unique?) constraint is not defined, then
> you still might have duplicates within a singe table.
> most comparison tools i've seen pull the pk dynamically, so if it's not
> defined, you'll also need a tool that lets you specify the pk column(s).
>
> GB wrote:|||>I ask it for FREE.
There is no FREE, one-click, easy button -- not even Staples has one of
those. So, you can invest the time in writing queries tailored to your
schema, or you can invest the money in a product that does the work for you.

Data comparison in crystal reports?

how to compare dates in crystal reports' selection formula.

I want to apply selection criteria on my crystal report.
Report is binded to a data view.
and i m doing like this

CRViewer.SelectionFormula = "{v_advances.creationDate} > '2/2/2004'"

v_advances= database view name
creationDate= view's field's alias name

and its not working.

kindly tell me; how to compare dates in selection formula of crystal report.Use Date(yr,mon,day) format

i.e, {table.field} < Date(2004,4,1)

Otherwise pass a date parameter, check with that parameter name.

Hope this will work|||its not working :(

what if i compare the view field with a parameter?

how to compare that.

:confused:|||Hi,

I'm not clear. What's view field. Is it ur database field. If it's use the folg.

{table.field} > {?Date}|||field is alias of db field in view.|||Then simply select the field, operator(like >,<),parameter field(Should be date datatype)

Data comparison and update

Hello All,

I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?

Are you duplicating the T1 data into T2? If so, why not simply delete all T2 rows and insert all T1 rows into T2 (or drop T2 and then recreate it from T1, including data)?|||

Hello,

Thanks you very much for the reply. T1 is big and is changing constantly and I am trying to find the discrepancy between T1 and T2 and update T2 based on the discrepancies. SO trying to realize synchronization on a single table. Any idea?

|||I haven't used Triggers in a long time, but it may be a good solution to your situation. Set up the Triggers for UPDATE, INSERT and DELETE operations and have it synchronize your T2 table accordingly. Once the Triggers are defined (and tested), you don't even have to worry about it. Be sure that performance isn't hit by doing this, though.|||

Sorry Jim, wish I could help you, but I'm currently bound by some confidentiality agreements that prohibit me from discussing this in much detail. But here are some choices:

Use a trigger to update t1 whenever t2 is updated (If you need them to be synchronized in realtime, including transaction consistancy). But since they are the same structure, there is usually little reason for implementing it this way.

Replication. You can use this to replicate data from server to server, and probably from table to table as well. There are so many options on how this can be done, it'll take you a while to research and test the possibilities.

Diff-gramming. Write queries to automatically insert, update, and delete those rows which differ from table1 to table2.

Data comparision of different data bases in crystal report 8.5

I have a requirement to compare the data from different data bases ( Oracle , Sybase, DB2) and hight the data if its different then Oracle data.
We have the same data in Oracle ,sybase and DB2. I now need to connect to all these DBs and get the data and then compare them and provide hight light if the data is different.

Can any expert provide me the best way of doing in crystal reports 8.5
I am trying to use the formula field .But don't know how to format the data and dispay with bold in crystal function.Link the databases. Then, if you want to show only the records with mismatched data fields, in your select formula pull only those records where the field(s) you are comparing are not equal. However, if you want to pull (and list) all the records but highlight only the mismatches, use highlighting.

DATA COMPARE Question??

I have two tables as:
table1
tablename tablecount
a 1
b 2
c 3
d 4
.
.
table2
tablename tablecount
a 1
b 2
c 10
d 11
.
.
.
How do I write a SP to do a COMPARE and give me the table names that are
different along with count differences.
Thanks,
TomdThis can basically be done by joining table1 with table2 and selecting where
tablecount <> tablecount.
select table1.tablename, table1.tablecount - table2.tablecount as difference
from table1
join table2 on table2.tablename = table1.tablename
where table1.tablecount <> table2.tablecount
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:C2BB644B-1849-45AF-A72A-38A5E105F8F9@.microsoft.com...
>I have two tables as:
> table1
> tablename tablecount
> a 1
> b 2
> c 3
> d 4
> .
> .
> table2
> tablename tablecount
> a 1
> b 2
> c 10
> d 11
> .
> .
> .
> How do I write a SP to do a COMPARE and give me the table names that are
> different along with count differences.
> Thanks,
> Tomd
>|||Tom,
Try:
SELECT A.TABLENAME, A.TABLECOUNT AS 'COUNT OF TABLE1',B.TABLECOUNT AS
'COUNT OF TABLE2'
FROM TABLE1 A JOIN TABLE2 B
ON A.TABLENAME = B.TABLENAME
WHERE A.TABLECOUNT <> B.TABLECOUNT
HTH
Jerry
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:C2BB644B-1849-45AF-A72A-38A5E105F8F9@.microsoft.com...
>I have two tables as:
> table1
> tablename tablecount
> a 1
> b 2
> c 3
> d 4
> .
> .
> table2
> tablename tablecount
> a 1
> b 2
> c 10
> d 11
> .
> .
> .
> How do I write a SP to do a COMPARE and give me the table names that are
> different along with count differences.
> Thanks,
> Tomd
>

Data Compare

I have and use Red Gate software for comparison. However; I'm at a loss on
this data compare scenario:
I have two tables I want to compare, but the PK-ID field is seeded
differenly between the 2 tables. So when I do a comparison - every row is
considered different. I looked at a couple other tools to see if I could
compare and exclude columns - but havent' found any. I remember once seeing
a T-SQL solution - but I cannot find that either.
One thought I had was that this particular table, the PK-ID field is not
used anywhere else - so maybe I could just reseed the one table to match the
other.
Can anyone offer some suggestions?It is hard to understand your narrative without an example. Can you post
your table structures, sample data & expected results? For details refer to:
www.aspfaq.com/5006
Anith|||I found the perfect solution just now a SP called sp_compare:
http://www.databasejournal.com/scri...cle.php/1579951
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23eTMaogHGHA.3624@.TK2MSFTNGP09.phx.gbl...
> It is hard to understand your narrative without an example. Can you post
> your table structures, sample data & expected results? For details refer
> to: www.aspfaq.com/5006
> --
> Anith
>

Data Compare

I have and use Red Gate software for comparison. However; I'm at a loss on
this data compare scenario:
I have two tables I want to compare, but the PK-ID field is seeded
differenly between the 2 tables. So when I do a comparison - every row is
considered different. I looked at a couple other tools to see if I could
compare and exclude columns - but havent' found any. I remember once seeing
a T-SQL solution - but I cannot find that either.
One thought I had was that this particular table, the PK-ID field is not
used anywhere else - so maybe I could just reseed the one table to match the
other.
Can anyone offer some suggestions?
That would sort of defeat the purpose. The PK is supposed to uniquely
identify a row. If there is no way to uniquely identify a row, then how can
any comparison tool compare any row to any other row?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Joe" <bill@.msn.com> wrote in message
news:OgjKPRgHGHA.216@.TK2MSFTNGP15.phx.gbl...
>I have and use Red Gate software for comparison. However; I'm at a loss on
>this data compare scenario:
> I have two tables I want to compare, but the PK-ID field is seeded
> differenly between the 2 tables. So when I do a comparison - every row is
> considered different. I looked at a couple other tools to see if I could
> compare and exclude columns - but havent' found any. I remember once
> seeing a T-SQL solution - but I cannot find that either.
> One thought I had was that this particular table, the PK-ID field is not
> used anywhere else - so maybe I could just reseed the one table to match
> the other.
> Can anyone offer some suggestions?
>