Kindle time at xSQL Software - get one

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

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

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:

1. Functionality: Oracle Data Compare supports Oracle Geometry, complex data types and unicode data; they DON’T
2. 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.
3. 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 xsql.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

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

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:

1. Allows session naming by clicking on the name without launching it
2. 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
3. Fixes an issue with screen resizing
4. Fixes an issue with print wrapping for windows that support printing.
5. Adds a UI formatter for XML data types
6. Improves the process of comparing select tables

You can download the new build from http://www.xsql.com/download/sql_server_data_compare/

Oracle Data Compare released

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

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.

1. First time deployment
• Backup / restore
1. Backup the database on the source
2. Copy the backup file to the target machine
3. Restore the database on the target
4. Create logins and set permissions as needed
• Compare and Synchronize
1. Create database on the target machine (blank)
2. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
3. 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.)
2. Database exists in the target server
• Compare and Synchronize
1. Use xSQL Object to compare and synchronize the database schemas of the source and the target. 
2. 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:

1. Restore the remote database on your local environment
2. Use xSQL Object to compare your source database with the restored database. Generate the schema synchronization script and save it.
3. 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. 
4. Deploy your schema synchronization script to the target machine. 
5. 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

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!

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:

1. 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.
2. 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.
3. 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

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?

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:

1. Launch the Script Executor user interface. Start a new project via File/New Project;
2. Add database(s) to the project;
3. Add Sql script(s);
4. 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;
5. Configure package options via Package/Options;
6. 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!

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:

1. I have a lot of experience with SQL server;
2. 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; 
3. 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:

1. 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.
2. 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;

1. Overestimating their ability while underestimating the effort required to get something done;
2. Misjudging the market;
3. 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):

1. What is my goal? What will success look like?
2. 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.
3. 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?
4. Have I thoroughly investigated the competing products? What do they do right? Where do they lack?
5. 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?
6. What will be that “killer” feature that is going to make the customers choose your product instead of those more established competing products?
7. 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

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

1. 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.
2. 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.xsql.com/download/sql_server_comparison_bundle/

SQL convert vertical list to horizontal

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!

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.

1. 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.
2. Usability: everything you need, nothing you don’t, all where it should be, one click compare – nothing approaches the efficiency of this interface. 
3. Value:
1. Free for SQL Server Express – can anyone beat this?
2. Free for other editions of SQL Server when the databases being compared have a limited number of objects – anyone out there can match this?  
3. 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!

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

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:

1. 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;
2. you will receive $0.1 for every unique visitor to our site, originating from your territory, over the established baseline. 
3. you will receive $0.5 for every unique product download (your territory only) over the established baseline.
4. 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.xsql.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@xsql.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

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@xsql.com

Major release: SQL Server Data Compare 4

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:

• download Data Compare 4
• what's new?
• product page
• quick tour

Important notes:

1. Have an active upgrade subscription? You do not need to contact us. In the next 3-5 days you will receive your new license.
2. 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.
3. 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@xsql.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?

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

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.xsql.com/download/script_executor/

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

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

1. Script SP1 out as CREATE
2. Right click on SP1 on the left panel and rename it to SP1_backup
3. 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.