SQL Compare - Exclude User problem
Jamie Clayton
Posts: 19
G'day,
I'm currently remotely supporting a clients databases and using the SQLCompare.exe command line + *.xml configuration arguments file to compare databases with snapshots.
One of the problems I'm facing is to do with Users (Microsoft Groups) in the clients database and how SQLCompare.exe excludes them
I have an entry in my arguments *.xml file like
<?xml version="1.0" encoding="utf-8"?>
<commandline>
..
<exclude>User:BNE-MYCUSTOMER\~ApplicationGroupName (BNE)</exclude>
..
</commandline>
But following the compare with a snapshot I get the following statement incorrectly.
PRINT N'Dropping users'
GO
DROP USER [BNE-MYCUSTOMER\~ApplicationGroupName (BNE)]
GO
If I run the full SQLCompare Application I can exclude the user successfully, so I'm assuming this is a syntax error with the characters in the <exclude></exclude> xml. I've tried a number of combination and they all seem to fail. Is it a PEBKAC or a bug with the command line?
I'm currently remotely supporting a clients databases and using the SQLCompare.exe command line + *.xml configuration arguments file to compare databases with snapshots.
One of the problems I'm facing is to do with Users (Microsoft Groups) in the clients database and how SQLCompare.exe excludes them
I have an entry in my arguments *.xml file like
<?xml version="1.0" encoding="utf-8"?>
<commandline>
..
<exclude>User:BNE-MYCUSTOMER\~ApplicationGroupName (BNE)</exclude>
..
</commandline>
But following the compare with a snapshot I get the following statement incorrectly.
PRINT N'Dropping users'
GO
DROP USER [BNE-MYCUSTOMER\~ApplicationGroupName (BNE)]
GO
If I run the full SQLCompare Application I can exclude the user successfully, so I'm assuming this is a syntax error with the characters in the <exclude></exclude> xml. I've tried a number of combination and they all seem to fail. Is it a PEBKAC or a bug with the command line?
Jamie Clayton
Senior Software Developer
Jenasys Design Pty Ltd, Australia
Senior Software Developer
Jenasys Design Pty Ltd, Australia
Comments
For example in your arguement file,
<commandline>
.....
<options>default</options> or <options>IncludeDependencies</options>or
<options>incd</options>
</commandline>
The default option turns the following options on, IgnoreWhiteSpace, IncludeDependencies, IgnoreFileGroups, IgnoreUserProperties, IgnoreWithElementOrder.
IncludeDependencies and it's alias incd, if selected as an option will also set the Include Dependencies to on.
Eddie Davis
Red Gate Software Ltd
Technical Support
E-Mail: support@red-gate.com
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
I had been using the or elements without much luck.
I'm using Role based security within the database, so it tested the "Ignore users' permissions and role memberships" in the project properties for the SQL Compare GUI and got very close to the answer I was expecting.
If I then remove all my entries and replace it with
I get completely the wrong results in the output script file. (I got this from the "options enumeration" help file). [Comparison to a snapshot].
To answer your question about Objects dependant on the user, I'm not sure exactly how to find the answer to that. However all the objects in the database are using DBO.ObjectName and there are no other user/group owned objects as this an application database managed by out company.
Here is how I'm doing it.
1. Have a *.bat file with the following 2. Config .xml file with
I'm expecting to have the Results\xyz_Test.sql file to be blank (non existant) because the only differents are the user and role membership of that user "BNE-MYCUSTOMER\~ApplicationGroupName (BNE)".
I guess I'm just having troubles working out the difference when driving the application from a command line (*.xml) file to the GUI, however I've got dozens of databases that are setup in similar ways, which I would like to compare nightly to ensure we don't have "db Administrators" breaking a database structure.
Senior Software Developer
Jenasys Design Pty Ltd, Australia