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 Question

I am not sure if this is the correct forum for my question but I will give it a shot. I want to be able to create a dataset from an ODBC datasource and compare it to existing data in my SQL DB. If there are changes (inserts/updates/deletes) I want to raise seperate events for each row state. Is this possible to do with SSIS/DTS or am I on the wrong track with what I am trying to do?

Have a look at this and see if its any help: http://www.sqlis.com/default.aspx?311

-Jamie

|||Thanks for the link it looks like really good information. Do you think that this will work for all three row states (new/updated/deleted)?|||anybody?|||

These methods tells you whether an incoing row is there or not. i.e. Is it an insert or an update.

It does NOT tell you if a row has been deleted from source. For this you will have to do...well... the opposite.

-Jamie

|||Thank you very much you have been excellent help I was able to do exactly what I needed. For updates should it work the same or do I have to run a check on a particular column(s) and see if they match?|||

The article I linked to should have all the answers. You should use method 2 in there by the way.

-Jamie

|||ok thanks again

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 Comparison

I have an Employee table with 3000 records and an Excel file having the
modified data of those emplyoees. Some of the data of Excel may be same
as that of table data but some may differ. EmpId is the unique field.
Other than this field, other fields of Excel may have modified data.I
need to compare the data from SQL Server table with Excel Data.
I decided to write a VB Program having two recordsets,one for SQL
Server and other for Excel and compare each field's value. If the
modified value is found then update that to table. Is there any way to
compare in SQL Server itself?

MadhivananProbably the easiest way is to create a second Employee table with the
same (or similar) structure as the current one. You can load the new
data into it with DTS or bcp.exe, and it's then easy to compare
whatever you want with SQL queries. This is quite a common general
technique for importing data - load into a staging table, check/clean
the data, then INSERT/UPDATE the destination table.

There are other options too, such as creating a linked server pointing
at the .xls file, but I would say that loading the data is probably the
simplest.

Simon|||Yes, I already decided to create new table with same structure and
export Excel to SQL Server table. Thanks

Madhivanan

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?
>

CY and LY comparison

Hopefully an easy one:

I would like to get a CY comparison vs. a LY comparison of sales between offices. The problem I am running into is the performance of the query. When I try below, it works, but it is painfully slow (I believe the SUM is what is killing it):

With Member [Measures].[CY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] )
, [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) )
, [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

The comparison is important to get the day of week to day of week comparison (hence the 52 weeks * 7 days vs. using parallelPeriod to do a day of the year comparison).

If I try the following, it is *very* quick, but the problem is if I run it for CY and separately for LY, some offices may not have been open last year, or may have closed this year, so the number of rows returned may be (will be) different. Also, I don't know how to "union" them together into one cellset returned.

Select {
[Measures].[Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] }

Does anyone know of a way I can do something like the following, either with Temporary sets, etc.? Please forgive the simplicity of the problem, as I am still cutting my teeth with this powerful, yet complex product. :)

With Member [Measures].[CY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] } ,
[Measures].[Sales] )

Member [Measures].[LY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) } ,
[Measures].[Sales] )

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1

From [Cube]

Any suggestions are greatly appreciated!

Thank you in advance,
John Hennesey

It seems to me that the last MDX that you provided won't work, since you are using:

{ [Org].[Reg - Pctr - Office].CurrentMember, [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) }

It mixes different dimensions in the enumeration set - you will get an error for that.

I also beleive that the following query should give you very good performance in AS2005 even without SP2, but especially with SP2:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

But probably, if you really need to compare at the Day Of Year level, the best idea would be to add [Day Of Year] attribute to your Time dimension, and then you will be able to do the following:

Select { [Calendar].[Year].[2006], [Calendar].[Year].[2007] } on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Day Of Year].&[Day1] : [Calendar].[Day Of Year].&[Day2] } * Measures.Sales

|||

Mosha -

Thank you very much for your quick response. I was afraid that was the only way to do it... When I run the first example, it takes ~9 seconds with just the CY Sales. When I add LY Sales, it goes to 18 seconds. When I add another measure (say, CY Net sales, for the same time period as CY Sales), it balloons to 36 seconds. By the time I get all the measures in (Sales, Return Count, Net sales, etc..) it takes ~1 minute to run. We have a relatively small cube (we do a fully reprocess 4 times a day, which takes < 5 minutes to complete). As for the 2nd example, it is a year to date comparison, so we definitely have to restrict the LY dates to 364 days ago. Sounds like either a redesign of the cube, or service pack 2 is the answer. Does SP2 address this kind of query? Is there any way (for lack of better terms) to design this similar to SQL where you can stash the results from one MDX in a temporary cube, run another MDX then do a join between the two?

Thank you so much,
John Hennesey

|||

> When I run the first example, it takes ~9 seconds with just the CY Sales

Have you tried my variant of the MDX ? I beleive it should perform as fast as set in the WHERE clause even before SP2, but certainly with SP2 as well.

|||

Once again, thank you for the timely response!

Here is what I ran:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])
Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[RG].[ZZZ123].Children
} on 1
From [Cube]

I did, and unfortunately it took ~20 seconds.

When I run the following it takes less than a second, whether for CY or LY.

Select { [Measures].[LY Sales] } on 0,
{ [Org].[Region - Pctr - Office].[RG].[ZZZ123].Children } on 1
From [Cube]
Where [Preparer Calendar].[Date].&[20061101].Lag(364) : [Preparer Calendar].[Date].&[20070220].Lag(364)

Our environment is c# querying the cube, so I think the answer in this case (until we apply SP2) is to:

Get a list of all offices