SQL Tools

SQL Jobs on a Mirrored Environment

2012-01-04T07:36:00.000-08:00

One of the little challenges a DBA has to deal with when setting up SQL Server mirroring is handling of SQL Server jobs. SQL Server mirroring works at the database level so SQL Server instance level objects are not mirrored. To achieve a transparent failover some of those instance level objects, like certain SQL Jobs related to your mirrored databases, need to be somehow "mirrored" as well. The objective is simple: any relevant jobs that were running on the principal, on failover should start running on the mirror. So we have to make sure the jobs are kept in sync and are activated at the right moment (you don't want those jobs to be running on the mirror until the failover happens).

One approach would be to have scheduled jobs that periodically syncs the SQL Jobs on the principal with those on the mirror and periodically checks the mirroring status to determine if the jobs on the mirror need to be activated.

Another simpler approach would be to use multi-server jobs. You can use a third server, like the witness for example, as the job "master" and do the following:

All the jobs that must be "mirrored" should be created on the job "master" server as multi-server jobs.
The target for those jobs is set to be the principal
Create a job on the job master server that checks the mirror status, let's say every minute
If a failover has happened and the mirror has become the principal then it changes the target server for those jobs.

Here is a simple stored procedure that checks the mirroring status and switches the target server for those jobs.

CREATE PROCEDURE [dbo].[usp_ChangeJobTarget]
@DBName varchar(256) = 'MyMirroredDB',
@Principal varchar(256) = 'MyPrincipal',
@Mirror varchar(256) = 'MyMirror'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @current_principal varchar(256)
DECLARE @principalID int
DECLARE @mirrorID int
DECLARE @jobname varchar(256)

   SELECT @current_principal = principal_server_name FROM sys.database_mirroring_witnesses WHERE database_name = @DBName
   SELECT @principalID = server_id from msdb.dbo.systargetservers WHERE server_name = @Principal
   SELECT @mirrorID = server_id from msdb.dbo.systargetservers WHERE server_name = @Mirror

IF @current_principal like @Mirror
   BEGIN
    -- principal has changed so we need to change the target for all jobs
    -- first let's get the complete list of jobs that target the principal
   DECLARE @JobList TABLE
    (
      [JobName] [varchar](256) NOT NULL,
      [Changed] bit NOT NULL
    )
    INSERT INTO @JobList
      SELECT [name], 0
         FROM msdb.dbo.sysjobs as a INNER JOIN msdb.dbo.sysjobservers as b on a.job_id = b.job_id
         WHERE b.server_id = @principalID
  -- now for each job on the @JobList we need to change the target from principal to mirror
      SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
         WHILE (@jobname IS NOT NULL)
          BEGIN
              exec msdb.dbo.sp_delete_jobserver @job_name = @jobname, @server_name = @Principal
              exec msdb.dbo.sp_add_jobserver @job_name = @jobname, @server_name = @Mirror
              UPDATE @JobList SET [Changed] = 1 WHERE [JobName] = @jobname
              SET @jobname = null
              SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
           END
    END
END
GO

A couple of notes to keep in mind:

There is a small delay on replicating a multi-server job. In other words, if you change a job or add a new job on the job master server it will take up to a minute for those changes to be reflected on the target server(s).
The above stored procedure only goes in one direction – changes the target from principal to mirror in case of failover. You will need to adjust it to make it work both ways – one simple way to accomplish this is to simply compare the current principal with the target for those jobs and if they don’t match then change the target.
The above script is provided as is - use at your own risk.

Last but not least: check out our SQL Server comparison and synchronization tools, xSQL Object and xSQL Data Compare they will make your database deployment a breeze. Also, check out our Script Executor - no other tool in the market today comes close to it.

Kindle time at xSQL Software - get one

2011-12-09T11:33:00.000-08:00

Don't "burn" your left over 2011 budget, put it to good use and get a great gift. Purchase a new, full xSQL Comparison Bundle license before Friday, December 16, 2011 and we will send you an awesome thank you gift, a Kindle. We will send the gift to whoever you direct us to send it to, anywhere in the United States and Canada. Here are the levels:

a Kindle when you purchase a single machine / single user license
a Kindle Touch 3G when you purchase a five machine / five user license pack
a Kindle Fire when you purchase a site license (unlimited activations within one physical site)

You must plug in the discount code KINDLE on the "discount" textbox on the shopping cart page otherwise no gift for you. Also, the purchase must be completed before December 16, 2011 - no exceptions.

Once you complete the order email us at sales@xsqlsoftware.com with instructions as to where you want your gift shipped to (a United States or Canada address only).

If you are not already, follow us on twitter and like us on facebook, we would appreciate it very much.

iPad and SQL Server

2011-10-27T04:44:00.000-07:00

In the last few months we noticed a jump in the number of downloads for RSS Reporter and were not sure what to attribute it to. So we started probing the users who were downloading the product and found that the increased interest in our RSS Reporter for SQL Server was directly related to an ever increasing number of SQL Server Database Administrators carrying an iPad around.

What's so great about RSS Reporter? A couple of things:

You don't need a client, just install the RSS Reporter on a machine with IIS that has access to the SQL Server instances you are interested on and then access it from anywhere with an internet browser, iPad, iPhone, tablet – anything that can read a standard rss feed;
Your executives need live data on their iPads, no problem, you add an ad-hoc query on RSS Reporter and simply send them the url;
You need to see what happened with your SQL Server Jobs last night, no problem. You can get an aggregate report from all your SQL Servers right on your iPad, and you can drill down and see exactly what happened.

If you haven't tried RSS Reporter check it out – it is completely free for a single SQL Server Instance and very reasonably priced if you want to monitor multiple SQL Server instances in one place.

After you try make sure to send us your comments and suggestions as on how we might make RSS Reporter even more useful to you.

xSQL’s Oracle Data Compare vs. the others

2011-10-21T08:32:00.000-07:00

When you do a search for data compare tools for Oracle a bunch of competing tools come up and at first look they all seem to be similar but once you start digging a bit deeper most of them get crossed off the list because they don’t amount to more than an amateurish effort. The very few that remain are hard to separate and you are inclined to go with the most known brand - after all, who has the time to thoroughly test all the available products!

We decided to take the time and do a more thorough review of a couple of those products that made the first cut and compared them to our Oracle Data Compare. Here is what we found:

Functionality: Oracle Data Compare supports Oracle Geometry, complex data types and unicode data; they DON’T
Performance: in the tests we did xSQL’s Oracle Data Compare completed the tasks 30% to 40% faster while keeping the memory consumption under 100 MB whereas the next best one was at 200 MB.
GUI: An argument can certainly be made in favor of simplicity but in the world of Oracle professionals having flexibility and control is important and xSQL’s Oracle Data Compare gives the user significantly more control and flexibility over the comparison and synchronization operation.

Download the free trial and see for yourself. If you wish to challenge our findings please do so – we will be delighted to hear from you! Email us at the info address on xsqlsoftware.com

Note: the introductory price of $174 expires on October 31, 2011. After that the price goes back to $349. To get the introductory price use promo code ORACLEPROMO on checkout.

Oracle Data Compare new build

2011-10-17T12:21:00.000-07:00

Just released a new build of Oracle Data Compare - support for Oracle collections was added. Download your three week trial copy here...

xSQL Data Compare for SQL Server - new build available

2011-10-10T11:10:00.000-07:00

We just published a new build of the xSQL Data Compare for SQL Server. Following is the list of enhancements and fixes included in this build:

Allows session naming by clicking on the name without launching it
Adds tooltip on session panel that shows the left/right databases, useful when the names of Sql Server/Database are too long and can't be fully seen in the UI
Fixes an issue with screen resizing
Fixes an issue with print wrapping for windows that support printing.
Adds a UI formatter for XML data types
Improves the process of comparing select tables

You can download the new build from http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=10&ProdID=xSQLBUNDLE

Oracle Data Compare released

2011-10-06T13:45:00.000-07:00

We have just released a new tool, Oracle Data Compare. The same awesome (to quote a user that called recently) data comparison and synchronization tool, many of you have become accustomed to, is now available for Oracle. You can learn more about the product here, and download a fully functional trial version from here.

Please note that from now until the end of October you can get a license for the low introductory price of $174 - use promo code ORACLEPROMO on checkout.

A big thank you goes to all the beta testers of Oracle Data Compare who gave us a tremendous amount of help during those last crucial months in the product development - we are grateful for all you have done!

How to deploy a SQL Server database to a remote host

2011-08-26T06:43:00.000-07:00

CASE 1: you have direct access to both the SQL Server where the source database is and the SQL Server where the target database is.

First time deployment

Backup / restore

Backup the database on the source
Copy the backup file to the target machine
Restore the database on the target
Create logins and set permissions as needed

Compare and Synchronize

Create database on the target machine (blank)
Use xSQL Object to compare and synchronize the database schemas of the source and the target.
xSQL Data Compare to populate the remote database with whatever data you might have on the source that you want to publish (lookup tables etc.)

Database exists in the target server

Compare and Synchronize

Use xSQL Object to compare and synchronize the database schemas of the source and the target.
Use xSQL Data Compare to push any data you need to push from the source to the target. Caution: be careful not to affect any data that exists on the target already.

CASE 2: You can not directly access the target server but you have a way to deploy SQL scripts on that server. As is indeed the case in most scenarios you also should have a way to get a backup of your database from that remote host. In this case follow those simple steps:

Restore the remote database on your local environment
Use xSQL Object to compare your source database with the restored database. Generate the schema synchronization script and save it.
Use xSQL Data Compare to compare your source database with the restored database. Carefully make your selections to ensure you push only the data you want to push from the source to the target. Generate the data synchronization script and save it.
Deploy your schema synchronization script to the target machine.
Deploy your data synchronization script to the target machine.

Both xSQL Object and xSQL Data Compare are completely free for SQL Server Express with no restrictions or limitations. Furthermore, for other editions of SQL Server the tools are free if the database has under a certain number of objects in it (current limitations are listed here).

Split String SQL function

2011-08-25T07:07:00.000-07:00

If you work with SQL Server you have, or at some point will, run into a situation where you have a string of separated values that you may need to involve in a join or simply generate a list out of. So, you need to split the string and dump the values in a table. Following is a simple Table-valued function that takes a string and a divider as parameters and returns a table containing the values into a list form (one value for each row). The parameters are defined as a varchar(1024) for the string of values and char(1) for the divider but you can change those based on your needs.

CREATE FUNCTION SplitString
(
    @SeparatedValues VARCHAR(1024),
    @Divider CHAR(1)
)
RETURNS    @ListOfValues TABLE ([value] VARCHAR(50))
AS BEGIN
      DECLARE @DividerPos1 int, @DividerPos2 int
      SET @DividerPos1 = 1
      SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, 0)

     WHILE @DividerPos2 > 0
           BEGIN
                  INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, @DividerPos2 - @DividerPos1))
                  SET @DividerPos1 = @DividerPos2 + 1
                  SET @DividerPos2 = CHARINDEX(@Divider, @SeparatedValues, @DividerPos1)
            END
           -- Now get the last value if there is onw
                  IF @DividerPos1 <= LEN(@SeparatedValues)
                       INSERT INTO @ListOfValues VALUES (SUBSTRING(@SeparatedValues, @DividerPos1, LEN(@SeparatedValues) - @DividerPos1 + 1))

        RETURN
END
GO

Once you create the function you can call it like this:
   SELECT * FROM [SplitString] ('value1|value2|value3', '|')
This will return:
   value1
   value2
   value3

Note that if the string starts with a divider like '|value1|value2|value3' then the first value returned will be a blank value.

Comparing 1TB database taking too long!

2011-08-24T07:20:00.000-07:00

We got a call yesterday from a customer who was using our xSQL Data Compare to compare a 1TB database. He was concerned that the comparison was taking a very long time – when he called it had been running for about 10 hours and was still going! While we don’t think there are many users out there comparing 1TB or larger databases we thought it might be helpful to those few out there if we explained why such compare may take 10 hours or more depending on the environment. Here are the factors to consider:

Connection speed - in a typical scenario xSQL Data Compare is running on a client machine let’s call that Client1 and the databases being compared reside on let's say Server1 and Server2. In order to compare those databases the whole 1TB worth of data from Server1 and another 1TB worth of data from Server2 will have to be "brought" over to Client1. So you are gradually transferring 2TB of data over the wire (or over the air) and depending on how fast the connections Client1 – Server1 and Client1-Server2 are this process alone may take not just 10 but 30 or 40 hours.
Processing power – xSQL Data Compare running on Client1 needs to pair those millions of rows and compare them one by one. If you have a slow machine, even if the data is readily available it will take a long time to process all that data.
I/O and local disk speed – 2TB worth of data is being temporarily stored on the Client1 hard drive and that process alone may take a long time.

