Comparing two databases for deleted records

How do I compare two databases to determine deleted
records."Aboki" <anonymous@.discussions.microsoft.com> wrote in message
news:13d7001c41b24$a4d4d240$a001280a@.phx
.gbl...
> How do I compare two databases to determine deleted
> records.
Select *
from dbArchive.dbo.table1 as a
left outer join
dbUpdated.dbo.table1 as u
on a.PK = u.PK
where u.pk is null
dbArchive = the old database name
dbUpdated = the one with the rows deleted
PK = whatever the Primary Key column is
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||hi ,
there is a tool that do compare between 2 databases and display deleted reco
rds an changes in schema and data ,
it's friendlly tool with beautiful gui for users.
it is called dbMaestro.
You can find it here:
http://www.extreme.co.il|||You might want to check out the Red-Gate tools SQL Compare and SQL
DataCompare. I think these tools will do what you are requesting. Here is
their website: http://www.red-gate.com/
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"yaniv cohen" <yanivc@.extreme.co.il> wrote in message
news:30D974F8-F7EC-4AD7-BCC7-DDE5D9146096@.microsoft.com...
> hi ,
> there is a tool that do compare between 2 databases and display deleted
records an changes in schema and data ,
> it's friendlly tool with beautiful gui for users.
> it is called dbMaestro.
> You can find it here:
> http://www.extreme.co.il
>

comparing time

