AdeptSQL Diff Online Help - Procedures, Views, etc

opt_procs

The QI/AN settings. There are two settings (QUOTED_IDENTIFIERS and ANSI_NULLS) which MS SQL Server actually keeps with each stored procedure (and also view, function or trigger). These checkboxes control if the QI and AN settings should be considered part of procedure definition and scripted before ALTER or CREATE PROC.

They also affect the comparison of procedures (and other code objects): if two procedures are identical except for their QI and/or AN settings, they will be marked as changed only if the scripting for SET QI / SET AN is enabled.

Additionaly, there is the "Always/When needed" sub-option for the QUOTED_IDENTIFIER setting. The QI setting only affects a procedure when it has any names or string literals enclosed in double-quotes, otherwise the difference in QI can be safely ignored. So if you choose "Always", the Diff will detect (and script) changes in QI for all code objects, otherwise it will do so only for objects having some double-quoted tokens in their bodies.

The "Script DROP/CREATE" section allows to configure the Diff to generate DROP / CREATE sequences for the selected type of objects instead of using ALTER.

Ignored 'WITH' options. When checked, these options force the Diff to ignore the differences in 'WITH RECOMPILE' and/or 'WITH EXECUTE AS ...' during the comparison as well as preserve them when producing the synchro script.

Suppose a procedure P in the left-hand database doesn't have "WITH RECOMPILE", whereas the procedure P in the right-hand (target) database does have this attribute. If "Ignore WITH RECOMPILE" is set, this will not be considered a difference. If the two procedures don't have other differences, they will be marked as identical in the schema tree and not included into update scripts. If there are other differences, the procedures will be scripted using the WITH RECOMPILE setting of the procedure in the target database. This is the same logic as used when comparing/scripting "NOT FOR REPLICATION" or "WITH ENCRYPTION" attributes.

Literal EXEC AS comparison. In SQL2005, you can specify execution context of a procedure (or other code object) using "WITH EXEC AS <user>|CALLER|SELF" clause. Normally the Diff would find out the actual <user> and take into account any "owner mapping" which might be in effect. For example, if the [dbo] on one side is associated with some [User1] on the other side, then "CREATE PROC ... WITH EXEC AS dbo" will be the same as "CREATE PROC ... WITH EXEC AS User1". Also "...WITH EXEC AS CALLER" will be identical to no "EXEC AS" at all and so on. But when the literal comparison is turned ON, the Diff will just compare EXEC AS texts extracted from procedure bodies, so in any of the examples above you will have a synchronizable difference.

Disable renaming for code objects: the Diff can be configured to associate with each other stored procedures that have different names but identical code. The Diff would then synchronize them using 'sp_rename'. However, renaming of code objects in MS SQL doesn't work quite correctly. The server renames them in sys.objects, but leaves the old names in the stored code. This option disables renaming for code objects (not only stored procedures, but also functions, triggers and views), re-creating them instead.

Encrypted objects. MS SQL Server can keep procedures, functions, triggers and views in the database in encrypted form, which means accurately comparing and scripting them is impossible. If one database has an encrypted object and the other has the same object in non-encrypted form, there is no way to tell if they are actually identical or not.

If the "Always consider changed" option is checked, encrypted objects in the target database with non-encrypted counterparts in the source DB are always considered outdated and synchronized, otherwise they are assumed to be identical and ignored.

The "Keep encrypted" option specifies whether such object should be scripted WITH ENCRYPTION in an update script. It is logical to assume that if a procedure was encrypted in the target database and we are updating it, we should make sure it remains encrypted, even if its counterpart in the source database isn't.

"Protect from tracing" causes a "magic" comment to be generated in encrypted procedures: "WITH ENCRYPTION /*sp_password*/". When a procedure "WITH ENCRYPTION" is sent to the server, its full definition can be caught in a MS SQL Profiler log, which is a security risk. However, if the profiler finds "sp_password" anywhere in the SQL (even in a comment), it would not show the statement text in the log.

The "Script with SQLShield encryption" is only useful for those who have SQL Shield server plugin installed. With this option ON, the Diff would generate another "magic" comment ("WITH /*sqlshield*/ ENCRYPTION") causing the plugin to encrypt this procedure in a special "unbreakable" way.