6.2 Compare is errantly flagging source control procs(dt_)

dksqlnut

I'm fairly new to SQL compare and have nailed down the process of copying DEV db schema to PROD. So I am staging the new version of the db on the same server as the DEV version. Let's call the db’s DEVSERVER.DB1 (<SQL Instance>.<db name>) and DEVSERVER.DB2 where red gate produced a working script to copy the schema from DEVSERVER.DB1 into an empty db of DEVSERVER.DB2. All is cool (after ironing out the broken code) so I ran a compare against the DEVSERVER.DB1 and DEVSERVER.DB2 expecting no differences. It's not a big deal but the compare tells me that 31 procs named dt_* ONLY exist in DEVSERVER.DB1. I visually checked this and it is NOT true. Both db's have all 31 dt_* procs. Being Windows and all, I stopped and started all programs involved (Compare 6.2 SQLEM) and eventually rebooted my workstation. I don't have the option of restarting the SQL server.

These dt_* procs are used for source control and I can continue to ignore the messages but it sure would be better to resolve the problem rather than working around it.

Any help is greatly appreciated.

DK the SQL nut

Brian Donahue

Hi DK,

Is it possible that the dt_ set of stored procedures are 'system'-type objects? SQL Compare ignores all objects that are part of a database's system schema, for instance metadata (syscomments, sysobjects, etc). If the source control tables are marked as system in one database and not in the other, they would appear as missing objects.

Hope this helps!

rgribble

Jumping in on an old post here but i thought i would let people know that indeed this is the problem, and occasionally you do find that the dt_* objects are not correctly tagged as system objects in a particular database.

We had this happen a few times, and although we have never identified what actually causes it to happen, we have the following script which will fix up things so these objects are once again tagged as system


exec sp_MS_marksystemobject 'dtproperties'
exec sp_MS_marksystemobject 'dt_addtosourcecontrol'
exec sp_MS_marksystemobject 'dt_addtosourcecontrol_u'
exec sp_MS_marksystemobject 'dt_adduserobject'
exec sp_MS_marksystemobject 'dt_adduserobject_vcs'
exec sp_MS_marksystemobject 'dt_checkinobject'
exec sp_MS_marksystemobject 'dt_checkinobject_u'
exec sp_MS_marksystemobject 'dt_checkoutobject'
exec sp_MS_marksystemobject 'dt_checkoutobject_u'
exec sp_MS_marksystemobject 'dt_displayoaerror'
exec sp_MS_marksystemobject 'dt_displayoaerror_u'
exec sp_MS_marksystemobject 'dt_droppropertiesbyid'
exec sp_MS_marksystemobject 'dt_dropuserobjectbyid'
exec sp_MS_marksystemobject 'dt_generateansiname'
exec sp_MS_marksystemobject 'dt_getobjwithprop'
exec sp_MS_marksystemobject 'dt_getobjwithprop_u'
exec sp_MS_marksystemobject 'dt_getpropertiesbyid'
exec sp_MS_marksystemobject 'dt_getpropertiesbyid_u'
exec sp_MS_marksystemobject 'dt_getpropertiesbyid_vcs'
exec sp_MS_marksystemobject 'dt_getpropertiesbyid_vcs_u'
exec sp_MS_marksystemobject 'dt_isundersourcecontrol'
exec sp_MS_marksystemobject 'dt_isundersourcecontrol_u'
exec sp_MS_marksystemobject 'dt_removefromsourcecontrol'
exec sp_MS_marksystemobject 'dt_setpropertybyid'
exec sp_MS_marksystemobject 'dt_setpropertybyid_u'
exec sp_MS_marksystemobject 'dt_validateloginparams'
exec sp_MS_marksystemobject 'dt_validateloginparams_u'
exec sp_MS_marksystemobject 'dt_vcsenabled'
exec sp_MS_marksystemobject 'dt_verstamp006'
exec sp_MS_marksystemobject 'dt_verstamp007'
exec sp_MS_marksystemobject 'dt_whocheckedout'
exec sp_MS_marksystemobject 'dt_whocheckedout_u'



Hope it helps