Data Compare for SQL Server - Column Mapping Workaround

When you need to compare and synchronize data in two databases, in most cases, the schemas of the databases in question are the same, so Data Compare for SQL Server 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.

Data Compare for SQL Server 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 allows you to compare and synchronize 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.

Download Data Compare for SQL Server now and try for yourself. 

Read More

How to automate the deployment 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 specifically designed 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 versions from SQL Server 2000 to SQL Server 2019, Script Executor also supports MySQL and DB2 and SQL Server Compact Edition.

Download the free, fully functional trial from: https://www.xsql.com/download/script-executor

Read More

One-click database compare - comparison sessions

All fours xSQL Software's database compare tools, Schema Compare for SQL Server, Data Compare for SQL Server, Schema Compare for Oracle, and Data Compare for Oracle, come with an awesome feature called "comparison sessions". So, what are comparison sessions and how do they help you? 

When you first compare two databases, whether it is two Oracle databases or two SQL Server databases, and whether you are comparing the schemas or the data, depending on the complexity of your scenario, the default tool settings may not satisfy your requirements and hence, you may need to spend some time configuring everything right, for example, if you are comparing a development database to a production database comparing and synchronizing permissions may not make sense so you want to exclude those, you may have a set of test tables on development that you do not want in the production, when comparing data you may want to chose a comparison key other than the primary key, etc. This can sometimes be an elaborate effort requiring hours to get everything just right and the last thing you want is to have to repeat that process the next time around. That is where the comparison sessions come in - every choice you make is automatically stored into a session that you can name, and a "tile" will be displayed on the workspace for each session. 

Each session tile shows the session name, the names of the two databases participating in the comparison, the last time that comparison was executed, and the number of times that comparison has been executed. 

You can change the color of the tiles and add a description for each session. Up to 100 comparison sessions can be stored in a workspace. By default the session tiles are ordered based on the "last execution date" but you can change the order in the "Application Settings".

Once a session has been saved you can simply click on the tile to run the comparison - just one click. 

You can download the SQL Server compare tools here and the Oracle compare tools here. 

Read More