Comparing two SSIS Packages?

I'd like to know how people out there are comparing ("diffing") their DTSX files.

Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.

ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"

I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.

I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.

So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.

Thanks in advance!

No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||

I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

[Microsoft follow-up]

|||

Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...

Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...

To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...

|||

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

Thank you!

This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

So... what are you using to compare packages, today?

|||

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

|||

jwelch wrote:

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

Well, it's good to know that great minds do indeed think alike.

|||

Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.

Comparing two SQLServer databases

Hi, does anybody know a nice tool to automatically compare and show
only the structural differences between two databases, this is, the
differences in the columns of a table, constrains, data type, etc.

Thanks

J.A.I use this one (there are a number of other, simipar products):

http://www.red-gate.com/SQL_Compare.htm

Simon|||For excellent compare functionality (including data) plus the ability
to link your source control system into the whole process I recommend
looking at DB Ghost (www.dbghost.com). It also guarantees any delta
script produced will work (due to a unique algorithm), something no
other tool can promise.

Comparing two sets of data

I have the following situation. One set of data has 274 rows (set2)
and anther has 264 (set1). Both data sets are similar in structure as
well as values for both of them were extracts from the same parent
table. Hope the info would substitute DDL. I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?

TIA.NickName wrote:
> I have the following situation. One set of data has 274 rows (set2)
> and anther has 264 (set1). Both data sets are similar in structure as
> well as values for both of them were extracts from the same parent
> table.
> Hope the info would substitute DDL.

It doesn't.

> I need to find the "gap"
> rows between these two sets.
> Attempted to run a query like
> select count(*)
> from set2
> where not exists
> (select *
> from set1)
> did not yield what I desired. What else to try?

Try posting your DDL. We at least need column names and keys to help you
here.

Zach|||OK,

I've proven that the "EXISTS" keyword/function can't solve this
problem. But then what?

-- test equality between two data sets
-- desired resultset: return "gap" rows

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));

insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');

select * into #tmp2
from #tmp1
where col1 < 5;
select *
from #tmp1
where not exists
(select *
from #tmp2)|||NickName (dadada@.rock.com) writes:
> I've proven that the "EXISTS" keyword/function can't solve this
> problem. But then what?

It certainly can, but you must specify how the NOT EXISTS is to work.
SQL is not about telepathy.

For your repro, you could do

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t2
WHERE t1.col1 = t2.col1)

or

select *
from #tmp1 t1
where not exists (Select *
from #tmp2 t
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)

depending on what you are looking for. The first just lists missing key
values, the second list all mismatches.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.
*) laziness is an enemy, I thought about that but the table has a large
number of columns, so, was wondering if there's another approach. Will
try it tomorrow when the db is available.
*) btw, I like the word, "telepathy".

Erland Sommarskog wrote:
> NickName (dadada@.rock.com) writes:
> > I've proven that the "EXISTS" keyword/function can't solve this
> > problem. But then what?
> It certainly can, but you must specify how the NOT EXISTS is to work.
> SQL is not about telepathy.
> For your repro, you could do
> select *
> from #tmp1 t1
> where not exists (Select *
> from #tmp2 t2
> WHERE t1.col1 = t2.col1)
> or
> select *
> from #tmp1 t1
> where not exists (Select *
> from #tmp2 t
> WHERE t1.col1 = t2.col1
> AND t1.col2 = t2.col2)
> depending on what you are looking for. The first just lists missing
key
> values, the second list all mismatches.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||NickName (dadada@.rock.com) writes:
> Thanks, Erland.
> *) laziness is an enemy, I thought about that but the table has a large
> number of columns, so, was wondering if there's another approach. Will
> try it tomorrow when the db is available.

If your aim is to find differences in any column, you will indeed have
to write code that has all column. There is no shortcut. What you can
do, if you have many tables and columns, is to generate code by reading
metadata. But this requires that you have a clear understanding for which
columns you want to compare, and which you do not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Something seems odd. Here's two test cases. Both are successful.
-- test equality between two data sets (with exact meta data)
-- desired resultset: return "gap" rows

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1));

insert into #tmp1
values(1,'a')
insert into #tmp1
values(2,'b')
insert into #tmp1
values(3,'c')
insert into #tmp1
values(4,'d')
insert into #tmp1
values(5,'e');

select * into #tmp2
from #tmp1
where col1 < 5;

select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

-- outcome = success

-- test equality between two data sets
-- with minor meta data difference (one table has 3 attributes while
the other has 2)
-- note: comparison seems still successful
-- desired resultset: return "gap" rows

drop table #tmp1
drop table #tmp2;