In short, when you are comparing large and very large databases don't be shocked if the process takes many hours to complete. The important thing is that, provided you have sufficient disk space on the machine where xSQL Data Compare is running, the databases will be compared successfully and you will be able to generate the synchronization script.

Script Executor new build 3.5.1.1

2011-08-23T06:11:00.000-07:00

A new build of Script Executor that fixes a small issue with the command line logging options is available for download here...

Script Executor is the best tool for executing SQL Scripts against multiple databases on SQL Server, MySQL and DB2.

How to execute multiple SQL scripts from command line?

2011-08-22T11:15:00.000-07:00

How to execute multiple sql scripts from command line?

Script Executor provides one of the most efficient ways of executing SQL scripts from the command line. Let’s assume you have a long list of scripts that need to be executed routinely on a number of servers. Here is how you can do that in a few easy steps using Script Executor:

Launch the Script Executor user interface. Start a new project via File/New Project;
Add database(s) to the project;
Add Sql script(s);
Configure the mappings via Package/Configure. Mappings determine the databases that the scripts should run against. If there is only one database container and one script container, this step is not necessary;
Configure package options via Package/Options;
Save the projects and exit user interface;

Step number 4 above is where you will save a tremendous amount of time is you have a big number of scripts that need to be executed against certain databases.

Once the project has been saved it can then be executed from the command line as follows:
ExecCmd /p: /l:logging_type=1;path=

You can download a free trial version of Script Executor from here...

Wrong product, wrong time!

2011-08-19T08:51:00.000-07:00

I recently met and spent a few hours with one of our "competitors" who had dedicated over 10 months of his life to building what he thought would be the best data compare tool for SQL Server and then a lot more time trying to promote his work. Now disillusioned he was on a quest to salvage what he could from this investment. I asked him how he made the decision to develop a data compare tool for SQL Server and here is his rationale he presented:

I have a lot of experience with SQL server;
Building a tool that compares data in two SQL databases seemed like an easy thing that I could do in a couple of months so the risk wouldn’t be very big;
There were a lot of data compare tools in the market so there must be a strong demand for such tool.

I asked him what went wrong and here is what he said:

This turned out to be a lot harder than I thought. Instead of two months I quit my job and spent 10 grueling months and I still wasn’t happy with the results.
Two months after the release I had gotten a handful of downloads mostly from acquaintances and zero feedback.

I tried to make him feel better by telling him that he was just unlucky but in fact luck is the last thing one can blame for a situation like this. This is the result of what plagues many of the programmers I have known over years, namely;

Overestimating their ability while underestimating the effort required to get something done;
Misjudging the market;
Often believing they know better than the customer what the customer really wants!

The candid conversation I had with him made me think about this. The software business is a great business if you pick the right product and introduce it at the right time but if one of those two factors is wrong then it is a terrible business. Unlike in many other businesses there is basically nothing that can be salvaged in the case of a failed software business. The World's hard drives are cluttered with billions of lines of code that have never seen the "joy" of actually "doing something for real" – they have only been "called upon" by their creator during the development. It is kind of sad!

So before you jump into developing some “me too” tool just because you think you can do it please stop and ask yourself a few questions (not an exhaustive list by any means):

What is my goal? What will success look like?
Who am I building this tool for? How will this tool help them? How much are you really willing to pay for such tool if one was available today? Don’t overestimate this! In fact whatever number you spill out first divide it by two.
Who are the competitors? How is the market divided between them? Which ones do you believe you can take market share from and why do you believe that?
Have I thoroughly investigated the competing products? What do they do right? Where do they lack?
How fast can I bring this product to market (whatever time you come up please double or triple it)? How likely it is that during this time one or more of the competitors will release new and improved products that may pre-empt your move?
What will be that “killer” feature that is going to make the customers choose your product instead of those more established competing products?
What if things don’t work out? What is my risk?

If you use SQL Server and wish to check out the best SQL data compare and synchronization tool in the market you can download it from here. It is free for SQL Server Express with no limitations and also free for other editions of SQL Server if the databases have a relatively small number of objects.

xSQL Comparison Bundle new build available

2011-08-15T06:58:00.000-07:00

Just published a new build of xSQL Comparison Bundle that fixes the following:

Licensing issue with the Lite Edition. Our database comparison tools are currently free for SQL Server Express however, some users of the version 4 of the xSQL Comparison Bundle may have received the message "The Lite Edition does not support the databases you are trying to compare" even though the databases being compared were SQL Server Express databases.
Scripting issue with identity columns based on data types that support precision pr length such as numeric.

You can download the new build of xSQL Compare Bundle from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=10&ProdID=xSQLBUNDLE

SQL convert vertical list to horizontal

2011-08-12T12:26:00.000-07:00

Let's say you have a table that looks something like this:
CREATE TABLE [Source_Vertical](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GroupID] [int] NOT NULL,
[Col1_Group] [varchar](50) NULL,
[Col2_Group] [varchar](50) NULL,
[Col1_Individual] [varchar](50) NULL,
[Col2_Individual] [varchar](50) NULL)

You don't know how many rows there are for each GroupID and your job is to generate a list that contains all values associated with a given GroupID in a single row. Here is a simple way to do it:

First: create a target table that looks something like this:
CREATE TABLE [Target_Horizontal](
[GroupID] [int] NOT NULL,
[GroupValues] [varchar](max) NULL)

Next copy, adjust based on your needs and execute the following simple script (note I used a vertical line as a value separator - you can use whatever you need, just make sure the character you choose does not exist inside the actual values of the columns):
DECLARE @GroupID int
DECLARE @GroupID_Current int
DECLARE @Col1_Group varchar(50)
DECLARE @Col2_Group varchar(50)
DECLARE @Col1_Individual varchar(50)
DECLARE @Col2_Individual varchar(50)
DECLARE list_cursor CURSOR
    FOR SELECT * FROM dbo.Source_Vertical ORDER BY GroupID
OPEN list_cursor
FETCH NEXT FROM list_cursor INTO
@GroupID,
@Col1_Group,
@Col2_Group,
@Col1_Individual,
@Col2_Individual
SET @GroupID_Current = ''
WHILE @@FETCH_STATUS = 0
BEGIN

  IF @GroupID_Current <> @GroupID
   BEGIN
    INSERT INTO Target_Horizontal (Id, GroupValues) VALUES(@GroupID, @Col1_Group + '|' + @Col2_Group + '|' + @Col1_Individual + '|' + @Col2_Individual)
    SET @GroupID_Current = @GroupID
   END
  ELSE
   BEGIN
    UPDATE Target_Horizontal SET GroupValues = GroupValues + '|' + @Col1_Individual + '|' + @Col2_Individual WHERE GroupID = @GroupID
   END

  FETCH NEXT FROM list_cursor INTO
   @GroupID,
   @Col1_Group,
   @Col2_Group,
   @Col1_Individual,
   @Col2_Individual
END
CLOSE list_cursor;
DEALLOCATE list_cursor;

Finally you can take the results from the target table and dump them on Excel or wherever you need to.  Hope this helps someone!

No one can beat our data compare tool, no one!

2011-08-12T06:58:00.000-07:00

Yes, this may sound a bit cocky but we are tired of being modest and polite. We don't have an army of salesman to knock on your doors and we don't own and manipulate every user group there is but we focus on making our products the best there is and making them available to you at the lowest possible price including free. So, let's state it loud and clear – no one can beat our data compare tool, no one.

Performance: throw in a large database and most of the competing tools will not be able to handle it at all or will take forever and a day to complete the job. Try it and see how xSQL Data Compare will knock the socks off all the alternatives.
Usability: everything you need, nothing you don’t, all where it should be, one click compare – nothing approaches the efficiency of this interface.
Value:

Free for SQL Server Express – can anyone beat this?
Free for other editions of SQL Server when the databases being compared have a limited number of objects – anyone out there can match this?
Site license option, unlimited number of installations in your organization for a very modest price – can anyone come close to this?

Dare to compare for yourself, download our data compare and evaluate it against any and all competing tools in the market. Let us know what you think.

LinkedIn – great concept but practically useless!

2011-08-11T19:45:00.000-07:00

The other day I had a need to get introduced to an individual, let’s call her Jane Doe, who worked for company X. I searched for her on LinkedIn and was delighted to find that she was “in” my network – only one step away, directly connected to my friend “Jon”. So, I ask Jon to introduce me to Jane explaining why I wanted to talk to Jane. Jon’s response was the shocker – he said “I am really sorry but I barely know Jane (translation: I don’t really know Jane). I will send her an email but I am not sure if she will respond or not!” But, that defeats the very purpose of this media I said to myself!
Well. after this I decided to examine my contact list and to my dismay I realized that gradually a good number of individuals that I can’t remember ever meeting has made it into my list of connections! Who are those people? Why are they on my list? What does it really mean to me to have those people in my list and what does it mean to them to be on my list? I certainly can’t vouch for them nor can I rely on them for anything since I don’t know them BUT the very fact that they are on my list of direct connections constitutes some sort of an endorsement. What a mess! What is now the real value of this thing to the end users other than to sales professionals who may use it as a cheap way to compile prospect lists or to marketing professionals who can run highly targeted ads? What motivates someone who has no prospecting interest to waste much valuable time there? I can't find a good answer!
I am not trying to bash LinkedIn here – the concept was great and the implementation near perfect but we the people seem to have ruined a good thing as we often do.

Tech Evangelists – exclusive territories, great potential

2011-08-10T10:37:00.000-07:00

If you "live" online and have solid knowledge of databases, especially SQL Server, this may be the perfect gig for you. As a technology evangelist you will promote the use of our database tools through blogging, reviews, demonstrations, presentations etc. You will be compensated for your effort based on your performance. Here are the guidelines:

if you are selected to be the exclusive tech evangelist for a given territory we will show you the current status of affairs in that territory and establish a baseline based on historical data;
you will receive $0.1 for every unique visitor to our site, originating from your territory, over the established baseline.
you will receive $0.5 for every unique product download (your territory only) over the established baseline.
you will receive 15% commission on all sales on your territory regardless of how the customer found us.

You can learn more about the products you will be promoting from our site at http://www.xsqlsoftware.com/ - if what you read there sounds all greek to you then you know this is not for you. If you think you might be the right person for the job please get in touch with us via email (sales@xsqlsoftware.com) and briefly tell us who you are and why you think you can excel on this.

$250 for the most creative use of xSQL Data Compare

2011-08-08T11:12:00.000-07:00

Have you used xSQL Data Compare to solve a challenging problem? Send us a short essay describing your scenario and how you made use of xSQL Data Compare and you may win $250.

How will this work?

All entries submitted from now until November 30, 2011 will be evaluated by an internal commission for:
- Creativity
- Challenge level
- Presentation
The commission will select 3 winners and the winning entries will be announced no later than December 16, 2011. The prizes are:
- First place winner -> $250
- Second place -> $100
- Third place -> $50

Please note that by submitting your entries you assign xSQL Software the right to anonymize and publish your essay online.

Please submit your entries via email to info@xsqlsoftware.com

Major release: SQL Server Data Compare 4

2011-07-06T06:00:00.000-07:00

Coming 7 years after the release of the very first version of xSQL Data Compare, the new xSQL Data Compare for SQL Server version 4 reflects a wealth of accumulated experience, countless comments and suggestions we have received from you over those years, and the latest trends in software architecting and design.

Quick links:

Important notes:

Have an active upgrade subscription? You do not need to contact us. In the next 3-5 days you will receive your new license.
Have a licensed version installed already? Please note that the new version installs on top of the existing version but the license you have will not work with the new version.
Single edition: when you visit our site you will notice that there are no more standard and professional editions available. There is only one edition that includes everything.

Product reviewers: if you would like to do a professional review of the new Data Compare 4 we will be glad to send you a license and provide answers to any questions you may have. Just email us at info@xsqlsoftware.com

Stay connected: - follow us on Twitter http://twitter.com/xsqlsoftware, and like us on Facebook http://www.facebook.com/xsqlsoftware. Also, next time you stop by our site don't forget to click on those "like" buttons on our product pages. As always, your comments, suggestions, and your critique are greatly appreciated!

Why is SQL Server doing a clustered index scan?

2011-03-17T10:59:00.000-07:00

This is a simple real life scenario I ran into recently. Client is experiencing performance issues with SQL Server. A quick investigation reveals that a certain table, let's call it MyTable which contains over 50 million rows seems to be the source of contention. In particular there is a frequently run, innocent looking query that is causing the trouble. The query looks something like this

SELECT c1, c2 FROM MyTable WHERE MyTable_PK BETWEEN 5 and 9

