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

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

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

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.xsql.com/LiteEdition.aspx. You can download your free copy from here: http://www.xsql.com/download/sql_server_data_compare/

Script Executor for DB2 free today

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

Profiler for SQL Express

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

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.xsql.com/download/database_documenter/ and let us know what you think.

xSQL Builder new build available

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 indispensable tool.

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

Best way to create an rss feed from SQL Server

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

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

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

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

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

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

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

Can a simple Alter Trigger statement cause serious blocking?

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

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.

Automate Deployment of SQL Server Databases

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

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.

Language a big barrier to penetration

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

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

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

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.

1. If you cannot access the clients' servers from your location (most likely scenario)
1. 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;
2. 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. 
2.  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

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

Where is Google - a support call

(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

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

Trouble connecting to remote SQL Server instance

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.