-- DDL and DML
create table #tmp1 (col1 int, col2 char(1),col3 int);

insert into #tmp1
values(1,'a',11)
insert into #tmp1
values(2,'b',22)
insert into #tmp1
values(3,'c',33)
insert into #tmp1
values(4,'d',44)
insert into #tmp1
values(5,'e',55);

select col1,col2 into #tmp2
from #tmp1
where col1 < 5;

select *
from #tmp1 t1
where not exists
(select *
from #tmp2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)

-- outcome = success

HOWEVER, when I applied the above to my tables (sorry I can't post
exact structure nor data here, PARENT table has 13 columns and the
derived table has 12 of them, comparison is between them), sql still
failed to find the "gap". What could possibly stands in the way?
Many thanks.|||Ahe, I think I've found the problem. In "my" tables, both has
duplicate rows, however, the number of duplicate rows are not the same,
(distinct rows are sure the same).|||NickName (dadada@.rock.com) writes:
> Ahe, I think I've found the problem. In "my" tables, both has
> duplicate rows, however, the number of duplicate rows are not the same,
> (distinct rows are sure the same).

I'm not sure how this should be addressed. Are you saying that in
one table you have two rows with the same value, but in another you
only have one?

If there are no disctinct keys in the data, all relational operations will
be problematic, that's for sure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might find it easier to export the table contents as ordered data and
then use a text file compare utility. Command-prompt example:

BCP "SELECT * FROM MyDatabase..Table1 ORDER BY MyPK" queryout
"C:\temp\Table1.txt" /T /c /SMyServer

BCP "SELECT * FROM MyDatabase..Table2 ORDER BY MyPK" queryout
"C:\temp\Table2.txt" /T /c /SMyServer

WINDIFF "C:\temp\Table1.txt" "C:\temp\Table2.txt"

--
Happy Holidays

Dan Guzman
SQL Server MVP

"NickName" <dadada@.rock.com> wrote in message
news:1104161334.766126.171470@.f14g2000cwb.googlegr oups.com...
> Ahe, I think I've found the problem. In "my" tables, both has
> duplicate rows, however, the number of duplicate rows are not the same,
> (distinct rows are sure the same).|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> You might find it easier to export the table contents as ordered data and
> then use a text file compare utility. Command-prompt example:
> BCP "SELECT * FROM MyDatabase..Table1 ORDER BY MyPK" queryout
> "C:\temp\Table1.txt" /T /c /SMyServer
> BCP "SELECT * FROM MyDatabase..Table2 ORDER BY MyPK" queryout
> "C:\temp\Table2.txt" /T /c /SMyServer
> WINDIFF "C:\temp\Table1.txt" "C:\temp\Table2.txt"

However, the use of windiff will break down if there is a column which is
permitted to be different in the tables. But there is a better alternative:
Beyond Compare, from http://www.scootersoftware.com. Beyond Compare
offers comparison on character level.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

You're exactly right. Maybe they did that to test my analytic skill,
who knows :)|||Dan,

Thanks. It seems a good way to try. Will do tomorrow.

Erland, no columns are supposed to contain different data sets though
one table may have an extra column, that is supposedly the only
difference. OK, I'll try the recommended software as well when get a
chance. I appreciate it.

Donsqlsql

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
YogWhat do mean by "Automated"? There is certainly a way, just write a query
that returns a boolean 1/0 based on whether the row column values are the
same or different... How to "Automate" it, depends on what "event" you want
to "trigger", (cause ) it to run... If yo want it to run automatically on
some time interval, use SQLAgent...
If you want it to run whenever someone inserts, updates, or deletes a record
from the table, then use a trugger.
"Yog" wrote:

> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the same
> table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog|||What result do you want from the comparison? Do you mean you want to
compart two particular rows? In that case you can use a self-join on
the columns you want to compare.
Maybe you just want to find duplicates, in which case:
SELECT col1, col2, col3, ...
FROM YourTable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||Yog wrote:
> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the
> same table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog
You can use the CHECKSUM() function as in:
Create table #checktest(col1 int, col2 int, col3 int)
insert into #checktest values (1, 2, 2)
insert into #checktest values (2, 2, 2)
insert into #checktest values (3, 2, 3)
Select CHECKSUM(col2, col3) as "ID 1"
From #checktest
where col1 = 1
UNION ALL
Select CHECKSUM(col2, col3) as "ID 2"
From #checktest
where col1 = 2
UNION ALL
Select CHECKSUM(col2, col3) as "ID 3"
From #checktest
where col1 = 3
drop table #checktest
David Gugick
Imceda Software
www.imceda.com|||To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
"CBretana" wrote:
> What do mean by "Automated"? There is certainly a way, just write a quer
y
> that returns a boolean 1/0 based on whether the row column values are the
> same or different... How to "Automate" it, depends on what "event" you wa
nt
> to "trigger", (cause ) it to run... If yo want it to run automatically on
> some time interval, use SQLAgent...
> If you want it to run whenever someone inserts, updates, or deletes a reco
rd
> from the table, then use a trugger.
> "Yog" wrote:
>|||SELECT A.col1, A.col2, ...
FROM YourTable AS A,
YourTable AS B
WHERE A.x = ?
AND B.x = ? /* Specify the two rows to be compared */
AND
(A.col1<>B.col1
OR A.col1<>B.col1
OR ...)
David Portas
SQL Server MVP
--|||Hi David,
Many thanks for your POST.
Your answer provided me HINT to achieve my goal of comparing two rows for my
requirement.
Regards,
Yog
"David Portas" wrote:

