Wednesday, May 9, 2012

SQL Data Compare column mapping workaround

In most cases when you need to compare and synchronize data in two databases the schemas of the databases in question are the same so xSQL Data Compare automatically pairs the tables and maps the columns based on names. However, there are cases when you might need to compare data in two tables with different names and different column names.

xSQL Data Compare provides the facility that allows you to map two tables with each other regardless of the table names, but the columns are automatically paired and there is no direct way to pair two columns with different names. Fortunately, there is a quick and easy workaround to this problem. Since xSQL Data Compare provides for comparing and synchronizing sql server views as well you can do the following:
  1. Create two views (one for each table you wish to compare) with identical alias column names 
  2. Compare and synchronize those two views instead – the columns will be mapped automatically since they have the same names but the updates during the synchronization will happen on the underlying columns which have different names.
A couple of limitations to be aware of:
  • Since SQL Server does not support SET IDENTITY_INSERT ON|OFF on views, the insert statements might fail if one of the view’s underlying tables contains an identity column. 
  • Data Compare cannot synchronize views that contain large binary fields such as varbinary(max) and image, or views with large text field such as varchar(max), nvarchar(max), text and ntext.

Monday, May 7, 2012

xSQL Comparison Bundle – new build available

A new build of xSQL Comparison bundle containing a few minor fixes is available for download: http://www.xsql.com/download/package.aspx?packageid=10
Issues addressed by this build include:
  1. Schema Compare tool (xSQL Object)  - fixes an issue with check constraints that depend on user-defined functions. Symptom:  database schema comparison fails with the error “Failed to generate check constraints synchronization script. Object reference not set to an instance of an object”; 
  2. Schema Compare tool (xSQL Object)  - fixes an issue with default constraints that depend on user-defined functions. Symptom: database schema comparison fails with the error “Failed to generate check constraints synchronization script. Object reference not set to an instance of an object”;
  3. SQL Data Compare tool - fixes a problem that occurs when the first column of the chosen data comparison key is created as a descending column. Symptom: SQL Data Compare tool fails to identify the rows that exist in both tables being compared. 
  4. SQL Data Compare tool - fixes a problem with the data candidate index defined on columns that allow NULL values. Symptom: Data Compare prepares update queries with where clause as “and fieldname = null” in cases when the key used includes a nullable column.

Friday, May 4, 2012

How to automate the execution of t-sql scripts

Following is a request we received from a client (verbatim): “I need to automate the execution of t-sql scripts (multiple t-sql scripts in order). Schedule the execution if possible on one or more databases. For example: I have the following scripts:
   1.sql : create tables
   2.sql : data insertion
   3.sql : security role creation, access rights, etc.
I need to run those scripts, in sequence, on the DEV, TEST and PROD sql server instances”
Script Executor was designed exactly to handle scenarios like this, in fact this is one of the simplest t-sql script deployment scenarios that our clients use Script Executor for. Here is how you can easily handle this: 
  1. Launch Script Executor and click on File / New Project
  2. On the “Databases” panel on the left side right click on “All Databases” and then click on “Add Databases…” and add all three databases DEV, TEST and PROD to that database group. Of course you can rename the database group and create new database groups. In a more complex deployment scenario you might want to create different database groups for development, test and production databases. 
  3. Click on the “Scripts” tab on the left panel , right click on “All Scripts” and then click on “Add Scripts…”  and add the scripts you wish to deploy. You can easily order the scripts by right clicking on a script and then moving that script up or down in the sequence. 
  4. Next go to Package / Configure… - Script Executor will perform an automated mapping of Database Groups to Script Containers (see the screen shot below). You can then easily tweak the mapping based on your needs.  
  5. Now if this is an on-demand deployment you can utilize the GUI to execute the package. Script Executor will execute each script against each database the script is mapped to and on completion you will see a detailed deployment report. You will also be able to browse through the result sets if one or more of those scripts returned any rows. 
  6. If you wish to automate this t-sql script deployment, that is schedule it to happen at a certain time then you have two options:
    1. You have Script Executor installed on the machine from which you will do the deployment. In this case you can do the following:
      1. Save the script deployment project
      2. Create a batch file that invokes the Script Executor command line to execute the project
      3. Use Windows Task Scheduler to schedule the execution of the batch file. 
    2. You don’t have Script Executor installed on the machine from which you will do the deployment. In this case you can go to Execute / Build Executable… to build a ready to deploy executable package that embeds all the target database information as well as the t-sql scripts you wish to deploy. You can then put that executable package on the machine from which you will do the deployment and you are ready to go. Use Windows Task scheduler to schedule the execution of this executable package and you are done.
Script Executor is the most powerful and robust t-sql script deployment tool in the market – it can handle any script deployment scenario you might have at a very low cost. The ability to wrap t-sql scripts in a ready to deploy executable eliminates the need for purchasing multiple licenses and makes it easy to deploy to remote client sites. In addition of SQL Server 2012, SQL Server 2008, SQL Server 2005 and SQL Server 2000, Script Executor also supports MySQL and DB2 and SQL Server Compact Edition.
Download the free, fully functional trial from: http://www.xsql.com/download/package.aspx?packageid=40