The execution plan shows that the SQL Server query optimizer has decided to do a clustered index scan on the primary key (terribly expensive on a large table like this). Why can't the query optimizer "see" that an index seek would be significantly more efficient than the scan? Upon closer examination I realized that an implicit conversion of the PK is happening. Now I can see the light – I check the table structure and realize that the Primary Key column has been defined as a varchar and that is being compared with integer values. That is what's "tricking" the query optimizer into picking the wrong plan. A little tweak:

SELECT * FROM MyTable WHERE MyTable_PK IN (' 5', '6', '7',' 8', '9')

Success - now instead of scanning 50 million rows the query optimizer has picked the right plan, that is, it is doing a quick index seek and getting the rows we need in a fraction of the time. All the contention disappears – everybody is happy!

Note: the real query was much more complex and the tweak is not necessarily as simple, but the point here is to highlight the fact that if the type of the index column does not match the type of the values with which it is being compared then the index is rather useless.

Script Executor for MySQL free today

2011-02-15T06:25:00.000-08:00

Today and tomorrow only (February 15-16, 2011) you can get a free license for Script Executor for MySQL - simply plug in the MYSQLEX11 discount code on the shopping cart page - the price will go down to $0 and you will not be asked to provide any payment information. One license per customer only please.

Script Executor is one of the best tools for deploying t-sql scripts to your database server "farm". Whether you are running MySQL, DB2, SQL Server or a combination of those platforms, Script Executor allows you to easily create deployment packages involving hundreds of scripts that have to be executed in a certain order targeting as many servers as you need on any of those platforms, simultaneously.

Download a free, fully functional trial version and see for yourself what you have been missing until now: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=40&ProdID=ScriptExecutorLic

Don’t rename SPs, UDFs and Views – drop and create instead

2011-01-18T13:38:00.000-08:00

When developers at client X need to make changes to an SP (let’s call that SP1) they usually go through those simple steps:

Script SP1 out as CREATE
Right click on SP1 on the left panel and rename it to SP1_backup
Work with the script and when done with the changes execute it to create the new SP1

If you try this in 2005 you would get an error telling you that the SP1 already exists in the database even though it was just renamed to SP1_backup! There is a perfectly good explanation for this on a Microsoft kb article – basically when you rename an object like a stored procedure, user defined function or view the name of the object in the catalog is changed but the name of the object in the definition of the object is not changed! While this is annoying, at least it stops the user from making a complete mess.

With SQL Server 2008 Microsoft decided to eliminate this little annoyance, so now step 3 above will work just fine, the new SP1 is created successfully and you will be happy until you decide to look into SYS.sql_modules and realize that you now have two SPs with identical names in the definition. Fine, that’s not the end of the World you say. But just for a little fun try executing the sp_refreshsqlmodule 'dbo.SP1_Backup' - this should be completely harmless, simply updates the outdated metadata right? Well, try looking into SYS.sql_modules again after you execute that – surprise, all the work you did on SP1 just disappeared! Now, the definition of the SP1 is exactly the same as that of SP1_backup. Definitely not what you wanted or expected!

The renaming issue has been there forever – they (Microsoft) must have a pretty good reason for not fixing it, so I am assuming it will never be fixed. In the meantime the advise to SSMS user is: when it comes to SPs, UDFs and Views just pretend that the rename functionality does not exist – if you want to rename such an object script it out, change the name in the CREATE statement and execute the script; then just drop the original object.

Outlook takes forever to switch from one folder to another after update Kb2412171

2010-12-17T07:57:00.000-08:00

After installing Microsoft Outlook update Kb2412171 it takes forever to switch from one folder to another – uninstall the update and you will be fine. Only do this if you are experiencing the problem – in this case it was Windows 7/ 64 bit running Outlook 2007 with SP2. To uninstall:

Go to Control Panel \ System and Security \ Windows Update \ View installed update
Find the above mentioned update and uninstall

Note if you have your system configured to install the updates automatically this will install again so you will need to change your configuration.

Free Executor license with xSQL Comparison Bundle purchase

2010-12-15T09:08:00.000-08:00

From now until December 31, 2010 you can choose to take advantage of one of the following special offers:

A complimentary Script Executor Professional license with the purchase of a xSQL Comparison Bundle Professional license;
A complimentary 12 months upgrade subscription with the purchase of any of our products.

As always your comments and suggestions are greatly appreciated. Also, we would like to encourage you to follow us on Twitter http://twitter.com/xsqlsoftware, like us on Facebook http://www.facebook.com/xsqlsoftware, and don't forget to click on that "like" button on our products' pages.

Happy Holidays! May the season be filled with joy and may you have a healthy, happy and prosperous New Year!

xSQL Data Compare new build

2010-11-29T06:52:00.000-08:00

New build of xSQL Data Compare that fixes an issue with the sync script serialization is available for download. Under certain conditions the serialization of the synchronization script would fail – while that failure is mostly transparent to the end user as the process will continue normally, it would be exposed in cases when the synchronization script is very large.

xSQL Data Compare provides for comparing and synchronizing data in two SQL Server databases. The current version supports SQL Server 2008/2005/2000 - all editions. xSQL Data Compare is free for SQL Server Express with no restrictions. For other editions of SQL Server it is only free if the number of objects does not exceed certain limits listed here: http://www.xsqlsoftware.com/LiteEdition.aspx. You can download your free copy from here: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=10&ProdID=xSQLData

Script Executor for DB2 free today

2010-11-22T07:05:00.000-08:00

Today only you can get a free license for Script Executor for DB2 - use discount code TWITDB2EX when placing the order. Script Executor is one of the best tools for deploying t-sql scripts to your database server "farm". Whether you are running DB2, SQL Server, MySQL or a combination of those platforms, Script Executor allows you to easily create deployment packages involving hundreds of scripts that have to be executed in a certain order targeting as many servers as you need on any of those platforms, simultaneously.

Download a free, fully functional trial version and see for yourself what you have been missing until now: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=40&ProdID=ScriptExecutorLic

Profiler for SQL Express

2010-11-12T06:58:00.000-08:00

The lack of profiling tools for SQL Server Express has been one of the issues small developers have had to grapple with. If you are one of those developers check out xSQL Profiler – it provides all the functionality of the SQL Profiler and more. It supports all editions of SQL Server including MSDE and SQL Express. What’s more the xSQL Profiler is free for two SQL Server instances – fully functional, no strings attached.

So, if you are looking for a profiler for SQL Express you can get it here for free.

xSQL Documenter V4 released

2010-11-10T06:15:00.000-08:00

One of the best database documenting tools in the market just got better - we released xSQL Documenter V4 with the following new and enhanced features:

support for Microsoft Integration Server 2005/2008, Teradata 13 and SQLite;
enhanced Extended Property Editor to support more platforms and support for comments on procedure parameters;
A new “columns” section for table valued functions shows what columns they return (SQL Server only);
Deterministic, schema bound and inline properties for functions were added (SQL Server only);
Ability to suppress object dependencies on schemas (SQL Server only);
Ability to specify max number of threads in GUI (the equivalent of the command line switch /threads);
Support for replacing tabs with spaces in DDL;
Scripting of users, logins, and roles for SQL Server;
Support for connecting to Oracle using ODBC;
More details in the foreign keys section for SQL Server;
Specify sample rows from tables separately from sample rows for views since views can be very "expensive";
Ability to order sample rows;

Database management systems that were already supported in the previous version are:

Microsoft SQL Server 2000/2005/2008
Microsoft Analysis Server 2005/2008
Microsoft Report Server 2005/2008
Oracle 9i and above
DB2 8.2 and above
MySQL 5.0 and above
Sybase ASE 12.0 and above
Sybase SQL Anywhere 10.0 and up
Informix IDS 10.0 and above
PostgreSQL 8.0 and above
Microsoft Access 97 and above
VistaDB 3.0 and above
ENEA Polyhedra 7.0 and above
Raima RDM Server 8.1

Download a free, non-expiring trial version from http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=15&ProdID=xSQLDocumenter and let us know what you think.

xSQL Builder new build available

2010-11-09T09:42:00.000-08:00

Just published a new build of xSQL Builder with improved handling of backup files when the user chooses the "Create destination database from a backup" option. The new build also comes with a new and improved install mechanism.

Targeting primarily software publishers, xSQL Builder is one of the best SQL Server database deployment tools. In just minutes you can create an executable package that embeds wither the schema of the master database only or a complete backup of the master database. When you run the executable on the client depending on the configuration it will either create and restore the database from scratch or compare the existing database with the embedded schema and update the target with the changes. Of course software publishers are not the only ones who would benefit from using xSQL Builder - anyone in need of deploying a SQL Server database will find xSQL Builder to be an indispensible tool.

You can download the new build from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=30&ProdID=xSQLBuilder

Best way to create an rss feed from SQL Server

2010-11-01T14:11:00.000-07:00

If you are looking for a quick and easy way to create and maintain dynamic rss feeds from SQL Server then RSS Reporter is your best bet. Completely free for one SQL Server instance RSS Reporter for SQL Server supports SQL Server 2008, 2005 and 2000. It allows you to write a t-sql query and automatically generates an rss feed with the results of the query. Every time a feed reader refreshes the feed the query you have defined will be executed against the chosen database and the feed will be updated with the new results.

Need to change the rss feed? No problem – simply login to the rss reporter admin interface, modify the query, save it and you are done – all users that are reading that feed will get the new results next time the feed is refreshed.

Best of all: RSS Reporter for SQL Server is completely free (no strings attached) for a single SQL Server instance and only $99 for 5 SQL Server instances. A DBA in charge of monitoring SQL jobs from multiple servers will greatly appreciate the pre-built SQL Server jobs feeds – all jobs from all servers in one aggregate rss feed; that is efficiency!

One to many join – pick only one corresponding row from the "many" table

2010-10-22T09:17:00.000-07:00

It is hard to "synthesize" this problem into one sentence hence the cryptic title, but here is the long description: You have table T1 that has a one to many relationship with table T2. You need to update a particular column, let's say C1, on table T1 with a value that can be extracted (with some manipulation) from one of the corresponding rows on table T2.

The first thing that comes to mind is a simple update like this:
    UPDATE T1 SET C1 = SomeFunction(t2.C1)
          FROM T1 INNER JOIN T2 ON t1.ID = t2.t1ID
          WHERE t2.c1 fulfills some criteria – we need this since not all the rows on T2 contain the values we need.

The problem with this is that it keeps needlessly updating T1 rows as many times as it finds corresponding rows on T2. For each row on T1 we need to pick only one of the corresponding rows from T2 that fulfills the criteria. One option would be to use a cursor, but we want to avoid using a cursor and handle this within one query. Here is one of the ways of accomplishing this:
   UPDATE T1 SET C1 = tempt2.C1
          FROM T1 INNER JOIN
                          (SELECT DISTINCT t1ID, SomeFunction(t2.C1) AS C1
                                FROM T2 WHERE t2.c1 fulfills some criteria
                          ) AS tempt2  ON t1.ID = tempt2.t1ID

Now, the sub-query will produce a temporary table that contains a single corresponding row for each row on T1 and it also contains the value with which we need to update column C1 on T1. This is based on the assumption that SomeFunction(t2.C1) generates the same value for all rows in T2 that are related to the same row on T1.

With small tweaks in the sub-query you can use this solution for all scenarios when you need to restrict the number of rows that are returned from a one to many JOIN.

Note: don’t forget to try our cool SQL Tools and remember that when you follow us on Twitter http://twitter.com/xsqlsoftware and “like” us on Facebook http://www.facebook.com/xsqlsoftware you will get a complimentary 12 months upgrade subscription when you purchase any of our products.

Standardizing Object Names - Schema Compare

2010-10-12T08:29:00.001-07:00

In a recent build of our schema comparison and synchronization tool xSQL Object we introduced a simple “feature” that is aimed at generating consistent statements and fix name mismatches that occur especially with objects migrated from SQL Server 2000. The idea is that regardless of what the name of the object looks like in the definition of the object in the source database when xSQL Object generates the change script it will use [owner].[object_name]. For example, you may have created a new stored procedure as CREATE PROCEDURE myStoredProcedure … , in the sync script generated by xSQL Object this statement will be CREATE PROCEDURE [owner].[myStoredProcedure].

The “problem” some of the users are running into is that after they execute the synchronization script they expect the databases to be identical however the object definitions are not going to be identical because of those discrepancies in the object name inside the definition of the object (that is only if the names in the source database are not defined as [owner].[objectname]).

While we like the idea of automatically "correcting" the object names (by the way SQL Server Management Studio does the same thing when you script an object) you (the user) may want the synchronization process to reflect exactly what the source database is – in other words you don’t want xSQL Object to standardize the names. In that case you can turn this feature off: find and open xSQL.SQLObjectCompare.UI.exe.config with Notepad and then add the following key under the <appsettings> section: <add key="PerformNameValidation" value="0">

Multiple result sets – SSMS versus xSQL's Script Executor

2010-10-05T09:36:00.000-07:00

