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.

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.

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

Today's free product code

Today's free license code will appear on one of the product pages at 12 noon (valid for that product only). As explained in the email the code will appear at the end of the product description at the top of the product page.  Time zone: (UTC-05:00) Eastern Time (US & Canada)

My database is a jungle, I need an intelligent search engine

Thousands of stored procedures and no good way to find the one you are looking for, so what do you do? You write your own stored procedure thus, not only wasting valuable hours but also contributing into increasing the “density of the jungle” making it even harder to find anything in the future. Multiply this by 260 days a year and 10 other people like you and you get the picture. My guess is that if one would do a complete inventory of the stored procedures in a database would likely find a lot of repetition – multiple stored procedures created by different people at different times doing exactly the same thing.

What if a google type search engine could take queries like “get all north-west region customers that purchased product  x during a given period of time”, search through the database schema and return all the stored procedures that potentially answer this query as well as the list of tables that would potentially be involved in answering such query? That would be beautiful, wouldn’t it?

However, the search engine would need some help to be able to do this. Adding extended properties was a great step in the right directions - at least someone that is diligent enough has a way to tag the objects with keywords and phrases that can later be used to efficiently locate those objects.  I would argue that it would be beneficial if there were at least two predefined mandatory extended properties: “description” and “keywords”. I know that many DBAs and developers might find such enforcement a bit annoying but in the long run I think most of them will be thankful.

Related products/tools:

SSMS - lets you filter objects based on the name, schema, owner and creation date but it does not provide for filtering based on the extended properties or the definition of the object. However, you can always query the system tables - not ideal but better than nothing.

xSQL Documenter:  will document the extended properties and the object definitions so you can technically go into the documentation and search for an object.  The drawback is that the documentation becomes outdated soon after it is generated. Of course you can refresh it but that means more time.

Object Search:  a free tool that allows you to search in the definition of the object using a regular expression as the matching criteria. Current version does not look into the extended properties.

10 days, 10 free Oracle Data Compare licenses each day

Starting today (March 26, 2012) we will be giving away 10 Oracle Data Compare licenses (a $349 value) for free, every day for 10 days. The promo code ODC10DAYS will be active for a few minutes each day. The starting time and the estimated time interval will be announced at least 2 hours in advance on twitter (www.twitter.com/xsqlsoftware) and facebook (www.facebook.com/xsqlsoftware) every day. Once the first 10 licenses are claimed the promo code will be de-activated and you will have to wait until the following day to get your free license.

In all, during the next 1o days we will give away 100 Oracle Data Compare licenses or $34,900 in free software.

xSQL Builder – the tool for software publishers

If you publish software that uses SQL Server on the backend then you are familiar with the pain of upgrading your clients from one version of your software to another. Some of the most common challenges you face include:

• The customers don't know and they don’t want to know anything about SQL Server or t-sql so sending them a set of scripts to execute on the target server is not going to help;
• Different customers may be on different versions of your software so you need to provide multiple upgrade paths;
• You may have customized your solution for certain clients so the "standard" upgrade won't work for those cases;
• You do not have direct access to the customers' databases;
• You have hundreds of customers you need to distribute your software to.

Those were the challenges we set out to tackle when we decided to build the xSQL Builder. Here is how xSQL Builder works:

1. A simple wizard guides you through a few steps that lead to the creation of an executable package that contains all the necessary information required to upgrade your customers’ databases. 
2. When the customers executes the package the following happens:
1. If applicable, a set of pre-synchronization scripts is executed against the target database;
2. The schema of the target database is compared with the schema of the master database which has been embedded in the executable package;
3. Synchronization script is generated and executed against the target;
4. If applicable, a set of post-synchronization scripts  is executed against the target;
5. A detailed log is emailed back to you so that you know exactly what happened at the customer's site.

The beauty of this is that the deployment package "does not care" what version of your database the customer is on.

 

Of course the wizard is quick and easy but what if you need to tweak something that the wizard does not provide for? We thought of that too – xSQL Builder comes with a set of customizable C# templates (C# classes) that contain the compare and synchronization code that runs on the client machine. xSQL Builder ships with a VS.NET 2005 C# project that you can run or modify as needed.

 

Download now and see for yourself what you have been missing all this time!