> SELECT A.col1, A.col2, ...
> FROM YourTable AS A,
> YourTable AS B
> WHERE A.x = ?
> AND B.x = ? /* Specify the two rows to be compared */
> AND
> (A.col1<>B.col1
> OR A.col1<>B.col1
> OR ...)
> --
> David Portas
> SQL Server MVP
> --
>

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
Yog
On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:

>Hi Gurus,
>Is there any automated way to compare the contents of TWO rows in the same
>table of SQL Server ?
Hi Yog,
The answer is probably yes - but you've given too little information
about your needs to allow for any more specific information.
Please check out www.aspfaq.com/5006to find out what information is
needed (and in what form) to allow others to understand your problem and
proposes solutions.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Well,
To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
Hope this makes sence.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:
>
> Hi Yog,
> The answer is probably yes - but you've given too little information
> about your needs to allow for any more specific information.
> Please check out www.aspfaq.com/5006to find out what information is
> needed (and in what form) to allow others to understand your problem and
> proposes solutions.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 22:27:02 -0800, Yog wrote:

>Well,
>To precisely put it , the content of the problem is as below
>Objective :- Compare TWO rows contents for few selected Columns. If they are
>different then show those columns with the contents.
>ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
>language or front end)
>Hope this makes sence.
Hi Yog,
Yes - but not enough to provide actual help. I need your table structure
(posted as CREATE TABLE stmt), some sample data (as INSERT stmts) and
the expected output.
I see that the link I posted got mangled - I missed a space between the
link and the next word. Here's the link again:
www.aspfaq.com/5006
Please follow that link and read the information there; then post the
required information here for further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Comparing two row values

whats the best way to compare two row values and concatenate those two

For example

Table1
---
Col1 Col2
----
A 1
A 4
B 2
C 3
A 5

I need a query to return something like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

Thanks in advancetry using cursor. one single SQL will not do the job.|||If I understand what you're asking, there is no need to concatenate, and certainly no need for a cursor.

What I think you want to do is SELECT rows from a table or view based on the values of four different columns (a, b, c, and d). In your case, you need to see the rows that meet all three criteria: Column A values of 1, 4, or 5 and also have Column B = 2 and Column c = d.

If I understand your question, then I'd use:SELECT *
FROM myTable
WHERE (a = 1 OR a = 4 OR a = 5)
AND b = 2
AND c = d-PatP|||You need to explain more. The way I see it you want
select * from Table1
where (Col1='A' and Col2 in (1,4,5))
or (Col1='B' and Col2=2)
or (Col1='C' and Col2=3)|||Ok let me elaborate

I have a table with two columns with the values

Col1 Col2
----
A 1
A 4
B 2
C 3
A 5

The data mentioned above is just the sample, I dunno wats its going to be in

I am looking for a query and that should form the where clause like
"where (A=1 or A=4 or A=5) and (b=2) and (c=3)"

which means it should find the identical ones first and concate with or clause and rest join with the and clause. In this case we have three A's so it should form (A=1 or A=4 or A=5) and then and clause to (b=2) and (c=3)

Thanks in advance|||I think I get what you're after.

Where do you need to plug in this "where" clause when you're done? Perhaps there's a cleaner way to get the job done.|||this only froms the string that u r looking for using cursor (i can hear some screaming!!). u know better what to do with that string