Up until now we have been promoting Script Executor as a tool that provides for deploying multiple t-sql scripts to many target databases (SQL Server, DB2 and MySQL). What we have neglected to emphasize is a seemingly small feature that many of our users find extremely helpful. If you work with SQL Server it is very likely that you have often executed queries that return multiple result sets and you have experienced firsthand how inconvenient it is to browse through those result sets - see the illustration below and just imagine what that looks like when you have more than just two result sets (click in the image to see full size):

Now, look at the screen shot down below to see how Script Executor displays the results – notice the green arrows (click on the image for full size) that allow you to jump from one result set to the other within the same server. Notice also something even cooler, on the left hand side there is a panel showing the list of databases against which the script was executed – so you can easily see the result sets you got from each database. Furthermore, those databases don’t all have to be SQL Server databases – the same scripts can be executed against multiple SQL Server, DB2 and MySQL databases at the same time.

And last but not least how about this ability to manipulate the result set – right click on the result set and a handy context menu appears allowing you to remove certain columns and or certain rows as well as copying and exporting the result set – see below:

I am of course not advocating ditching SSMS in favor of Script Executor – SSMS is an indispensable tool, but I am simply trying to emphasize the advantages that Script Executor has in certain situations. The reason Script Executor exists is to provide for an easy and efficient deployment of scripts to multiple targets.

Download a copy of Script Executor now and see for yourself how useful this tool can be.

Follow us on Twitter and Like us on Facebook

2010-10-04T12:02:00.000-07:00

Follow us on Twitter http://twitter.com/xSQLSoftware and like us on Facebook http://www.facebook.com/xsqlsoftware. In return we will give you a 12 months complimentary upgrade subscription when you purchase our products.

Script Executor - new build available

2010-09-22T13:26:00.000-07:00

A new build of Script Executor that provides for overriding the target server and database for an executable package via command line is now available for download.

xSQL Database Compare tools V3.5 released

2010-05-27T10:38:00.000-07:00

We just released version 3.5 of our database comparison and synchronization engine xSQL SDK, the schema comparison and synchronization tool xSQL Object, and the data compare and synchronization tool xSQL Data Compare.

The new version includes:

Support for SQL Server 2008 R2.
The passwords for SQL Server connections are now encrypted and stored in the workspace file thus eliminating the need to enter the password each time the xSQL comparison tools are launched.
Fixing the discrepancies between the name in the object definition and the name in the system catalog. This enhancement addresses an issue caused when the sp_rename procedure is used to rename objects with sql definition such as procedures, views, functions and triggers. The procedure sp_rename in these cases changes only the name in the sys catalog, but it does not change the name in the object's definition. This particular problem manifests itself mostly on SQL Server 2000 databases.
A new schema options named "Include objects with invalid dependencies" replaces the previous option named "Include views with invalid dependencies"; in addition to views this option now affects stored procedures, functions and triggers.
Trial expiration issue on Windows Vista and Windows 7 with UAC on. The enhancement addresses an issue with early expiration of xSQL Object and xSQL Data Compare trial period on Windows Vista and Windows 7 when the UAC is turned on - it guarantees that both tools will be fully functional with no restriction for the duration of the trial period. After the trial, the products will revert to Lite edition.
Some enhancements and bug fixing in the command line utilities including a newly added error code that tracks script execution errors.
A fix to stored procedure multi-line comments.
An enhancement in the way xSQL Data Compare handles identity columns. Explicit updates of the identity columns are no longer supported and will trigger data warnings.

You can download the new version from: http://www.xsqlsoftware.com/Download.aspx

xSQL Database Version Control - new build available

2010-05-25T06:53:00.000-07:00

A new build of xSQL Version Control (xSQLVC) for SQL Server is now available for download. The new build simplifies the configuration steps while giving you more choices on how you wish to copy the master development database from the master server to your local machine - in addition of the backup and restore option you can now copy all or part of the database via SQL Scripts.

Can a simple Alter Trigger statement cause serious blocking?

2010-05-13T14:25:00.000-07:00

Most of the time it would not as it is a extremely quick operation but it certainly has the potential to. I ran across this the other day: an admin user was running a simple, “innocent”, Alter Trigger statement on a production SQL Server, he had done this many a times without any trouble so he had no reason to worry. Unfortunately, this time something went wrong – the Alter Trigger statement which usually executes instantaneously wasn’t completing and all of a sudden blocking alerts started firing left and right! He killed the Alter Trigger and all went back to normal, but he was puzzled: what had just happened?

After a quick investigation here is what I found: the Alter Trigger had blocked virtually everyone (it was a trigger on a critical, heavily used table) but it wasn’t the head blocker! Instead, a “long” query that appeared to be doing some sort of data aggregation had blocked the Alter Trigger statement. But wait how can that be, the query was written with the NOLOCK option - apparently the person who wrote it knew that this query could potentially wreck-havoc on the production system so he tried to make sure he wouldn’t block anyone. And yes, most of the time his query would not cause any trouble to “others” except… while the nolock directive ensures that the query takes no data locks the query still takes what’s called a schema stability lock (in other words it’s saying: I will read un-committed data but don’t change the schema on me while I am reading) which prevents the Alter Trigger from taking the schema modification lock it needs. So, while the Alter Trigger is patiently waiting in line to get the schema modification lock it needs the rest of the processes that desperately need access to that table start piling up!

t-sql - save yourself, comment everything out

2010-03-26T08:07:00.000-07:00

Do you have a "miscellaneous" script that contains numerous disparate t-sql statements that you usually run on an ad-hoc mode? If yes then do yourself (and others that depend on you) a favor: select the whole script and click on the "comment out" icon. That way, if you ever happen to accidentally "execute" without first selecting the statement(s) that you really wish to execute nothing bad will happen. Otherwise, in just seconds you may end up creating a huge mess.

What prompted this? It was the desperate face of a very good database programmer who had just "successfully" executed one of those miscellaneous scripts and completely wiped out a couple of production tables, deleted certain rows from other tables and updated a few more. He did not lose his job but those few seconds of “terror” have probably shortened his life expectancy by a few months. It would have never happened if his miscellaneous statements were all commented out.

Are there other lessons one can learn from this story? Yes, plenty of them – there is a lot that the DBA can do to prevent such things from ever happening (just do a search for sql server best practices and you will find a lot of great advice) but the goal of this was to remind you of a very simple measure that everyone who “touches” the database can easily take.

Here is the product plug-in: this was a great showcase for our xSQL Data Compare. Here is what happened next. They mounted the previous night’s backup on a verification instance and applied the logs up to the last log before the "unfortunate event". Next they used xSQL Data Compare to determine what was different and generated a selective synchronization script that basically reversed all the changes that the un-intentional execution of that "miscellaneous" script had caused. Thanks to a reliable backup scheme and to xSQL Data Compare of course, the damage was contained to a couple of hours of work with no data loss but it could have been a real disaster.

Please feel free to leave your comments here.

xSQL Database Version Control for SQL Server released

2010-03-15T10:24:00.000-07:00

We have just released xSQL Database Version Control for SQL Server (short xSQLVC) - the first true database version control utility for SQL Server that provides the convenience and flexibility of working on your own copy of the database coupled with the assurance that you will be in sync with the other developers on your team. So, how does xSQLVC work? Here is the high level description:

you put a given database under version control
xSQLVC helps you automatically grab a copy of that database and create your own version
you make changes freely to your local copy and when ready to check in your changes xSQLVC will automatically generate the change scripts and execute those on the master copy
all your team mates are immediately notified of the scripts you checked in and with a click of a button they can apply those changes on their own local copies
xSQLVC maintains a complete history of the changes and allows you to rollback or re-apply certain scripts.

That's all there is to it: the power of xSQLVC lies in its simplicity - read more and download your fully functional trial version now.

Automate Deployment of SQL Server Databases

2010-03-03T10:54:00.000-08:00

Deploying SQL Server databases to clients can be a pain!

You may be a software publisher and your specialized software has been distributed to hundreds of your clients. For some of those clients you may have had to make custom changes to the database and the application to better meet their specific needs. Every time you need to send out an upgrade of your application you have to go through a laborious process of upgrading those hundreds of clients one by one. To make matters worst some of your clients have moved to SQL Server 2008, some are using SQL Server 2005 and yet some others are still running SQL Server 2000 so you have to worry about version specific scripts as well. Furthermore, your clients don't necessarily have in house SQL Server expertise to follow your technical instructions on running certain T-SQL scripts in a certain sequence etc.

Or, you may be a member of a development team or a SQL Server DBA in a big organization and after QA your application is routinely deployed to multiple locations. Deploying the database changes to all those locations is part of the time-consuming routine for each "target" you schedule the upgrade during the low activity time, you take a backup of the target database and then you start applying the change scripts. Yes, it is painful.

How can I avoid or at least ease this pain?

xSQL Builder allows you to automate the deployment of SQL Server based solutions. You can use it to generate an executable packages that contains all the information and logic required to deploy the SQL Server database to the clientsï¿½ environments regardless of whether it is a first time deployment or an upgrade from a previous version. The self-contained SQL Server database deployment package can be incorporated into a setup and deployment solution or it can be shipped to clients as a separate application.

What does an xSQL Builder executable package include?

The self-contained, executable package generated by xSQL Builder contains the following components:

depending on your choice it may contain a snapshot of the schema of the master SQL Server database, or the T-SQL creation script, or the backup of the master database.
run-time components for performing a SQL Server database comparison and synchronization
custom T-SQL Scripts that you may want to run before the comparison of the two databases takes place as well as custom T-SQL scripts that you may want to run after the target database has been synchronized with the master database
logging component that maintains a detailed log of all the events and operations performed on the client's environment during the execution of the deployment package. The executable package created by xSQL Builder generates three logs on the client: the operational log, the synchronization log and the error log.
notification component that allows you to configure the package to notify you via email in case of completion or failure of the execution of the package on the client

What does the executable package generated by xSQL Builder do on the client environment?

The self-contained, executable package generated by xSQL Builder performs the following actions on the client when executed:

execute any pre-synchronization custom T-SQL scripts that you have defined
in case of a new installation it may create the database on the target machine using either the embedded backup of the master database or the creation script depending on what you chose when you generated the package.
in case of an upgrade it will perform a schema comparison of the target database with the snapshot of the master database that has been embedded in the package
generate the synchronization script and execute it against the target database
execute any post synchronization, custom T-SQL scripts you may have defined
prepare and send notifications which may include the logs for your review in case of failure

In case all goes as planned your database has been deployed almost transparently to the client otherwise you will receive the detailed log that will help you determine what failed and why.

Compare and Synchronize SQL Server Databases

2010-03-02T08:28:00.000-08:00

Why compare databases?

Whether you are a developer that uses SQL Server on the back end to support your application or a database administrator in charge of monitoring and maintaining those SQL Server databases you often have to deal with different versions of the same database. For example, you may have a development environment, a testing or QA environment and a production environment and at any given time your database is in different "states" in each of those environment. As you work on implementing changes and additions to your applications you routinely make changes on the development database like adding tables, adding columns, changing types, creating indexes, adding new and modifying existing views, stored procedures, user defined functions etc.

At different points in the development you may need to promote all the work you have done on to the QA environment so that the QA team can start "pocking holes" in the work you have done. As soon as you do that you go back to your development World and continue working on your application and making database changes as the need arises so, it won't be long before the version of the database you are working on does not match the version the QA team is testing and that does not match the version of the SQL Server database that is running on your production servers.

Any professional in this position will keep meticulous records on each version of the database so that at any given time he would be able to tell what is different from one version to the other. That's well and good but it does not take much to realize that, especially if the database is relatively big and changes relatively many, sorting through such records to figure out what changes when (let alone how to change it back if necessary) is like looking for the needle in the haystack. That's why the need to have a tool that compares the SQL Server databases and tells you what is different.

How do I compare two databases?

xSQL Object which you can download from this site allows you to compare the schemas of two SQL Server databases with a few clicks, regardless of the location of those databases (as long as you can access those databases). The results of the comparison are displayed on an easy to use grid that shows the objects on the top section and the differences for the selected object on the bottom section. In addition of the side by side display of the selecte objects' scripts it also provides two change scripts - one to make the object on the second database in the comparison the same as the corresponding object in the first database in the comparison and abother change script to go the other way.

What do I do after comparing the two databases?

After you have compared the SQL Server databases in question, knowing on which direction you wish to transfer changes (example: if you were comparing the development version of your SQL Server database with the QA version it is likely that you would want to transfer changes from the development version to the QA version) you simply click on the right button (link) and xSQL Object automatically generates the change script required to transfer those changes. The script generated is safe and version specific (this is great for cases when you have different versions of SQL Server running on different environments - for example: you have upgraded the development environment to SQL Server 2005 or SQL Server 2008 but your QA and production environments are still on SQL Server 2000). At this point you can simply review the script to ensure that you do indeed intend to make those changes on the target database and then execute the script directly from xSQL Object's interface.

