How to ignore the 'WITH ENCRYPTION' phrase ?

mah530

I need to compare the objects in two SQL 2000 databases. In one database, all of the objects are encrypted, in the other they are not. ( This will always be the case). When I compare the objects, all objects are considered different due to the WITH ENCRYPTION clause that exists only in one database.

How can I set up the compare so that it will ignore this one difference, and give me just the legitimate differences?

Can I set it up to ignore specific text - such as 'WITH ENCRYPTION'?

Thank you!!!

Eddie D

Thank you for your post into the forum.

Unfortunately there is not an option available within SQL Compare to Ignore the 'WITH ENCRYPTION' syntax when comparing or synchronizing database objects.

Also I am unaware of a work around for the problem you have reported to us.

I have added the details of this forum post to an existing Feature Request, requesting to add an Ignore WITH Encryption option. The reference number for this request is SC-4419. At this time the feature request is marked for review. Hopefully the requested feature will appear in a future version of SQL Compare.

Many Thanks
Eddie

mah530

That makes things very difficult here, because 90% of the time we will be trying to verify/synch the encrypted version against our master database which will never be encrypted.

This means visually checking each object to see if the 'difference' is only due to the encryption phrase. Thousands of objects.

I am interested in any workarounds that you could think of. I was considering scripting the encypted db, and removing the 'with encyption' , and then comparing it to the master d...but, I don't think I can even generate a script against the encypted objects...

Any other workaround ideas??

Thanks

mah530

I started looking at the SQL Compare SDK - to see if I could try to do this myself.

Could I try creating new Option Type - "IgnoreWithEncryption" - that would be processed in similar way as the existing option type "IgnoreWithNocheck"? Only difference would be that it would look for the WITH ENCRYPTION phrase in the object text, rather than the WITH NOCHECK on foreign keys and check constraints.

Is it feasible? or are all needed classes sealed/unavailable to me?

Thank you!

Simon C

Unfortunately, you can't add a new option yourself that will do what you need. As well as all the classes being sealed & obfuscated, there's some internal infrastructure that has to be updated when new options are added.

A workaround you can use, if you are using the SDK, is to write your own state machine that parses the object text in the relevant Text property if the objects aren't equal, and determines if the only difference is the WITH ENCRYPTION property missing in one.

Simon C

mah530

How do I acces the text property - didn't see it in the Difference class
Thanks

Simon C

The Text property is available on the DdlTrigger, Function, StoredProcedure, Trigger and View classes, accessible through the ObjectIn1 and ObjectIn2 properties of the Difference class

mah530

Is there any way for me to modify the ObjectIn1, ObjectIn2 properties before doing the compare? Would I have rights to modify the text in these objects (trigger text, stored proc text, etc)? I would want to remove the 'WITH ENCRYPTION' phrase before the actual compare is performed.

Is this possible? Without me having to write the actual compare process?

Simon C

No, I'm afraid you're unable to modify the object model after the database registration has taken place. You would have to modify the objects on the database itself to change the resulting object model in SQL Compare.

Simon C

mah530

thank you for the reply. looks like I will not be able to use SQL compare.

Please consider adding the option to 'Ignore WITH ENCRYPTION' in a future release of the product.

Thanks