declare @.Str char(1)
declare @.LastStr char(1)
declare @.Num smallint
declare @.Out varchar(200)
set @.Out = '('
declare Cur1 cursor for select Str,Num from theTable order by str,num
open Cur1
fetch next from Cur1 into @.Str,@.Num
while @.@.fetch_status = 0
begin
if (@.LastStr is not null)
begin
if(@.LastStr <> @.Str)
set @.Out = @.Out + ') and ('
else
set @.Out = @.Out + ' or '
end
set @.Out = @.Out + @.Str + '='+ cast(@.Num as varchar)
set @.LastStr = @.Str
fetch next from Cur1 into @.Str,@.Num
end
close Cur1
deallocate Cur1
set @.Out = 'where ' + @.Out + ')'
print @.Out|||thanks upalsen, but I am thinking is there any other way to do this without cursors. Your code definetly works but looking for alternative. Thanks|||as i told earlier, it is possible without cursor as well. but that is neither going to look smarter not would perform better. performance of course is dependent on many other factors that is not clear to us form the post. if it is a onetime affair u can use cursor without any doubt. however, if u r still interested in cursor-less sql, let me know.|||thanks upalsen, yes please let me know how can we do this without cursors, I did use the cursors and its working fine, but just want to know how can we do without cursors. I appreciate your help.

Thanks in advance.|||select Col1,Col2 into #TT from theTable order by Col1,Col2
update #TT set #TT.Col1 = #TT.Col1+'_Max' /*expecting the width sufficient to hold 5 chars, else add col/change width*/ from #TT, (select Col1,max(Col2) as MaxCol2 from #TT group by Col1) as TT2 where #TT.Col1 = TT2.Col1 and #TT.Col2=TT2.MaxCol2
declare @.sql varchar(200)
set @.sql = '('
select @.sql = @.sql + left(Col1,1) + '=' + cast(Col2 as varchar) + case when len(Col1)>1 then ') and (' else ' or ' end from #TT
set @.sql = 'where ' + left(@.sql,len(@.sql)-6)
print @.sql
drop table #TT

looks meaningless though...cant find anything better than this....|||I like a challenge. I came up with this one...
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp (Col1 CHAR(1), Col2 CHAR(1))
INSERT #tmp VALUES ('A','1')
INSERT #tmp VALUES ('A','4')
INSERT #tmp VALUES ('B','2')
INSERT #tmp VALUES ('C','3')
INSERT #tmp VALUES ('A','5')
INSERT #tmp VALUES ('C','1')

DECLARE @.sql VARCHAR(1000)
SET @.sql = '('

SELECT @.sql = @.sql + CASE
WHEN Col1 IS NULL THEN ''
WHEN Col2 IS NULL THEN ') AND ('
ELSE Col1 + '=' + Col2 + ' OR '
END
FROM #tmp
GROUP BY Col1, Col2
WITH ROLLUP

SELECT REPLACE(LEFT(@.sql, LEN(@.sql) - 6), ' OR )', ')')

comparing two records field by field

