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.
DROP/CREATE" section allows to
configure the Diff to generate DROP / CREATE sequences for the
selected type of objects instead of using ALTER.
'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
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.
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.
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