How about the data - the content of two SQL Server databases?

So, what if you have some sort of a distributed SQL Server database or a replicated database or some other scenario where you need to compare the actual data between two SQL Server databases to make sure that the replication has worked correctly or to see what records were changes since the last backup if you are trying to perform some auditing or for some other reason? Well, we thought of that to: xSQL Data Compare allows you to compare the data, the content, of two SQL Server databases, see where the differences are and then transfer all or some of those changes from one database to the other. xSQL Data Compare comes together with xSQL Object and the corresponding command line utilities as part of the xSQL Bundle that you can download from this site.

How much am I going to have to pay for those tools?

Not much at all - in fact it is likely that you may not have to pay anything - xSQL Bundle which includes both xSQL Object and xSQL Data Compare is completely free for SQL Server Express and comes in a free Lite Edition for other editions of SQL Server.

Database version control done right

2010-02-26T15:30:00.000-08:00

A good database version control tool should meet two simple requirements:

Give the developer the flexibility he needs to efficiently make changes to the database unhindered by a rigid structure of rules and regulations
Ensure that the development team is synchronized – they are all developing against the same version of the database

The problem is that those two seemingly simple requirements contradict each other to a degree – without an inherently inefficient structure of rules it is very difficult to meet the second requirement. This is the challenge we have tackled with a new product, xSQL Database Version Control (xSQLVC), that we will be announcing in the next few days.

Language a big barrier to penetration

2010-02-10T09:21:00.000-08:00

To translate or not to translate that is the question!

For years we have been operating under the apparently false assumption that for most techies English is the common language and when it comes to software tools that target this particular segment of the population, language is not a real barrier. We looked at the number of visits to our site (http://www.xsqlsoftware.com/) in the last 12 months by country and compared those to the population of each country for some of the developed countries. To make the comparison easier we normalized the numbers using visits from United States as the base. Proportionally to the population of each country for every 1 US originated visit we received:

From English speaking countries

Ireland -> 1.78
New Zealand -> 1.55
Australia -> 1.31
Canada -> 1.26
United Kingdom -> 1.19

Non English speaking countries

Italy -> 0.38
Austria -> 0.26
Germany -> 0.23
Spain -> 0.23
France -> 0.21
Japan -> 0.06

Since our tools are mostly tools for SQL Server it would have been nice if we had some idea of the penetration of SQL Server in those countries but, even if we corrected for that the gap between English speaking countries and non English speaking ones is striking and clearly highlights the fact that language is indeed an immense barrier even for our target audience (software programmers and database administrators). Of course, from this it does not necessarily follow that we should all rush to translating our sites and products into 10 different languages – whether that kind of an investment would pay off or not depends on many factors but it is certainly something we need to consider.

iApple an unbelievable iBuzz TM machine

2010-01-27T12:25:00.000-08:00

I have never seen another company that is able to come close to apple in terms of iBuzz TM or “buzz ratio” TM (defined as amount of buzz generated / value of the product being launched) – I don’t know how they do it but they do. Their approach to product launching is nothing short of “magical and revolutionary” even when their products are not.

Take the iPad for example – there is not a soul that hasn’t heard of it today, it’s unveiling easily eclipsed some “not so important” stories like the State of the Union, the Davos Forum or the Feds meeting to name a few. So, what’s so “magical and revolutionary” about the iPad? Well, it’s thin the “master” said, you can customize the background to your liking and you can turn the thing sideways… really!
So, how can Jobs get away with telling us that we will be able to change the background on this “ground breaking revolutionary” computer? Would he be able to generate this kind of buzz the next time around when he takes the iPad and makes it iPadXL? The reason says no, but, he has proven time and again that he is able to defy reason, he has the magic and can make us drool in anticipation every time!

xSQL Documenter 3 released

2010-01-25T13:07:00.000-08:00

We have just released xSQL Documenter 3 which includes the following new features and enhancements:

Support for Microsoft Report Server 2005/2008;
Support for Raima RDM Server;
Support for SVG diagrams. Now the diagrams can be rendered in all browsers, such as Firefox, Opera, Safari, and Internet Explorer;
Option to use short names in the documentation instead of the three part names;
Each table and view column is now added to the CHM index;
SQL Server only: support for an extended property filter. If you use a filter, only extended properties named in the filter will be fetched.
Microsoft Analysis Server: support for excluding XMLA and MDX code for objects.

There is no comparison in features or value between xSQL Documenter and other database documentation tools - xSQL Documenter comes way ahead on every category. Generates both compiled CHM and HTML documentation and supports the following platforms:

Microsoft SQL Server 2000/2005/2008
Microsoft Analysis Server 2005/2008
Microsoft Report Server 2005/2008
Oracle 9i and above
DB2 8.2 and above
MySQL 5.0 and above
Informix IDS 10.0 and above
Sybase ASE
Sybase SQL Anywhere 10.0
PostgreSQL 8.0 and above
Microsoft Access 97 and above
VistaDB 3.0 and above
ENEA Polyhedra 7.0 and above
Raima RDM Server 8.1

You can download the free trial version from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=15&ProdID=xSQLDocumenter The trial version does not expire but simply puts the "trial" notices throughout the documentation. Give it a try - it does not require installation - you will have the most thorough documentation ready in minutes.

Publish database changes to hundreds of clients - software publisher

2010-01-22T06:59:00.000-08:00

This is a "how to" written with software publishers in mind but it will benefit other software development groups that may not be classified as software publishers.

Here is the scenario: you have tenths, hundreds or maybe more clients using the software you wrote that utilizes SQL Server on the backend. The first release was painless but all the subsequent releases have given you grief - you send out database upgrade scripts with clear instructions but the users just don't seem to be capable of following those instructions and then they call you..., so you are looking for a way to make this process pain free. Well, you are in luck as nowadays there is a tool for everything. Following, I will provide some guidance for each of the scenarios you may be facing.

If you cannot access the clients' servers from your location (most likely scenario)

if you have a small number of database versions on the field and each version's schema is "un-touched" - that is you (or the client) did not make any changes to it then the most efficient way to handle this would be to use xSQL Object to compare each previous version to the new "master" version and generate a sync script for each version upgrade and then use Script Executor to package the sync script and any additional scripts you may want to run on the clients' servers into a executable which can then be made part of your installation package;
if you have many versions out in the field and what's more critical you may have made (or allowed the client to) customizations for different clients then the approach described above would not work very well, but xSQL Builder was designed exactly with this scenario in mind. In just few easy steps you can generate an executable package that embeds the schema of the master database you wish to publish. When the executable is launched on the client's site it will compare the currently installed version with the embedded master schema and synchronize the target to the master. While doing that it will generate a detailed synchronization log and automatically email it back to you so that you know what happened when the client ran the upgrade.

If you happened to have access to all your clients' databases then you can easily manage this from your location. Use xSQL Object to generate synchronization scripts for upgrading from version x to the current version, then use Script Executor to create a deployment package - basically add all your target servers (databases) into database groups, add your sync scripts into script containers and then map script containers to databases and execute ALL with a click of a button.

If you happen to face a scenario not covered by the above options please do let us know.

Applies to:

software publishing
publishing database schema changes
synchronizing clients' databases (the schemas) to a master database
comparing and synchronizing database schemas
executing multiple scripts against multiple databases

Execute SQL Scripts on MySQL

2010-01-13T14:29:00.000-08:00

Executing Sql Scripts against a MySQL database has never been easier, whether you need to execute a single script or many of them in a certain order, whether you need to execute the scripts against a single database or multiple servers/databases at once the best bet is Script Executor. The interface is simple and intuitive allowing you to create deployment projects involving hundreds of scripts and hundreds of target databases. Once you have created the project, with a click of a button you can execute them all based on the order you have determined and precedence constraints you may have set.

You can use Script Executor to execute scripts against MySQL 5.0 and 5.1 databases.

You can read more and download your trial copy of Script Executor from: http://www.xsqlsoftware.com/Product/Sql_Server_Script_Executor_Licensed.aspx

Where is Google - a support call

2010-01-13T10:18:00.000-08:00

(this is a transcript of a January 13, 2010 call to xSQL Software’s support group)

Caller: where is SQL Server 2005 SP3 – I can’t find it?
Support: well, we are not Microsoft but I will help you. Please do a Google search for “SQL Server 2005 SP3”, the first item on the list is what you need.
Caller: but I am using Bing, is that a problem?
Support: no, it is not a problem but the results are not necessarily the same and I am not sure if what you are looking for will be the first item on the list, so, why don’t you do this one search on Google?
Caller: but Bing is my default search and I don’t know how to change it!
Support: how about going to google.com first and then doing the search, you will not need to change anything?
Caller: oh, that is a good idea, a no brainer in fact...

xSQL Profiler version 1.6 now available for download

2010-01-12T13:50:00.000-08:00

We have just released a new version of xSQL Profiler that adds the following features:

ability to load the trace data directly to the central repository from a UNC path without having to go through the monitored servers temp db;
the option to set different polling intervals for each trace on each target server;
the option to configure server level traces reducing the number of traces that will need to be started on the monitored servers

xSQL Profiler allows you to configure and schedule SQL traces to run simultaneously on multiple servers and automatically collects all trace data into a central repository.

You can download the new version from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=12&ProdID=xSQLProfiler

Trouble connecting to remote SQL Server instance

2010-01-11T09:22:00.000-08:00

Cannot connect to server1\instance1… SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified…

From location1, machine1 connects to a remote SQL Server instance, no trouble. Take machine1 move to location2, very similar environment – machine1 fails to connect, the error message is the one above. You verify your SQL Server configuration and everything looks ok, and besides if something was not right you wouldn’t be able to connect from location1 either. So what is the problem?

It turns out location2 firewall is blocking UDP traffic and that is why you are not being able to connect. When the client sends the request to the server asking to connect to server1\instance1 it is the SQL Browser service (on that server) that handles the request and responds with more detailed information to the client, information that the client needs in order to establish the connection to the server. That communication between the SSMS and the SQL Browser service happens over UDP (port 1434). So, when the UDP traffic is filtered out the client does not hear back from the SQL Browser and consequently is not able to establish the connection.

So what can you do if you can’t change the firewall configuration? If you know where your SQL Server is listening then all you need to do is specify the protocol and the port number as shown on the pictures below and you will be able to connect – in this case the SQL Browser service is bypassed since the client already has all the info it needs to connect to that SQL Server instance.

What if you don’t know what port SQL Server is listening on? If you can connect from some other location to that server you can try Microsoft’s Port Query utility (this is a great tool for diagnosing your connection problems when you can not connect also) http://www.microsoft.com/downloads/details.aspx?familyid=89811747-C74B-4638-A2D5-AC828BDC6983&displaylang=en they also have a UI add on for this that you can download from here: http://www.microsoft.com/downloads/details.aspx?familyid=8355E537-1EA6-4569-AABB-F248F4BD91D0&displaylang=en – it will show you the response from the SQL Browser service indicating which port your SQL Server instance is listening.

Backup database structure only

2009-12-02T07:19:00.000-08:00

Sometimes you need to back up the database structure (schema) only – it can be because you simply would like to maintain an audit trail of database schema changes or because you need to compare the schemas of two databases that you can’t access directly from one location.

Whatever your reasons maybe xSQL Object provides the ideal solution to accomplish this – with a click you can take a snapshot of the SQL Server database schema. The schema snapshot contains all the schema information but no data and consequently is a very small size file. You can then use xSQL Object to compare this schema snapshot to other snapshots or to live databases and see exactly what changed from one schema version to another.

T-SQL: set end date to first or fifteenth of the month

2009-11-24T18:14:00.000-08:00

Here is a simple assignment: if the subscription date is from first to 15th of the month then the expiration date should be set to the 15th of the same month a number of years later; if the subscription date is from 16th to the end of the month then the expiration date should be set to the first of the following month a given number of years later.

I am sure there are many ways to do this but here is one:

DECLARE @sDate DATETIME -- subscription date
DECLARE @sYears tinyint -- number of years the subscription will last
SET @sDate = getdate()
SET @sYears = 1

IF datepart(dd, @sDate) <= 15
SELECT DATEADD(year, @sYears, @sDate) - datepart(dd, @sDate) + 15
ELSE
SELECT DATEADD(year, @sYears, DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0))

Script Executor - package t-sql scripts into executables

2009-11-24T09:14:00.000-08:00

In addition of allowing you to deploy multiple scripts to multiple databases directly from the interface Script Executor also allows you to package all your scripts into an executable.

An executable package in the context of the Script Executor is a small, dynamically generated executable file that contains embedded in it scripts, list of databases against which the scripts will be executed, and a small piece of code to run these scripts. You can run it as you would run any other Windows program.

