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.