SQL Compare....objects are the same but tool says not

SQL_Rookie

Running SQL compare 5.2 on 2 seperate db's with same schema. I get back that there are procs that are different but once I highlight them and check the SQL differences window at the bottom of the screen the code is the same and not is highlighted to show the differences

--- I have a few options set already too..
ignore white space is checked
fill facte and index padding is checked
with nocheck is checked
filegroups, partition shemes... is checked
user properties is checked
with element order is checked.

second issue ---how can you expand the grid columns to see the object name. My procs names are long and I can not see the entire name on the first object name column the second object name column is fine.

JonathanWatts

Hi there,

1) Is it possible for you to post the db1 and db2 scripts for an object that demostrates this behaviour, so that we can take closer look at your problem?

2) Unfortunately the grid columns have a fixed width, you will get a tooltip if the object name exceeds the column width, but I can see this is a problem if you have lots of similarly named objects. May I ask what your average object name length is? We may look into addressing this is in future releases.

Regards,

Jonathan

SQL_Rookie

not sure what you mean about scripts but here is the code from the sql difference windows

-- Stored Procedure

CREATE procedure [sp_MSdel_Activities] @pkc1 int
WITH ENCRYPTION as
delete [Activities]
where [ACTIVITY_ID] = @pkc1
@rowcount = 0
@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598


GO


-- Stored Procedure

CREATE procedure [sp_MSdel_Activities] @pkc1 int
WITH ENCRYPTION as
delete [Activities]
where [ACTIVITY_ID] = @pkc1
@rowcount = 0
@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598


GO




also if you can tell me how to get the average length of the object names I can supply that too. I can get the LEN of the objects names but how can I get average.

JonathanWatts

Hi there

1) I take that you are using SQL 2000? Also can I check that you are not using the ADD WITH ENCRYPTION option?

2) The following should give you the average length of object name

SELECT len(name) from sysobjects COMPUTE  avg(len(name))

Regards,

Jonathan

SQL_Rookie

yes I'm using the "ADD WITH ENCRYPTION "

max lenght is 90
avg length is 30

JonathanWatts

Okay, I am assuming that only one of your databases is actually encrypted. If this is the case then I think that you have encountered a rather strange behaviour that is present in SQL Compare.

When there is a difference in encryption within an object whilst the “add with encryption” option is enabled and the encrypted database is database1 (the left hand database) then encryption differences will not be ignored and the object will be reported as different even if the object appears to be equal in the SQL Difference panel.

This might sound a bit strange but can I ask you what the result of comparing the databases the other way around? This should resolve your problem.

Sorry for this inconvenience,

Regards,

Jonathan

By the way thanks for the length information.

SQL_Rookie

If I reading you reply correctly is that you are saying the left side db as the object encrypted. Actually both db's don't have this proc encrypted. If you see the name of the proc is actually a replication proc used by SQL server. And the procs are the same and we don't encrypt these since replication uses them. We encrypt the other procs that our company creates mandated by security..I know Iknow .... that is why the check box is on... but as you can see I have to pick thru all of them to select the ones I really want to move over....and the replication procs 3 per table and we have over 200 or so tables.....nasty...



Can you explain to me how sql compare works alittle ? I would like to know if there is any impact on running this on a production db while users are heavily using it?

JonathanWatts

If you have differences in encryption and you are using the SQL Compare "ADD WITH ENCRYPTION" option then you should have the object that is really encrypted on the RIGHT hand side to ensure that they are equal.

However, what you are saying suggests that it is something more than this cauisng your problem. I will take a closer look into this and get back to you. May I just double check that you are using v5.2.0.32?

In answer to your last question. We would always say that you should test any change on a staging environment before running them on your production database to ensure you don't cause any problems with the database or dependent applications.

Also I think that best practices would suggest that any changes to a database's schema should be carried out when a database is not being used to avoid any contention issues.

Regards,

Jonathan

SQL_Rookie

yes we are using 5.2.0.32..


I meant running sql compare on the prod just to see what is different not to actually go thru with the sync.