Comparing all fields of two rows in few tables.

The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G

Command Line connect to SQL Server Database

I am trying to connect to the SQL Server database from the command line or from a cmd script on Windows - Is there a way to do this?

I am new to SQL Server and I am trying to create a script that can check to see if the database is up and then send me a message if it is down.Yes, you can connect to the SQL Server from the command line by means of the BCP utility for example.

Here is a list of the various CMD utilities for SQL 2000. Haven't tried them for SQL 2005 but i suppose they either work or there is something simmilar around.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_intro_3nsj.asp

As to inform you on the status of the SQL Server, you could create a Job in the SQL Server Agent to check for a simple query such as "SELECT @.@.SERVERNAME" and send an e-mail when it fails.
You can also set the SQL Server Agent service to auto-restart and create another Job to start every time the SQL Server Agent starts and tell the job to send an e-mail, so you can check for problems with the SQL Server Agent.

Hope this helps.
Good luck,
Alan.|||

osql.exe is command line client which allows you to establish a connection to SQL Server.

Please try
osql -E
for trusted connection, or
osql -U<login id> -P<password> -S<instance name>

you can skip -S parameter if you have installed a default instance.

In addition, use
osql -U<login id> -P<password> -S<instance name> -i<tsql script file name>
to run your scripts.

HTH.

MCW

|||Thanks for pointing me to the CMD utilities.|||Thanks this was very helpfull I was able to login from the cmd prompt.