Hi,
a stored proc should find all different fields in two records. This two
records are in two temp tables and have the same fields, but the
recordstructure can be different in every call of the stored proc.
For example, in one call i check the difference of two records from the
customer table, in the next call i check the difference of two records from
the orders table.
So i cannot use field names. In the moment i read all informations from
sysobjects and syscolumns and build sql strings for each column and execute
them to get the value as varchar and compare them.
But this is so performance- and timeconsuming, especially for records with
nearly 200 fields, that i have to find another way.
Any suggestions how to do this?
thanks for all tips,
HelmutIt depends on what your ultimate aim is (other than pain). If you're
just wanting some data analysis there's a tool called SQL data compare
that does that quite well.
If you're aiming for some nice dynamic query, you'll have to post a SQL
example (I'm afraid your description was a tad cryptic).
my approach when comparing things dynamically is to use the stored proc
sp_columns. Shove that up a temporary table, use sp_executesql to then
execute the compare, however it sounds like you're nearly doing that
(shouldn't be too bad performance wise - are you hitting the cursors a
bit too hard?)
I think you'll get better advice if you put some hard examples up of
what you're currently doing, and what you're wanting it to do.
As a load of people write as a generic response - "post your DDL and
code examples and maybe we can help more" - they say "more" as though
that was in some way helpful
anyway,
more info dude
Cheers
Will|||Hello Will,
thanks for your response. Here an example:
let's say, i have two backups of a database. Now i restore both backups and
have a stored proc wich loops through the persons table record by record.
There is an identity field, so i can read the same record from both backups
...
insert into #tbl1 select * from backup1.dbo.persons where id = 123
insert into #tbl2 select * from backup2.dbo.persons where id = 123
...
if both records exists, then i want to find the differences field by field,
for this i call another stored proc, and this proc knows, that it has one
record in #tbl1 and another record in #tbl2 with identical structure.
But it does not know, that this are records from table persons!
So it should do something like:
declare @.tblDiff (
fieldname varchar(20),
oldValue varchar(2000),
newValue varchar(2000) )
for x = 1 to #tbl1.fieldcount do begin
if (#tbl1.field[x].IsNull and not #tbl2.field[x].IsNull) or
(not #tbl1.field[x].IsNull and #tbl2.field[x].IsNull) or
(#tbl1.field[x].Value <> #tbl2.field[x].Value) then
insert into @.tblDiff values(#tbl1.field[x].Name, #tbl1.field[x].Value,
#tbl2.field[x].Value)
end
return select * from @.tblDiff
This is a pseudo code to show what i want to do. Now i need to solve this
in pure TSQL.
thanks,
Helmut|||You could try something like this, but it sounds like you're already at
this solution.
DECLARE @.Table varchar(50)
SET @.Table = 'sysobjects'
DECLARE @.IDvalue int
CREATE TABLE #Cols
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
TYPE_NAME sysname,
[PRECISION] int,
LENGTH int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)
CREATE TABLE #Results(field sysname, OldValue nvarchar(4000), NewValue
nvarchar(4000))
INSERT INTO #Cols
exec sp_Columns @.Table
DECLARE Cols Cursor
FOR SELECT Colname
FROM #Cols
DECLARE @.Colname sysname
OPEN Cols
FETCH NEXT FROM cols INTO @.COlname
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'INSERT INTO #Results SELECT ''' + @.Colname + ''', T1.' +
@.Colname + ', T2.' + @.ColName + ' FROM database1.dbo.' + @.tablename + '
as T1 INNER JOIN database2.dbo.' + @.TableName + ' as T2 on T1.IDField =
' + @.IDValue + ' AND T2.IDField = ' + @.IDValue + 'WHERE t1.' + @.Colname
+ ' != T2.' + @.Colname + ' AND NOT (T1.' + @.ColName + ' IS NULL AND
T2.' + @.ColName + ' IS NULL)'
exec sp_executesql @.SQL
FETCH NEXT FROM cols INTO @.COlname
END
CLOSE Cols
DEALLOCATE Cols
DROP TABLE #Cols
SELECT * FROM #REsults
DROP TABLE #Results|||Another soloution would be to use
BINARY_CHECKSUM()
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Am 6 Apr 2006 01:47:28 -0700 schrieb Jens:

> Another soloution would be to use
> BINARY_CHECKSUM()
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
Not really, at first, BOL says, that binary_checksum() can detect most, but
not all changes. And second, this can be used maybe in the calling function
to detect, if a difference is here. But my problem is the second step -
finding and documenting the changes.
bye, Helmut|||Hi Will,
thanks for this source. What i see at first, i need much more steps to find
the differences, so maybe you solution is faster (i hope).
But the main points are the same, information comes from sysobjects and for
every field i have to build a statement and do a sp_executesql.
But on the wend i take this source and make a benchmark and post the
result on monday.
Do you think, it would be much faster using an external stored proc?
thank you very much,
Helmut|||What are you trying to do Helmut? some kind of a retrospective audit
trail? have you considered putting triggers on your tables to maintain
this data as it changes?|||Am 6 Apr 2006 03:07:00 -0700 schrieb Will:

> What are you trying to do Helmut? some kind of a retrospective audit
> trail? have you considered putting triggers on your tables to maintain
> this data as it changes?
Yes, it should do both. For audit trail i use the data from inserted and
deleted. Working with columns_updated() is no solution, because i only
store changes and this means, if i have a record with 200 fields and only 5
fields are filled with data (and the rest is NULL), then i store only this
5 values. But when you do an insert, then columns_updated() has all bits
set.
bye, Helmut|||Helmut,
I'm afraid I think that your query is just about as fast as you'll get
it (I certainly can't think of any significant ways to improve it).
I've had one idea you could try, but I don't have time to fully
investigate it:
You could rather than performing the difference check on each column,
build up the SQL string as 'select inserted.'+colname+',
deleted.'+colname,+' CAST(CASE when insert.'+colname+' !=
deleted.'+colname+' then 1 else 0 end as bit) as ' + colname +
'ischanged' .
then build up this string for the whole set of columns, only do one
select into a temporary table, then do your inserts into your audit
table based on whether or not the bit field conameischanged has been
set to 1.
I have no idea if that will work, it has the advantage that you only
select from your 2 tables once as opposed to once per column, but it
has the divantage of needing an extra insert into a temporary table.
if you do write it then post the source and the benchmarks, I'd be
interested to know how it performs. If this isn't clear let me know,
I'll try and write some SQL this evening.
Cheers
Will