Ignoring non-existant dependencies?

andersom

I'm trying to use the product to keep a development master version of my production databases in sync with changes that are promoted to production, however I'm running into some problems with dependency checks.

The command line I'm using is:

"E:\Red Gate\SQL Compare 6\SQLCompare.exe" /verbose /exclude:user /exclude:role /exclude:schema /options:iu,iup,irpt /server1:prodserver /server2:devmasterserver /database1:proddb /database2:devmasterdb /sync

The problem comes in when the tool tries to update stored procs and functions on the dev server that reference objects in databases that don't exist on the dev instance. For example, this is one of the current error messages I'm experiencing:

SQL Compare Command Line V6.0.0.1410
==============================================================================
Copyright c Red Gate Software Ltd 1999-2007

Serial Number:

SQL Compare running with option: IgnoreUsers (OK).
SQL Compare running with option: IgnoreUserProperties (OK).
SQL Compare running with option: IgnoreReplicationTriggers (OK).
Registering databases
Creating mappings
Comparing
Replaying user actions
Comparing database prodserver.proddb with database devmasterserver.devmasterdb...
Creating SQL
Synchronizing databases
Error: Error Comparing prodserver/proddb vs
devmasterserver/devmasterdb : Could not find server 'adifferentprodserver' insysservers. Execute sp_addlinkedserver to add the server to sysservers.

Obviously I want to keep production and dev as disconnected from each other as possible, so I don't want to link adifferentprodserver on my devmasterserver.

If I manually execute the alter procedure statement that SQL Compare is choking on, SQL Server accepts it happily, so I'm assuming the problem must be some internal dependency check that SQL Compare is executing.

The big question therefore is: can I tell SQL Compare to either disable the dependency checks altgether, or alternately generate a warning message instead of a failure?

JonathanWatts

Hi there,

SQL Compare retrieves the schema extractly as it is in your production environment and then tries to execute in your development environment. It doesn't alter the syntax in anyway, therefore if your development server does not have access to a linked server that an object in your production database requires then the synchronisation will fail.

The error message given is actually SQL Server's error message not SQL Compare's as SQL Compare does not check for dependencies outside of the target database. SQL Compare cannot at the current time refactor objects on the fly to avoid such problems.

At the moment I am at a lost to explain why the manually execution of the alter proc statement produces a different result, unless the linked server information has been changed in the proc?

Regards,

Jonathan

andersom

Hmm... I'll dig a little deeper then, perhaps the statement I was able to execute manually wasen't the same one that generates the error message.

andersom

The databases I'm comparing have many thousands of objects. Is there a way to get a more descriptive error message, ie. something that tells me which statement caused the error?

andersom

Nevermind, I generated a SQL Script for all the changes and can see where the problems are. You're correct, it's a SQL Server message, not a SQL Compare message.

Thanks.