t-sql random string generator

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

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

xSQL Data Compare 3.0 requires .NET Framework SP1

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

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

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

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

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

xSQL Data Compare custom comparison keys

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