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

Compare 2 records in the same group

Hi all!
I have a staff Turnover report with table that has a group for
"Status". The value of this group is either Active or Terminated, so
here's a very simple description of how the table looks when
previewed:
_________________________________
TURNOVER REPORT
Active (5 associates)
Luke Skywalker
Leah Organa
Han Lolo
Chewbacca
Yoda
Terminated (2 associates)
Obi Wan Kenobi
Anakin Skywalker
_________________________________
As you can see I have a "Count" function that gets the number of
associates with each status (5 active, 2 terminated). I need a
function to divide those two values (2/5) to get the turnover percent,
but try as I may I cannot seem to get it to perform a calculation on
members of the same group. I have tried various methods to get around
this with no success. Does anyone have any suggestions?
I'd really like this to be calculated from within the report instead
of adding another dataset to return these numbers (i already have 5
datasets - most are used to populate data for the report parameters).
I appreciate your help!
MichellePut the values for count (2 and 5) in separate textboxes. For example lets
name these textboxes as tbCountActive and tbCountTerm.
Now in another textbox use following expression to display percent:
= ReportItems!tbCountTerm.Value / ReportItems!tbCountActive.Value
Cheers
AJ
"Michelle" <Michelle@.bwalk.com> wrote in message
news:92c953bf.0501251305.2a45486f@.posting.google.com...
> Hi all!
> I have a staff Turnover report with table that has a group for
> "Status". The value of this group is either Active or Terminated, so
> here's a very simple description of how the table looks when
> previewed:
> _________________________________
> TURNOVER REPORT
> Active (5 associates)
> Luke Skywalker
> Leah Organa
> Han Lolo
> Chewbacca
> Yoda
> Terminated (2 associates)
> Obi Wan Kenobi
> Anakin Skywalker
> _________________________________
> As you can see I have a "Count" function that gets the number of
> associates with each status (5 active, 2 terminated). I need a
> function to divide those two values (2/5) to get the turnover percent,
> but try as I may I cannot seem to get it to perform a calculation on
> members of the same group. I have tried various methods to get around
> this with no success. Does anyone have any suggestions?
> I'd really like this to be calculated from within the report instead
> of adding another dataset to return these numbers (i already have 5
> datasets - most are used to populate data for the report parameters).
>
> I appreciate your help!
> Michelle|||Hi AJ,
How to put them in seperate textboxes? The count is a single field in
a group - and it needs to remian in the group in order to get the
correct numbers.
Thanks!
Michelle|||Try
=Sum( iif(Fields!Status.Value = "Terminated", 1, 0), "DataSet1") / Sum(
iif(Fields!Status.Value = "Active", 1, 0), "DataSet1")
Where DataSet1 is the dataset bound to the table.
"Michelle@.bwalk.com" wrote:
> Hi AJ,
> How to put them in seperate textboxes? The count is a single field in
> a group - and it needs to remian in the group in order to get the
> correct numbers.
> Thanks!
> Michelle
>|||Hi Michelle,
Actually I assuming that you have combined the text and value in one textbox
like,
="Active (" & count(whatever) & "associates)"
Instead place the value of count in a single textbox like,
= count(whatever)
and then just for convenience sake give some meningful name to this textbox,
i.e. tbCountActive...
Then do the calculation based on the value contained in the textbox. You
can refer to a specific textbox by using ReportItems!textboxname.
I haven't seen this feature in Crystal Reports and this is something cool
with Reporting Services.
Hope this helps...
AJ
<Michelle@.bwalk.com> wrote in message
news:1106692227.587559.227350@.c13g2000cwb.googlegroups.com...
> Hi AJ,
> How to put them in seperate textboxes? The count is a single field in
> a group - and it needs to remian in the group in order to get the
> correct numbers.
> Thanks!
> Michelle
>|||This works beautifully - thank you!|||This may or may not work depending on where the output is desired due to the
scope limitations.
"AJ" wrote:
> Hi Michelle,
> Actually I assuming that you have combined the text and value in one textbox
> like,
> ="Active (" & count(whatever) & "associates)"
> Instead place the value of count in a single textbox like,
> = count(whatever)
> and then just for convenience sake give some meningful name to this textbox,
> i.e. tbCountActive...
> Then do the calculation based on the value contained in the textbox. You
> can refer to a specific textbox by using ReportItems!textboxname.
> I haven't seen this feature in Crystal Reports and this is something cool
> with Reporting Services.
> Hope this helps...
> AJ
>
> <Michelle@.bwalk.com> wrote in message
> news:1106692227.587559.227350@.c13g2000cwb.googlegroups.com...
> > Hi AJ,
> >
> > How to put them in seperate textboxes? The count is a single field in
> > a group - and it needs to remian in the group in order to get the
> > correct numbers.
> >
> > Thanks!
> > Michelle
> >
>
>

Communication Link Failure?

Hai,

Iam using VC++ application to connect remote database(MSSQL) through network.

Everything works fine, if the network connection is active. If the network breaks and resumes, the application throws "Communication Link Failure" and do not get connected even after the network is resumed.

Client :

* Windows 2000 Professional

* MDAC 2.5

* VC++, using framework- CRecordset to access database

* PEER to PEER network configuration

* DSN through ODBC

Server :

* Windows 2000 Professional

* MDAC 2.8

* MSSQL Server

how to resume the database connection ?

Any one help me?

Thanks in advance,

If an existing connection fails due to broken network connectivity the client application must open a new connection to the server.

Does the new connection fail as well? If so, what is the full error message text?

|||

No..if the application is restarted the connection resumes..

Do i have to make a new connection after the broken network connectivity? Is there any other solution without reconnecting?

|||

Yes, you have to make a new connection after the broken network connectivity.

|||Thank you..

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.