How do I determine if the time value of a datetime field (in SQL) is between
2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
AM# and the comparison fields are starttime = #05:00:00 PM# and
endtime=#02:00:00 AM#)?
Thanks,
Mary Fran
WHERE
(LastUpdated >= StartTime AND LastUpdated < EndTime)
...
;
?
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
> Thanks,
> Mary Fran
|||> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
If I understand correctly, you want to ignore the date part of the database
column. Furthermore, when the specified start time is greater than the end
time, you want to reverse the criteria.
There may be more elegant methods but I believe the example below will
accomplish the task. Be advised that this technique will require a scan
because of the non-sargable expression. A more efficient approach is to
store time with a base date like January 1, 1900 to facilitate searches on
time only.
SELECT LastUpdated
FROM dbo.MyTable
WHERE
(@.TimeOnlyStart < @.TimeOnlyEnd AND
DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1, LastUpdated)
BETWEEN @.TimeOnlyStart AND @.TimeOnlyEnd)
OR
(@.TimeOnlyStart >= @.TimeOnlyEnd AND
(DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) > @.TimeOnlyStart
OR DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) < @.TimeOnlyEnd)
)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
> Thanks,
> Mary Fran
|||Sorry, after reading Dan's response, I realize I misunderstood the question.
A slightly different approach (which also accounts for the case where start
and end time are the same, and lastupdated falls on that instant):
CREATE TABLE #foo
(
i INT,
LastUpdated DATETIME,
StartTime SMALLDATETIME,
EndTime SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT 1,'2007-05-29 01:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 2,'2007-05-29 12:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 3,'2007-05-29 16:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 4,'2007-05-29 19:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 5,'2007-05-29 16:04:32', '05:00 PM', '11:00 PM';
INSERT #foo SELECT 6,'2007-05-29 16:04:32', '03:00 PM', '07:00 PM'; --
should match
SELECT i,LastUpdated
FROM (
SELECT i,LastUpdated,
delta = DATEDIFF(MINUTE,0,DATEADD(DAY,-DATEDIFF(DAY,0,LastUpdated),
LastUpdated)),
s = DATEDIFF(MINUTE,0,StartTime),
e = DATEDIFF(MINUTE,0,EndTime),
r = ABS(DATEDIFF(MINUTE,StartTime,EndTime))
FROM #foo
) x
WHERE (delta BETWEEN s AND s+r)
OR delta = CASE WHEN s = e THEN delta ELSE -1 END
OR delta <= CASE WHEN e < s THEN e ELSE -1 END;
DROP TABLE #foo;
sqlsql

comparing time

How do I determine if the time value of a datetime field (in SQL) is between
2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
AM# and the comparison fields are starttime = #05:00:00 PM# and
endtime=#02:00:00 AM#)?
Thanks,
Mary FranWHERE
(LastUpdated >= StartTime AND LastUpdated < EndTime)
...
;
?
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
> Thanks,
> Mary Fran|||> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
If I understand correctly, you want to ignore the date part of the database
column. Furthermore, when the specified start time is greater than the end
time, you want to reverse the criteria.
There may be more elegant methods but I believe the example below will
accomplish the task. Be advised that this technique will require a scan
because of the non-sargable expression. A more efficient approach is to
store time with a base date like January 1, 1900 to facilitate searches on
time only.
SELECT LastUpdated
FROM dbo.MyTable
WHERE
(@.TimeOnlyStart < @.TimeOnlyEnd AND
DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1, LastUpdated)
BETWEEN @.TimeOnlyStart AND @.TimeOnlyEnd)
OR
(@.TimeOnlyStart >= @.TimeOnlyEnd AND
(DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) > @.TimeOnlyStart
OR DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) < @.TimeOnlyEnd)
)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?
> Thanks,
> Mary Fran|||Sorry, after reading Dan's response, I realize I misunderstood the question.
A slightly different approach (which also accounts for the case where start
and end time are the same, and lastupdated falls on that instant):
CREATE TABLE #foo
(
i INT,
LastUpdated DATETIME,
StartTime SMALLDATETIME,
EndTime SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT 1,'2007-05-29 01:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 2,'2007-05-29 12:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 3,'2007-05-29 16:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 4,'2007-05-29 19:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 5,'2007-05-29 16:04:32', '05:00 PM', '11:00 PM';
INSERT #foo SELECT 6,'2007-05-29 16:04:32', '03:00 PM', '07:00 PM'; --
should match
SELECT i,LastUpdated
FROM (
SELECT i,LastUpdated,
delta = DATEDIFF(MINUTE,0,DATEADD(DAY,-DATEDIFF(DAY,0,LastUpdated),
LastUpdated)),
s = DATEDIFF(MINUTE,0,StartTime),
e = DATEDIFF(MINUTE,0,EndTime),
r = ABS(DATEDIFF(MINUTE,StartTime,EndTime))
FROM #foo
) x
WHERE (delta BETWEEN s AND s+r)
OR delta = CASE WHEN s = e THEN delta ELSE -1 END
OR delta <= CASE WHEN e < s THEN e ELSE -1 END;
DROP TABLE #foo;

Compare Dates and fail job if no match

This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it

Scenario.

I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.

To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.

SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry

Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.

How do I get this working - can't seem to get my head around how I can compare the 2 dates ?

Derived column transformation has DateDiff function.

or

Script Component, you can write your own function in vb.net

|||Use the precedence constraints in the control flow. Use Execute SQL tasks to get your dates, and then hook them up to a sequence container which houses your data flow(s). The precedence constraint can perform your date comparison.|||

Hey Phil,

Thanks for that... you just confirmed what I had gradually worked out for myself!!

My solution was.....

I created 2 Execute SQL tasks. One to get the source database date, the other to get the "next date" due for loading into the DW and joined them to the first execute package task. But, between the final Execut SQL task & the package task I created a precedence "expression" constraint as follows

@.[User::MasterPackage_vLoaddate]== @.[User::NextLoadDate]

which were the 2 variables assigned from the Execute SQL tasks. On success, the remaining packages run but on failure the job fails.

It works great, so thanks for confirming that....

|||I appreciate that you tried this on your own and got it to work. I also appreciate that you posted your solution for others to use.

Glad it worked out for you!

Thanks,
Phil

Compare data in Tables

I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

LeoIf its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.

"Leo" <leolist@.optushome.com.au> wrote in message
news:Xns9639579AFB3Bleolistoptushomecoma@.211.29.13 3.50...
>I am trying to determine the changes an application makes to a database.
> The plan is to copy the existing schema (active) to a reference schema,
> run
> the application and then diff the table data between the reference and the
> a active schema. I have found one software vendor who has a tool to do
> this, but it will only do one table at a time (interactively); I have more
> then 300 and will run this a few times.
> One other way of determining the changes, I guess, would be to log all sql
> statements (in order), but I don't know how to do this (either).
> Any pointers would be greatly appreciated.
> Leo|||There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.|||"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in
news:1113551521.457015.133450@.l41g2000cwc.googlegr oups.com:

> DB Ghost

DB Ghost did exactly what I needed.

Thanks for your advice

Leo

Command delivery status in MSrepl_commands

I need to build some custom replication trouble shooter and I can't find
answers on couple of questions.
- How to determine status of command in MSrepl_commands? I mean status is
delivered this command to subscriber or not.
- How long delivered transactions reside in MSrepl_commands?
Have a look at the view msdistribution_status, which should be what you are
looking for. The code of the view is not encrypted and you could use it to
not do the group by and create your own version. If you want the actual
commands, then have a look at sp_browsereplcmds.
How long do commands reside in this table? If they have been read by all the
distribution agents involved and you don't have anonymous subscribers, the
commands will be removed by the cleanup agent. If a distribution agent isn't
synchronized or you have anonymous subscribers, they'll stay there until the
retention period is reached (72 hourd by default), and are then removed by
the cleanup agent.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)