Executable packages are suited for deploying Sql script to the end-users/clients. A user can run it without having Script Executor, or SSMS installed.

SQL Server execute scripts - multiple scripts, multiple databases

2009-11-23T07:48:00.000-08:00

You launch SQL Server Management Studio – open script #1 and execute it against database 1 on SQL Server 1, then execute it against database 1 on SQL Server 2… then open t-sql script #2 and execute it against database 1 on SQL Server 1 etc… what a painful process, you dread the day you have to go through this (sometimes that is everyday). What a waste of resources too – a $100K database administrator spending hours executing scripts on tenths of server!

No more! You can now use Script Executor to take care of this job for you. One time only, you will go through a simple, and may I suggest fun, process of organizing your scripts into virtual containers and your databases into groups; then, map those script containers to the database groups; set an order of precedence if you need to and voila you have a package that you can now execute with a single click; or, you can even schedule it to run before you even make it to the office.

Script Executor will execute each script in the order you have defined against each database based on the mappings you have defined and once completed it will generate a comprehensive report allowing you to see how the script execution went on each target and also easily “flip through” the result sets that those scripts may generate.

Deploying (executing) multiple scripts against multiple SQL Server databases has never been easier.

You can download a free trial version of Script Executor from: http://xsqlsoftware.com/DownloadDetails.aspx?PackageID=40&ProdID=ScriptExecutorLic

A free community edition of Script Executor with limited functionality is also available: http://www.xsqlsoftware.com/Download.aspx

Applies to:

SQL Server execute t-sql scripts
SQL Server execute multiple scripts
SQL Server deploy scripts to multiple databases
SQL Server package t-sql scripts
SQL Server run multiple scripts

Kindle, Zune, iPod - a free gift with every purchase

2009-11-17T08:10:00.000-08:00

This month only we are giving away a free cool gift with every license purchase - kindle wireless, Zune, iPod Touch etc. Check out the details here: http://xsqlsoftware.com/nov_gift_choices.aspx

Is Bing beating Yahoo already?

2009-10-16T14:12:00.000-07:00

Looking at our site stats today I was surprised to see that in the last 30 days we have received about 20% more “organic” visitors from Bing than from Yahoo! Is this just an aberration or is Bing already beating Yahoo? What you seeing on your site? Please answer the poll on the right panel here and leave your comments below.

The poll has been closed - the final result was 60% for Bing and 40% for Yahoo however, the number of responses we got was statistically insignificant so I would put no stock on these results.

$100 for your SQL puzzle

2009-10-02T10:43:00.000-07:00

We are planning to bring back the “puzzle of the month” program and this time the puzzles will come from you. We are asking you to send us the hardest SQL puzzles you have come across or that you can come up with.

If your puzzle is chosen to be published on our site we will:

Send you a $100
Link from the puzzle page to your blog or your site

The harder your puzzle the higher the chances that it will be picked, however,

The puzzles should fall into one or more of the following categories: SQL Server, T-SQL, Database Management Systems

Submit your puzzle here: http://www.xsqlsoftware.com/Submit_SQL_Puzzle.aspx

To pay or not to pay this is the question

2009-09-17T14:18:00.000-07:00

Product reviews are absolutely critical to the success or failure of a product and that is especially true in the software world. Trying a software product takes effort and precious time so we all naturally gravitate towards relying on product reviews that others have done and only take the time to try one or two products that may have been heavily and positively reviewed.

Knowing how important those reviews are we have sent tenths of product review requests in the last 6 years and in the process we have learned the unsettling truth about product reviews: they don’t come by easily. In the case of the big guys (technical magazines and large user group sites) you don’t have to pay for the product review per-say but your product will forever be in the queue unless you are shelling out a few thousand advertising dollars in those medias, only then does your product magically shoot up to the top of the product review queue; whereas in the case of the smaller guys (bloggers and user group contributors) you often have to pay for the review anywhere from a set price of as low as $200 for one product review to a per word price that can be as high as $0.6 per word (this is the highest product review offer we have received).

In the case of the big guys since you are not paying directly for the product review there is no moral dilemma – deep down you know that the review can’t possibly be fair and un-biased. If you are paying $30K / year for advertisements on that media the last thing they would want to do is alienate you with a bad product review, but, it’s easy to shun those thoughts, after all this is your product and you truly believe that it is a great product.

On the other hand, when it comes to paying for the review directly it feels like cheating and unfair to the reader who believes that he is reading an impartial review. For it to feel right one must disclose in big bold letters that “this review has been paid for by the product vendor…”, but such disclosure would likely deem the review worthless. Ideally you would want someone to review your product simply because they like the product and they want to share it with other people but in all fairness reviewing a software product and then writing up your observations and conclusions may take hours of work of which we humans have a very limited supply of. Herein lies our dilemma – to pay or not to pay?

Until now we have chosen not to pay, instead we have only offered the reviewers a free license for the product they decide to review which we think is fair and does not present a conflict of interest. We would certainly be more comfortable continuing with this approach but the results are not very promising hence our pondering.

Let us know what you think – would you pay?

And of course, we would be delighted if you would consider reviewing any of our fine products in exchange for a free license.

Synchronize two SQL Server databases

2009-09-02T13:50:00.000-07:00

You are furiously pounding code on your computer and in the meantime making small changes to the local (development) version of the database as the need arises – add a column, change the type and width of another, add a view, make a change to a stored procedure and so on. At some point in the early morning hours as the World wakes up you are ready to publish your work on the production server, somewhere… – just a few clicks and the early risers will see your masterpiece (application) in action. Well, not so fast unfortunately, all those changes you made to the development database must be made to the production database otherwise your application is not going to work, is it? Now, that is some painful and tedious work to say the least. What if you forget something? And here is a twist to make this a real nightmarish scenario – remember last week when the client needed those urgent changes and you were forced to do the “unthinkable” – make changes right on the production database! Worst yet, you realize that you never got around to bringing those changes down to your local, development database – no words can describe the pain you must feel at that moment!

Is this time for panic? No it is not, it is time for xSQL Object – in just a few clicks it will show you exactly where the differences are on both sides and better yet auto-generate the scripts that you need to quickly and safely make the changes.

Download it now and eliminate “the pain” – free lite edition with no strings attached. Supports SQL Server 2000, SQL Server 2005 and SQL Server 2008.

Best way to execute t-sql scripts

2009-08-28T07:41:00.000-07:00

Whether you need to deploy / execute multiple t-sql scripts to hundreds of your clients or to hundreds of your enterprise servers Script Executor is your best bet. For starters, it supports SQL Server 2000/2005/2008, MySql 5.0 and higher, DB2 9.0 and SQL Server Compact 3.5. It allows you to quickly organize your scripts into virtual script containers and organize your servers into virtual server groups. You can then map the script containers to the server groups, define execution precedence if necessary, and finally just deploy them OR package the scripts into an executable. Imagine, instead of sending your clients t-sql scripts and telling them to download and install Sql Server Management Studio so that they can run your scripts you can just send them an executable file that anyone can run - no that's easy!

New build of xSQL Bundle available

2009-07-31T06:57:00.000-07:00

A new build of xSQL Bundle that includes xSQL Object for database schema comparison and synchronization and xSQL Data Compare for comparing and synchronizing data is available for download from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=10&ProdID=xSQLBundle

The new build adds the ability to include and exclude object level permissions from the synchronization. Permissions are now shown under each object they're granted to or denied on. Statement permissions are listed under the database node in the comparison grid.

xSQL Builder now supports SQL Server 2008

2009-05-25T13:54:00.000-07:00

The newly released xSQL Builder 3.0 now supports SQL Server 2008. xSQL Builder provides for effortless distribution of database changes to remote clients. In just minutes you can create an executable package that contains the schema of the "master" database you wish to propagate to your clients - the client executes the executable and voila, the schema of the client database is upgraded.

No more sending T-Sql scripts to your clients - send them an executable instead!

Download your free trial of xSQL Builder now: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=30&ProdID=xSQLBuilder

Right tool for the job - what our customers say

2009-05-06T13:52:00.000-07:00

It is always nice to see a nice blog post about your products but it is awesome to see a paying customer praising the product without being asked to do that and without asking for any favors in return! I just ran accross one such blog post that was published today:
http://catholicinformation.aquinasandmore.com/2009/05/06/using-the-right-tools-for-the-job/ - it is in such cases that we can proudly say: you don't have to listen to us - see what others are saying...

Execute Sql Scripts on DB2

2009-03-26T11:39:00.000-07:00

Whether you need to execute one Sql Script on a DB2 database or many Sql Scripts on one or many DB2 databases there is one solution unlike any other – Script Executor. Its simple and intuitive interface allows you to create deployment projects involving hundreds of scripts and hundreds of target databases and then with a click of a button execute them all based on the order you have determined and precedence constraints you may have set.

You can use Script Executor to execute scripts against DB2 9.0 databases.

You can read more and download your trial copy of Script Executor from: http://www.xsqlsoftware.com/Product/Sql_Server_Script_Executor_Licensed.aspx

Script Executor now supports DB2 and SQL Server Compact

2009-03-25T08:35:00.000-07:00

we just released a new version of Script Executor which now in addition of SQL Server 2008/2005/2000 and MySQL 5.0/5.1 also supports DB2 9.0 and SQL Server Compact Edition 3.5.

Script Executor provides for deploying Sql Scripts to multiple target databases with a click of a button and allows you to:

organize target databases into logical groups
organize your Sql Scripts into containers and order scripts within each container
map script containers to database groups
order mappings and set precedence constraints
get a comprehensive execution report
view execution results in one integrated easy to navigate interface

Download your trial copy today from: http://www.xsqlsoftware.com/DownloadDetails.aspx?PackageID=40&ProdID=ScriptExecutorLic

Independent consultants can quickly translate hours saved to dollars - employees can't

2009-03-09T14:35:00.000-07:00

One would think (or at least I thought) that when it comes to selling auxiliary software tools it would be easier to sell them to an organization where the buyer is using the organization’s funds to purchase the product than to an independent consultant who has to shell out his own money. However having spent the last 5 years in the SQL tools market I have concluded that the opposite is true – generally speaking the independent consultants do not need much convincing, whereas the full time dba/developer in an organization seems to have a much harder time making a $300 decision!

So, that got me thinking: why would someone readily pay a few hundred dollars from his own pocket for one of our tools while a full time employee seems to struggle to make up his mind and appears to be constantly looking to come up with unusual scenarios that the off the shelf tool may not handle? I have come up with three main reasons (not necessarily an exhaustive list of reasons):

the value preposition for the independent consultant is very clear – every hour of their time has a price tag – if they find a tool that saves them 1 hour per day they can quickly translate that saved hour into dollars. On the other hand a full time employee can not easily convert “hours saved” to money, nor can his boss or the boss’ boss.
The bureaucracy that plagues many organizations significantly dilutes the short term value of such tools - if you as an employee are forced to go through hoops to get approval for purchasing a $300 product that will potentially save you a couple of hours per week then you may decide it is not worth the pain. The independent consultant on the other hand does not need to ask for permission from anyone so there are no “costs” associated with the approval process.
A programmers ego: “I can do this stuff myself, why should I pay someone else to do it for me” When it comes to productivity an independent consultant can not afford to have an ego whereas a full time employee can and furthermore, by building a custom solution instead of buying an off the shelf tool the full time programmer will likely consolidate his position with the company. A lot of you may be surprised but I have found that it is often easier to convince the higher ups to let you put 100 hours on a project than convince them to let you spend $300 on a tool.

Please note that this is not a “research based” conclusion – it is simply an opinion supported by personal experiences. Please feel free to add your perspective on this and don't forget to check out our tools - you don't even have to pay anything in some cases.

A real life Sql scripts deployment story

2009-03-06T08:28:00.000-08:00

We got a surprise call today - a client that just purchased a license for the newly released Script Executor called to thank us (normally we thank our customers for choosing our products not the other way around)! He told us that for more than 2 years one of his most annoying weekly "chores" was to execute about 120 Sql scripts on a handful of SQL servers. The task was complicated by the dependencies between those Sql scripts - a set of scripts had to execute on Server 1 successfully before another set could be executed on Server 2 and so on. And of course within each set the scripts had to execute on a certain order. So every week he would consume many hours of his life opening and executing those 120 Sql scripts one after the other.

For the last few months he was using the free community edition of our Script Executor and it had helped him a lot but it wasn't exactly what he wanted whereas the new Script Executor 3 gave him exactly what he needed - he spent about 15 minutes configuring his Sql script deployment package and saved the project. Now, all he needs to do is click Execute and a task that once took hours takes him one minute to launch and about 10 - 15 minutes to review the results.

His words: "I would have gladly paid the $179 from my pocket if the company would not have approved the purchase. Thank you for an awesome product!"

