The Script Executor you have been waiting for

We have just released Script Executor 3 with support for SQL Server and MySQL - deploying your Sql Scripts to multiple target servers has never been easier.

The previos version of Script Executor has been renamed to "Script Executor Community Edition" and will continue to be available free of charge from our website.

Check the new Script Executor out - you will be impressed!

Download your free trial now: http://www.xsql.com/download/script_executor/

Best way to compare data in two SQL Server databases

So you need to compare the data in two SQL Server databases – maybe just the data in certain tables, maybe certain rows only and maybe you are just interested in certain columns. And what if the tables you need to compare don’t have a primary key defined (well, they all should but it is not rare to see cases when they don’t) how can you approach the task? A quick search on google will reveal an abundance of tools all claiming to do what you need so how do you know which one to pick?

We have made the task simple for you. We have tested every single one of those tools and the bad news is that with the majority of them you would be wasting your time. There are only very few that do the job right and we are happy and proud to say that xSQL Data Compare is the best tool you will find in terms of breadth of functionality it provides, flexibility it gives the user, performance with large databases and last but not lease cost. No, you don’t have to take our word for it – we have thousands of very happy users the majority of which have not had to pay a dime for it. You see, xSQL Data Compare is completely free for SQL Server Express with no restrictions and it is also free for other editions of SQL Server as long as the databases being compared don’t have more than a certain number of objects.

Here are some highlights of xSQL Data Compare:

• You can compare the data in two sql server databases or just select tables in those databases;
• You can map tables to be compared any way you want regardless of the table names;
• If xSQL Data Compare can’t find a predefined unique key on a table it allows you to define it on the fly as a combination of one ore more columns in that table;
• You can set filters on each table if you wish to compare only a subset of the rows;
• You can include or exclude columns from the comparison;
• You can choose to use bulk insert for the synchronization if you expect that a large number of rows will need to be inserted in the target table.

xSQL Data Compare supports SQL Server 2008, SQL Server 2005 and SQL Server 2000.

You can download your copy from: http://www.xsql.com/download/sql_server_data_compare/

Documenting your databases – one tool does it all

Documenting anything is usually a painful task that everybody talks about but no one likes to do. I have seen one client after another with virtually no database documentation and no awareness of the incredible amount of time that especially newcomers spend on understanding how the objects relate to each other, how the data is stored, and what kind of constraints they need to be aware of!

Here is a perfect excuse that I have heard way too often: “by the time we are done documenting things have already changed so the documentation becomes outdated before it is even completed”. That might have been true in the distant past but not any more. With the right tool you can document your databases like SQL Server, DB2, Oracle, MySQL, Informix virtually any platform, in minutes – you can even create a job that automatically updates the documentation every day. One of the best tools out there to do the job is xSQL Documenter which allows you to simultaneously generate compiled CHM and / or HTML documentation for all your databases.

What’s even better, a great tool like this does not cost a fortune – for less than $200 you will get an incredible tool that will save your organization hundreds of hours of work.

xSQL Documenter supports the following platforms: SQL Server, Oracle, DB2, MySQL, Informix IDS, Sybase ASE, Sybase SQL Anywhere, PostgreSQL, Access, VistaDB, ENEA Polyhedra;

You can download your copy of xSQL Documenter from http://www.xsql.com/download/database_documenter/

What’s the interest on the $819B stimulus bill?

Assuming that the government would be able to finance the stimulus bill by selling $819B worth of Treasury bonds at let’s say 4.5% to the Chinese government and others the yearly interest WE will pay on those bonds is just about $36.9 billion for a 30 year total of $1,105,650,000,000 – that would be about $1.1 trillion dollars in case you are having trouble reading that number. And just in case you are trying to envision what $1 trillion dollars look like check this out: http://www.cnn.com/2009/LIVING/02/04/trillion.dollars/index.html

So, what does this have to do with SQL Server tools? Nothing really other than these numbers are so amazingly big that it is hard to just ignore them and focus on our job which is to build awesome tools for SQL Server database administrators and developers.

Another presentation on RSS Reporter

Chad Miller did another presentation at the SQL Saturday Tampa 2009 focused on RSS enabling System Administration information - utilizing RSS Reporter to automate the daily DBA checklist by aggregating job status information from across multiple SQL Servers and databases. More details can be found on Chad's blog: http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!267.trak

Deploying database schema changes to hundreds of clients

This post is directed to software publishers who have published software that uses SQL Server on the back end. Here is a simple scenario you run into: you release the software and within a couple of months you have successfully deployed it to tenths of customers. In the meantime you have discovered a flaw on a stored procedure which needs to be changed, you want to add a couple of reports for which you need to add a couple of new supporting views, a column on a table needs to be changed from varchar(50) to varchar(100) etc. – you get the picture. The question is: how do you make those changes on all those customers’ sites? You could send them a change script that will alter and add those objects in the database but a lot of the customers don’t know anything about SQL Server and don’t won’t to deal with those scripts. Furthermore, if this is not the first iteration of changes and the customers are using different versions of your database the script you send must be customized for each version. The main point here is that sending the customers a t-sql change script and asking them to apply it is generally not going to work.

So, what do you do? Well, you use xSQL Builder which was designed to alleviate this kind of trouble. xSQL Builder is extremely easy to use but at the same time, if your scenario is more complicated it gives you all the flexibility to customize it to best fit your needs. How does it work? You go through a simple wizard that takes no more than a couple of minutes and allows you to choose the source or “master” database which you want to deploy to your customers, set a few parameters and generate an executable package that you can then send to your clients. The client runs the executable which compares the embedded schema of the source/master database with the client’s version of the database, generates a synchronization script, executes the script and emails you the results. Now, you have to admit that is a cool and extremely efficient way to deploy your database schema changes.

You can download your copy of xSQL Builder from: http://www.xsql.com/download/sql_database_deployment_builder/

Wrap your insert, update and delete statements on a transaction – ALWAYS

The main objective of a transaction is to ensure that either every statement within that transaction is executed successfully or none of them. So, you would logically wrap in one transaction related statements that should be executed together. A classic example is that of transferring money from one account to another – the action of subtracting the money from account A must happen together with the action of adding that same amount of money to account B (either both or none) otherwise, if let’s say the system crashed right after subtracting the money from account A and before adding them to account B the money will disappear! So, great, when one understands this a legitimate question comes to mind – what’s the point of wrapping a single statement in a transaction?

Well, we are humans and as such we are prone to making mistakes, sometimes, big and costly mistakes so, wouldn’t it be nice to “erase” our mistakes when we realize that? That’s what a simple Begin Transaction statement provides us – the ability to rollback our mistakes. How does one realize that he made a mistake – the first indicator is the “number of rows effected” which SQL Server reports – if that number is larger than you expected then maybe you made a mistake on your statement. How else can I tell? The cool thing is that SQL Server will let you read the uncommitted rows and see what your statement did - so you can issue a select and review the rows you just updated for example. If you do realize that you made a mistake you simply issue a rollback and all is good – it is like it never happened. If you are sure your statement did what you intended it to do then you issue a commit transaction and the changes you just made become permanent.

So again, be diligent and wrap every update, delete or insert statement in a begin transaction / rollback / commit transaction. It is much better to be safe then sorry!

Applies to: SQL Server, SQL Server Mangement Studio, Query Analyzer