Wednesday, September 21, 2016

xSQL Schema/Data Compare database list is empty

Applies to: xSQL Schema Compare v 7.0.0 to v 9.0.0; xSQL Data Compare v 7.0.0 to v 9.0.0

Summary: When adding databases to the workspace both in xSQL Schema Compare and xSQL Data Compare the first step after connecting to the specified SQL Server is to read the list of databases from the server and make that list available for the user to select the databases he/she wishes to work with. SQL Server stores the list of the databases in the master database so in order to obtain that list the account that is used to connect to the SQL Server instance should have permission to read from the master.

In the case of SQL Server this is almost never a problem as all users usually have permission to read from the master however, the situation is different in SQL Azure. Often, the developers only have access to certain databases on an Azure account and do not have permission to read from the master. Therefore, in these cases, the Schema and Data Compare tools are not able to read the list of available databases.

Symptoms: After providing the login credentials on the "Add Database" dialog window and clicking on the "Refresh" link-button to populate the "Databases" window the list fails to populate and the database window remains empty. You are thus not able to proceed with the comparison and synchronization operation.

Cause: The account used for connecting to the SQL Server instance or SQL Azure does not have permission to read from the master database.

Resolution:

  • Install the latest build from our website - builds v. 9.0.1.0 and higher include a fix that eliminates the need to read from the master db, but only for the default database for that user. If you need to add a database other than the default one then continue reading. 
  • Grant read permission on the master database to the account you are connecting with. 
  • Login with a different account that does have read permission on the master database.

Future versions of the compare tools: both comparison tools will eliminate the need for reading from the master database by allowing the user to specify the database(s) to be added.

Workaround: xSQL Schema and Data Compare tools come with Command Line Utilities that allow you to specify all the comparison and synchronization details, including the database name, in a XML file which is given as an argument to the comparison command, thus eliminating the need to read from the master. The command line utilities are intended primarily for unattended, automatic database comparison and synchronization operations and they provide great convenience and utility for that purpose but you don't get the benefits of the interactive GUI.



0 comments:

Post a Comment