Well, thank you! For us there is nothing better than knowing that our work is providing real value to others.

Execution order and precedence

2009-03-04T09:20:00.000-08:00

In addition of allowing the user to organize Sql scripts in containers and ordering the scripts within each container the new Script Executor 3 allows the user to also order and set precedence constraints between “mappings”. In other words you may want scripts on the script container 1 to execute against the databases in the database group 1 first and only if those all succeed then execute the scripts in container 2 against database group 2 etc.

This cool feature allows the user to configure highly complex deployment scenarios involving dependencies between scripts and groups of scripts. The picture below shows where the Execution order and precedence constraint can be set for a given mapping.

The 7 countries that have “lost the number” of our site!

2009-02-27T11:10:00.000-08:00

-- sorry Mr. Biden, I just couldn’t resist!

Every time I look at the World map on google analytics, that cool map that shows where the visitors came from, I see a handful of white spots – that is countries from which we did not have any visitors in the last 30 days. I am always curious to know which ones those countries are but they are usually on the same general area so I don’t often drill down. Today it occurred to me that it would be interesting to go back in time and see if there were any countries from which no one ever visited our website – I set the range from January 1, 2008 to today and found that in those 14 months we have not had a single visitor from 7 countries out of 200 and some countries. And the “winners” are:

From Africa

Burkina Faso
Central African Republic
Madagascar
Mauritania
Western Sahara

From Asia:

Kyrgyzstan
Turkmenistan

Please do keep in mind that our website: http://www.xsqlsoftware.com is a niche site providing database tools to database administrators and software developers and therefore, this “finding” does not necessarily mean anything. I just found this interesting and decided to share with ya’ll!

Executing multiple Sql Scripts

2009-02-27T08:09:00.001-08:00

If you work with SQL Server and especially if your job includes DBA tasks it is very likely that you have run into situations when you had to take a bunch of scripts (maybe tenths of them and maybe hundreds) and execute them in a certain order one after the other against a database. It is a tedious task to say the least. Now imagine if you had to do the same thing for 10 servers or 100 servers then you are really in trouble - there comes a point when it will be more efficient for you to sit down and write an application that will help you automate the process then do this job even once.

Well, you don't have to go through that annoying process anymore - with Script Executor, in just minutes, you can configure a highly complex scenario involving hundreds of scripts and tenths of target servers after which your highly complex deployment will require just one click or even none if you use the command line utility.

Download Script Executor now and see for yourself why we are so excited about it!

Where do MySQL users congregate?

2009-02-25T16:06:00.000-08:00

We are kind of new to the MySQL world and are trying to figure out what are THE "places" to go if you want to reach MySQL users. Our tools are primarily SQL Server tools but we have just released a new tool, Script Executor that supports MySQL as well. The tool in a nutshell allows the user to execute Sql Scripts against multiple MySQL databases – it is a great tool for any database administrator or developer that works with MySQL. We are looking for two things:

users who would be willing to review the product
web destinations where we should advertise

So, if you are either someone interested in reviewing Script Executor or someone who knows where we should advertise please email us at our info address on our xsqlsoftware.com domain or simply put a comment here.

The Script Executor you have been waiting for

2009-02-24T05:37:00.001-08:00

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.xsqlsoftware.com/DownloadDetails.aspx?PackageID=40&ProdID=ScriptExecutorLic

Best way to compare data in two SQL Server databases

2009-02-04T08:40:00.000-08:00

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.xsqlsoftware.com/download.aspx

Documenting your databases – one tool does it all

2009-02-03T05:55:00.000-08:00

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.xsqlsoftware.com/Download.aspx

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

2009-01-29T07:53:00.000-08:00

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

2009-01-28T06:48:00.000-08:00

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

2009-01-19T08:23:00.000-08:00

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.xsqlsoftware.com/Download.aspx

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

2009-01-15T15:59:00.000-08:00

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

SQL Server Query Governor way off on its estimates!

2009-01-14T15:07:00.000-08:00

First of all if you are looking for information on how to set the query governor cost limit option you can go directly to the source: http://msdn.microsoft.com/en-us/library/ms190419(SQL.90).aspx or to any of the tenths of blogs that seem to have replicated this information word for word.

So, here is the scenario: as a diligent DBA you audit a SQL Server you just took over and you realize that the query governor cost limit is set to 0 which means in essence that as far as the query governor is concerned any queries can run on the server for as long as they need to run. That’s not good so you go ahead and set a high limit to start with, let’s say 300 seconds. Your thinking is that if a query that is estimated to run for more than 5 minutes ever comes across it should be stopped dead on its tracks before hundreds of calls from angry users reach your desk. To your surprise 5 minutes after you have set that limit you get a call from Jane Doe – she has seen this strange message on her screen she never saw before “you can’t execute this query because it is estimated to take 450 seconds”. Jane assures you that the report she is running usually runs in 5 to 10 seconds!

You go ahead and raise the limit to 500 seconds – Jane runs the report and sure enough the report takes less than 6 seconds! Something is not right – why is the query optimizer so grossly overestimating the time this query will take to run?

The answer is simple indeed – remember the query optimizer is estimating - estimates are made based on available information and if the available information is either missing or not accurate than the estimate won’t be accurate. The information that the query optimizer uses is in the statistics that SQL Server maintains and that is where you need to look. So, check it Auto Create Statistics is on first – if not no then you either need to turn it on or manually create the statistics the optimizer needs for estimating this query (this is the case when the necessary statistics information may be missing). Second check if Auto Update Statistics is on – if not you may need to update the stats to enable the query optimizer to produce a more accurate estimate.

Furthermore, you may find that all is in order that is no statistics are missing and the stats are up to date and yet the estimate is wrong! Try forcing a recompile of the query in question and see what happens. What may have happened is that SQL Server is using a cashed plan that was optimized for the “wrong” parameter values – that is values which are not good representatives of the dataset and based on that plan it is estimating that 10,000 rows will be returned from table a when in fact in 90% of the cases the number of rows returned from table a on that query is under 1000. By forcing a recompile of the query you are forcing SQL Server to re-evaluate and possibly pick a better plan this time based on new parameter values.

The objective of using the query governor cost option is to prevent “stupid” queries from degrading the system performance and not to prevent legitimate queries from running. So you may be forced to raise the limit but you should look at that as a temporary measure until you have dealt with the legitimate queries by taking the above mentioned actions as well as optimizing the query itself. Then, you should go back and start gradually lowering the query governor cost limit.

Why is SQL Server scanning the clustered index when I have an index on the column I am filtering on?

2009-01-12T11:55:00.000-08:00

I ran into a very simple SQL Server performance related question the other day. A puzzled DBA was staring at a simple query “SELECT * FROM table1 WHERE col_n = x”. Since this was a fairly common query that was executed very frequently he had appropriately created an index on col_n. Yet, for some reason SQL Server was simply ignoring the index on col_n and instead scanning the clustered index. So the puzzling question on this DBAs mind was “why is SQL Server scanning the table? Isn’t the SQL Server Query Optimizer smart enough to see that it will be more efficient to use the col_n index?

The answer lies in the fact that the SQL Server Query Optimizer is smarter than that (albeit not as smart as I wish it would be). You see, when deciding on a particular execution plan the Query Optimizer has to use an actual value for the parameter x to estimate the number of rows that will be returned on each step. It further assumes that the rows you are looking for are randomly distributed and that it will need to do a page read for every row being returned in addition of the reads it needs to do on the index pages. Depending on this number it makes a determination whether it will be more efficient to just scan the whole table and pull out the rows it needs or go to the col_n index first to get a list of addresses and then go and pull those rows.

So, now that we know why should we just leave it there since it appears that SQL Server is correctly picking the most efficient path? Not so fast! Remember all that evaluation is being done based on a certain value of parameter x. It could be that for most of the values of parameter x scanning the table is more efficient. In that case you can simply drop the index on col_n if it is not needed for any other queries and leave it at that. However, it could be that for 99% of the possible values of x it would be a lot more efficient to utilize the col_n index – it just so happened that unfortunately when generating the plan the value of x happened to fall on that 1% for which scanning is more efficient. In this case you have to options:

simply force SQL Server to dispose the bad plan that it has cashed and generate a new one; OR
use a HINT to force SQL Server to use the index. The danger with this however is that with time as the data changes the use of that index may not be optimal so I would recommend that you avoid using HINTS whenever possible and let SQL Server do its job.

One last note: it is often not necessary to return all the columns – in other words instead of SELECT * FROM table1 WHERE col_n = x you may only need to return a couple of columns like SELECT col_1, col_2 FROM table1 WHERE col_n = x in which case it could be worth it to include col_1 and col_2 in the col_n index. That way SQL Server would not need to go to the table at all but instead get all it needs from the index pages. In certain scenarios where you have a rarely updated but very frequently queried table the above approach of including other columns in the col_n index may make sense even if the query returns all the columns.

SQL Server search stored procedures

2009-01-12T07:48:00.000-08:00

Anyone who works with SQL Server has at one point or another faced the simple question: “how can I find all stored procedures that reference a given table?”, or “how can I find all the objects in the database the definition of which contains a certain word or phrase?”. While SQL Server makes those tasks relatively easy those are still time consuming and when done on an ad-hoc bases chances of missing or forgetting something are relatively high.

The good news is: you don’t have to struggle with this when there is a really cool and completely free tool called xSQL Object Search available. So how does xSQL Object Search work? Very simple:

You connect to the server you are interested on
Choose to search in a certain database or All Databases on that server
Define your search criteria
- What are you looking for
- How do you want to search (exact match, contains, starts with, ends with, sql server expression, regular expression)
- Choose where do you want to look - the object name, object definition, or both?
- Finally choose which object types you wish to search (all objects or just certain types of objects like stored procedures, views, tables, triggers etc.)
Click on Find Now (see the screen shot below

xSQL Object Search is a free tool and you can download it from: http://www.xsqlsoftware.com/download/
Applies to:
SQL Server search tables
SQL Server search views
SQL Server search stored procedures
SQL Server search triggers
SQL Server search functions

Free SQL data compare tool

2009-01-06T05:45:00.000-08:00

There are many scenarios in which someone working with SQL Server may need to find out what the data differences are between two databases or just two tables. Furthermore, it is often necessary to synchronize the data, that is, make the data in the target database the same as the data in the source database or vice versa. That’s where xSQL Data Compare comes in – it is a tool that was designed specifically for this purpose. It comes as part of the xSQL Bundle package and just like in the case of xSQL Object chances are you won’t have to ever pay a dime for it.

The xSQL Data Compare licensing works exactly the same way as the licensing of xSQL Object described in detail on yesterday’s post here.

Free SQL Compare tools for SQL Server

2009-01-05T10:02:00.000-08:00

If you are looking for a free schema compare tool that allows you to compare and synchronize the schemas of two SQL Server databases, like your development database with the production database, then xSQL Object is the tool for you. Not only is it one of the best tools in the market (don’t take our word for it – give it a try and you will see for yourself) but what’s unmatched in the industry is the way our licensing works. I will give you the details below but the end result is that most of our users enjoy using xSQL Object completely free. Here is how our licensing works:

everyone downloads the same xSQL Comparison Bundle package that includes xSQL Object and xSQL Data Compare;
for the first two weeks the applications function as if you had purchased a professional license;
after the first two weeks the licensing module first checks the edition of the databases you are comparing:

if both databases are SQL Server Express edition then you see no difference – works the same as before;
if one of the databases in the comparison is something other than SQL Server Express then the licensing module checks the number of objects in that database (number of tables, stored procedures, views, functions)

if the number of objects is within the limits of what we call the Lite Edition then again the application works and you don’t see any difference;
if the number of objects is greater than those limits then you are kindly notified that to compare and sync that database you need to acquire a license. Well, someone has to pay for all that development effort that goes into our products :)

You can download xSQL Bundle from: http://www.xsqlsoftware.com/download/ and while there don’t forget to check our other cool tools.

t-sql random string generator

2008-12-29T08:37:00.000-08:00

This is a simple stored procedure that you can use to generate a random string. You can use it as a random password generator and for other purposes. It is very simple so I won't bore you with unnecessary explanations but if you do happen to have any questions feel free to post them here.

CREATE PROCEDURE [dbo].[usp_GenerateRandomString]
  @sLength tinyint = 10,
  @randomString varchar(50) OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @counter tinyint
  DECLARE @nextChar char(1)
  SET @counter = 1
  SET @randomString = ''

  WHILE @counter <= @sLength
    BEGIN
      SELECT @nextChar = CHAR(ROUND(RAND() * 93 + 33, 0))
      IF ASCII(@nextChar) not in (34, 39, 40, 41, 44, 46, 96, 58, 59)
        BEGIN
          SELECT @randomString = @randomString + @nextChar
          SET @counter = @counter + 1
        END
    END
END

There are of course other ways to accomplish this – a very simple one would be to use newid() and grab whatever number of characters you need from it.

No dependency on SP1 of .NET framework anymore

2008-12-15T06:37:00.000-08:00

Since we received a few messages from users having trouble with xSQL Data Compare because of the SP1 dependency, instead of "forcing" the users to install SP1 for .NET Framework we decided to remove that dependency altogether. You can simply download the latest build of xSQL Bundle from our site and install it over the current version you have on your machine.

You can download the latest build of xSQL Bundle from: http://www.xsqlsoftware.com/Download.aspx

xSQL Data Compare 3.0 requires .NET Framework SP1

2008-12-04T08:26:00.000-08:00

We have receives a couple of reports from users who are not seeing the INSERT statements when scripting a table (schema and data) from xSQL Data Compare or when generating a synchronization script. The issue is related to a structure that we are using in version 3.0 that was apparently introduced with the SP1 of .NET Framework.

If you are experiencing this issue with xSQL Data Compare either install the SP1 for .NET Framework which you can get from: http://www.microsoft.com/downloads/details.aspx?familyid=79BC3B77-E02C-4AD3-AACF-A7633F706BA5&displaylang=en or email us if for some reason you do not want to install SP1 and would rather just go back to xSQL Data Compare version 2.5 which does not require SP1.

Synchronize with bulk insert – xSQL Data Compare

2008-12-01T11:29:00.000-08:00

By default the database synchronization script that xSQL Data Compare generates contains one INSERT statement for every row that exists in the source database but does not exist on the target (destination) database. When the number of such rows is extremely large you can imagine that the synchronization script will likely get to be very large also and such script on a transaction will make for an extremely large transaction that is bound to create performance issues.

It is for that reason that xSQL Data Compare provides a little known “switch” called “Synchronize with bulk insert” which will change the default behavior and instead of generating one INSERT statement for each row will generate only one bulk insert statement for each table in the comparison making for a much more efficient synchronization script.

The “Synchronize with bulk insert” option can be found under the main menu Compare / Data Options.

In case you are not familiar with xSQL Data Compare – it is a simple tool that allows you to compare and synchronize the data in two SQL Server databases and supports SQL Server 2008, SQL Server 2005 and SQL Server 2000. It is free for SQL Server Express databases with no limitations and it is also free for other editions of SQL Server but with some limitations. You can read more and download your copy of xSQL Data Compare from our website at: http://www.xsqlsoftware.com

Microsoft hires DeWitt and starts Jim Gray Systems Lab in Madison Wisconsin

2008-11-20T16:02:00.000-08:00

I just read this exciting news (a bit old apparently but I did not know) that Microsoft has hired David DeWitt to head the newly founded Jim Gray Systems Lab in Madison Wisconsin. Why is this exciting you say? Well, two reasons: first, xSQL Software has strong ties to Madison Wisconsin and to the database department that DeWitt headed for many years; and second, because this is a great move on Microsoft’s part. What makes this very interesting is the fact that in his new position DeWitt works for the Data and Storage division that makes SQL Server rather than working for the Microsoft Research division – in practical terms that means that his ideas and work have a much higher chance of being applied in the real world applications that Microsoft makes.

We wish DeWitt and the Jim Gray Systems Lab great success!

xSQL Documenter 2.0 released

2008-11-12T09:53:00.000-08:00

We just released a new version of xSQL Documenter that adds supports SQL Server 2008, Sybase SQL Anywhere as well as new features and enhancements. Here is what's new on xSQL Documenter 2.0:

Support for SQL Server 2008
Support for Sybase SQL Anywhere 10.0 and up
Adds a “choose objects” page in the GUI where you can specify what object types you want to document.
Support for all DBMS platforms in the "exclude objects" GUI. Previously this GUI supported only SQL Server.
Support for arbitrary HTML in database comments – see /pt command line switch.
Analysis Server: support for XML comments in MDX scripts. This enables documentation of calculated members.
Support for cross database and cross server dependencies (through linked servers)
Support for column dependencies (which columns of which table a stored procedure selects or updates.
SQL Server: support for XML comments on objects that don't have their DDL stored on the server, such as tables. This is implemented with extended properties.SQL Server: add extended properties filter. Using this you can restrict by name the extended properties that are fetched by xSQL Documenter.

xSQL Documenter generates compiled CHM and html database documentation for virtually all DBMS platforms including SQL Server, DB2, Oracle, MySQL etc.

You can read more about xSQL Documenter and download your copy from: http://www.xsqlsoftware.com/Product/xSQL_Database_Documenter.aspx

xSQL Data Compare custom comparison keys

2008-11-05T08:47:00.001-08:00

A conversation with a customer yesterday brought to light the potential confusion with the custom comparison keys that xSQL Data Compare allows the user to define "on the fly". The customer was worried that defining a custom unique key would change the structure of the table - so here is some clarification:

when you define a custom comparison key for a table in xSQL Data Compare the database schema (structure) is not affected - no changes are made to the schema;
the custom comparison key can include multiple columns from the table in question;
the key must be unique - that is the combination of the values of the columns included in the key should uniquely identify a row in that table.
the comparison key defined on one of the tables in the comparison should match the comparison key defined on the other table - the rows from both tables in the comparison are paired based on the key values.

xSQL Data Compare provides for comparing and synchronizing the content (the data) of two SQL Server databases. It supports SQL Server 2000, SQL Server 2005 and SQL Server 2008 and it is completely free for SQL Server Express and its predecessor MSDE. xSQL Data Compare can be downloaded as part of xSQL Bundle from: http://www.xsqlsoftware.com/Download.aspx

Don't forget to vote today

2008-11-04T12:59:00.000-08:00

To all US citizens - don't forget to vote - it is more important than almost anything else you may have to do today. We would even offer you a free license for any of our products if you voted today but that would violate federal and state laws - we learned that from the Starbucks example (Starbucks was advertising a free tall coffee for anyone who had the "I voted sticker" but they are now giving free tall coffee to anyone who asks for it otherwise they would be in violation of the law).

And remember your mail-in ballot must be received by the county election officials before the polls close otherwise your vote won't count. So, don't drop the ballot at the post office today - you are too late - instead, hand-deliver it to any precinct in your county (the one where you are registered to vote).

SQL Server 2008 penetrating faster than the predecessor

2008-11-03T10:23:00.000-08:00

We have not conducted any rigorous research on this but based on our empirical data it appears that there is a lot more excitement about the SQL Server 2008 release than there was for the release of SQL Server 2005. Our user base today is orders of magnitude larger than it was when SQL Server 2005 was released but proportionally we have been receiving 3 times as many inquires about support for SQL Server 2008 nowadays than we were receiving then for SQL Server 2005.

A survey conducted by SQL Server Magazine showed that about 81 percent of the participants are still on SQL Server 2000 and only about 40 percent are using SQL Server 2005. I am willing to bet that one year from now over 50% of those same people will be already using SQL Server 2008 - there are just too many cool and very useful features in SQL Server 2008 that make it really hard to pass.

All xSQL Software tools support SQL Server 2008.

xSQL Script Executor - batch parsing modes

2008-10-31T11:18:00.000-07:00

In addition of adding support for SQL Server 2008 the new version of xSQL Script Executor adds a user option called batch parsing mode which determines the type of batch parsing that will be performed on the source scripts. The purpose of the batch parsing is to identify batches which are T-SQL statements in the script files separated by GO. xSQL Script executor supports the following batch parsing modes:

Simple: is the fastest batch parsing mode. It simply identifies batches in the source script with very little overhead.
Common: the source scripts are parsed first to remove T-SQL comments, and then batches parsing takes place
Thorough: the source scripts are parsed to remove comments, string constants, dynamic scripting and then the batch parsing takes place. The "Thorough" mode can take significantly longer than the other parsing modes. If you know that the source files do not contain dynamic scripting, Common or Simple mode will do the job well.

xSQL Script Executor is a free utility that provides for executing multiple T-SQL scripts against SQL Server. You can download your copy of xSQL Script Executor from: http://www.xsqlsoftware.com/Download.aspx

New build of xSQL Profiler published

2008-10-30T10:11:00.000-07:00

We just released a new build of xSQL Profiler that does two main things:

significantly improves the process of uploading trace data from the servers being traced to the central repository; we had received reports from users indicating that under stress conditions the pulling of the trace data from each individual server into the central repository could time out and hence there would be gaps in the trace data collected. The improved process performs significantly better and should eliminate any issues experienced with the previous build.
allows the user to see the status of a given trace on each one of the servers on which the trace runs. In cases when xSQL Profiler may loose the connection with a server it will not be able to stop the traces that it has started on that server. Of course it would stop them when the connection is re-established but there was no easy way for the user to see if the trace was still running on some of the servers while stopped on some others. Now the user can simply "drill down" on a trace and see what is happening on each server.

xSQL Profiler allows you to define a trace and schedule it to run on any number of SQL Servers simultaneously while automatically collecting trace data from all servers into a central repository. xSQL Profiler supports SQL Server 2008, SQL Server 2005 and SQL Server 2000.

Don’t use this – it may not be supported in future versions – lessons from the field

2008-10-28T10:50:00.000-07:00

“Avoid using this feature in new development work…“– I am sure this sounds familiar to all software developers out there. I am also sure (based on real life examples that I have encountered over the many years of experience) that a lot of developers simply ignore those warnings. Take for example my latest “encounter” – recently written T-SQL queries that still use the old *= outer join syntax! And how long has it been since Microsoft recommended not using it? At least 10 years!

Why do programmers do this?

resistance to change - the way you are used to doing things makes sense the new way usually doesn’t (to you that is);
forgetting that your objective as a software developer is not just to “get it done” but to “get it done right”;
not giving enough consideration to the potential future costs of the short-cuts you take today.

I would like to remind all developers out there (especially those that do take unnecessary, easily avoidable “short-cuts”):

it may be you who will have to go back and spend a portion of your life doing the most annoying and boring job of all – that of tweaking your own code to make it compliant instead of spending the time creating new and exciting things;
the costs associated with those kind of changes can be staggering for the company and if such costs could have been avoided, but for your negligence, then you really did not do right by the company. Or, to put it in simpler terms: you did not do your job and did not deserve the compensation you received, let alone any praise that your superiors lavished on you.

What’s the lesson? If it says “avoid using it” you better not use it, take the time and change your “ways” – it is not fair to the recipient of your work and it may come to haunt you!

RSS Reporter - real life scenarios

2008-10-27T15:04:00.000-07:00

Seeing that we have not done a great job in explaining how a SQL Server database administrator can benefit from utilizing RSS Reporter one of our customers, Chad Miller, took matters on his hands and prepared a great presentation for the Tampa Bay SQL Server User Group. In just a handful of simple slides he explains the awesome power of the RSS Reporter for SQL Server – he shows how in a matter of minutes an administrator can create an integrated dashboard that allows him to monitor:

backups;
log errors;
jobs and replication;
help desk systems;
disk space;
up time

Check out Chad's presentation and download your copy of RSS Reporter today - it is completely free with no strings attached for one SQL Server instance.

while 1 < 1000

2008-10-24T17:27:00.000-07:00

This is a true story, not a joke. This young student asked me to look at a piece of code he had written. He had an intentional endless loop that looked like this:
while (1 < 1000)
{
…
}

This sparked my curiosity and I asked him: why not do “while (1 < 2)”? I was expecting something like “I just like it that way” but no, to my utter surprise his answer was – “I just want to make sure”! I guess he was afraid that after a few loops 1 could grow and become bigger than 2! I contained my laugh and did not say anything but the puzzled look on my face probably gave him a cue that something was wrong with his reasoning.

It is funny and it does make one laugh (a couple of colleagues couldn’t stop laughing when I told them the story) but I also find it very sad for a college student to be at that level.

Stored Procedure naming conventions

2008-10-24T09:04:00.000-07:00

I got a question about stored procedure naming conventions today. I spent a bit of time thinking about it and here is what I advised the client:

The most important thing is to have a standard that is accepted by the whole team and is followed (possibly enforced). The actual standard that is adapted is not very important. You should remember that there are two main goals that the standard (naming convention) is intended to achieve:
- Avoid potentially costly mistakes (for example dropping a table while you think you are dropping a view)
- Improve the productivity of the developers by making the code much easier to read (understand) and reducing the time it takes to locate an object.
Given those two simple objectives it follows that the name of the object should tell us:

What type of object it is;
What is the base object on which this depends (if applicable)
What does it do (Insert, Delete, Update, Create etc.)
Other group identifiers if necessary

As far as specifics the only warning I am aware of (this was valid in SQL Server 2000 and I am not sure it applies to 2005) is that you should not prefix the stored procedures with sp_ - there is an article by Brian Moran on SQL Server Magazine (http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html) that explains why.