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/

Don't forget to vote today

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

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

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

New build of xSQL Profiler published

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

“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

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

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

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.

xSQL Profiler versus SQL Profiler

What do you need xSQL Profiler for when you have SQL Profiler? Well, here are three important things you can do with xSQL Profiler that you can’t with SQL Profiler:

• you can define a trace once and run it on multiple servers simultaneously;
• you can schedule the traces to start and stop automatically;
• trace data from all servers is automatically collected into one central repository

xSQL Profiler supports SQL Server 2000, SQL Server 2005 and SQL Server 2008 all editions including MSDE and SQL Server Express.

Support for Change Tracking and Data Compression

xSQL SDK 3.0 and xSQL Object 3.0 support the new change tracking and data compression features that SQL Server 2008 introduces.

By default change tracking is NOT compared and neither is synchronized however the user can turn that on easily. It is important to note that Change Data Capture, which is different from Change Tracking, is not supported in this version of xSQL Object and xSQL SDK.

xSQL SDK and xSQL Object recognize, compare and synchronize data compression property for the following database objects:

• Tables
• Primary Keys
• Unique Keys
• Indexes

By default data compression is compared and synchronized.

New data types supported by xSQL Software's database comparison tools

The just released xSQL Object v3, xSQL SDK v3 and xSQL Builder v2 support all new data types that Sql Server 2008 introduces as well as the enhancements on the existing data types. xSQL Object allows you to compare and synchronize SQL Server database schemas, xSQL SDK allows you to integrate database schema comparison and synchronization in your application and xSQL Builder allows you to package SQL Server databases in an an easy to deploy executable.

Following is the list of the new and enhanced SQL Server 2008 data types

• date: a date/time data type that stores only the date portion.
• time: a date/time data type that stores only the time portion.
• datetime2: similar to existing datetime data type, but with a larger fractional seconds and date range.
• datetimeoffset: a date/time data type that has time zone awareness.
• geography: a new data type for geodetic spatial data defined latitude and longitude coordinates.
• geometry: a new data type for planar spatial data defined by coordinate on a plane (used mostly for maps).
• hierarchyid: used to store hierarchical relationship.
• FILESTREAM varbinary(max): a variation of varbinary(max) that stores the BLOB data as file in the file system outside the database.

You can download those products from http://www.xsql.com/download/

Support for SQL Server 2008 – new release

We are very excited to announce a major new release of the following tools which now provide full support for SQL Server 2008:

• xSQL Object V3 (free for SQL Server Express)
• xSQL Data Compare V3 (free for SQL Server Express)
• xSQL SDK V3
• xSQL Builder V2
• RSS Reporter V3 (free for one SQL Server instance)
• xSQL Object Search V2 (free tool)
• xSQL Script Executor V2 (free tool)

What's new - a detailed description of changes and enhancements on this new release can be found at: <>. Following are some highlights:

• Support for all new or enhanced data types such as geography, geometry, hierarchyID etc.
  Support for the table level change tracking feature introduced by SQL Server 2008.
• Data compression support for tables, primary key constraints, unique constraints and indexes.
• Support for the enhanced full-text catalogs and indexes.
• Support for Remote Service bindings for Service Broker.
• Improved object dependency handling.

You can download those tools from: http://www.xsql.com/download/

He is just the data guy – what the …. does he know!

This is a true story. I don’t think that this particular opinion about those “data guys” so eloquently articulated by this particular person represents a majority opinion and as such I should probably simply ignore it but it stung pretty badly so I couldn’t put it to rest without expressing my disappointment in writing.

Here is the story. I was consulting for this marketing company who was working on a project for a big customer. An experienced data guy, let’s call him Frank (not the real name), on the customer’s side had provided the data for the project as well as some important pointers on what he thought we should be focusing on. Well during one of the meetings at this marketing company’s site when the discussion was going on a direction that completely ignored Frank’s suggestions I reminded them of what Frank had said. The person in charge, forgetting for a moment that I am a “data guy” by trade as well, said “Frank is just a data guy, what the …. does he know!”. As the last words were coming out of his mouth he realized the mistake but it was too late.

This marketing company has a small IT department which is “coincidentally” made up of “data guys” and I had seen this person tell those data guys how important they were to the company and how much their hard work was appreciated and blah, blah, blah… I was just glad those guys were not in the room at that moment.

As for me personally, I don’t hold any animosity towards this person with a marketing degree who for whatever reason (other than the fact that this guy makes twice as much as those data guys) thinks that he is smarter and better than those “data guys” who have computer and math degrees. I simply doubled my rate for this client and I am sure that in the next assignment (if it ever comes) I will be twice as smart J

There, now I can forget about this!

New version of xSQL Profiler released

We have just released a new version of xSQL Profiler. In addition of performance and other improvements on the core (including bug fixes of course) it includes custom query templates that allow the user to gain invaluable insight from the trace data that the xSQL Profiler collects. It utilizes SQL statement signatures to identify the queries that may be bogging a particular server down because of CPU consumption, I/O traffic etc.

The performance reports included in this release are:

• Top N queries by number of times executed
• Top N queries by CPU time
• Top N queries by duration
• Top N queries by number of Reads
• Top N queries by Read/Writes
• Top N queries by number of Writes

xSQL Profiler supports SQL Server 2008, SQL Server 2005 and SQL Server 2000 all editions. You can even trace SQL Server Express and MSDE instances.

Completely free with no restrictions or limitations for up to two SQL Server instances. Download your copy today from: http://www.xsql.com/download/sql_server_profiler/

SQL Server Express 2008 - 3 editions

There are three different version of SQL Server 2008 Express:

SQL Server 2008 Express
SQL Server 2008 Express with Tools
SQL Server 2008 Express with Advanced Services

What are the key differences? Which one should I download?

First let’s look at SQL Server 2008 Express and SQL Server 2008 Express with Tools – there is virtually no difference in functionality – the only difference is that the download package for the SQL Server Express 2008 with Tools includes a bare bones version of Management Studio to allow you to perform some admin functions from a nice interface. Well, there is one more little difference – SQL Server Express with Tools includes PowerShell Integration which otherwise would have to be installed separately. So which one should you download? Well, if you already have SSMS (SQL Server Management Studio) installed on your machine then there is no reason to download the SQL Server Express 2008 with Tools.

When it comes to SQL Server Express 2008 with Advanced Services things are different – there are a few critical (this is relative term – those are critical services if you need them) services included in this edition that you otherwise don’t get:
- Integrated Full Text Search
- Reporting Services (increase RS memory limit; RS Word/Rich Text Export; IIS agnostic report deployment; Enhanced gauges & Charting; Business Intelligence Developer Studio)

Also, the SQL Server 2008 Express with Advanced Services includes Synchronization Services which in the other two editions would require a separate installation.

So, if you need any of those advanced services your choice is clear – download the SQL Server Express with Advanced Services.

You can learn more about SQL Server 2008 Express and download your copy from Microsoft’s official site at: http://www.microsoft.com/express/sql/default.aspx

xSQL Profiler deployed in minutes

From all the comments we have received so far it appears that we have hit the mark with the “agent-less” feature of xSQL Profiler. Unlike many of the database monitoring / diagnosing tools that require the installation of service agents on the target servers and consequently require days to deploy, xSQL Profiler does not need any agents. The xSQL Profiler application and service are installed in minutes on a single machine from where you can define and schedule traces to run simultaneously on as many target servers as you need. The trace data from all servers are pulled automatically on predefined time intervals into the central repository. It can’t be simpler than that. And did we mention that it is completely free for up to two SQL Server instances? Go ahead, download your copy today and let your colleagues know.

PS not only can you trace SQL Server 2000, SQL Server 2005 and SQL Server 2008 but you can also trace SQL Server Express and MSDE instances.

Every 10 PCs migrated to Vista = one car off the road

“Every 10 PCs migrated to Windows Vista is equivalent to taking one car off the road in terms of greenhouse gasses” (Windows Vista Energy Conservation - http://www.microsoft.com/windows/products/windowsvista/enterprise/vistafacts/default.mspx).

This got my attention fast – consider the number of PCs in use and just imagine the impact this could have! I decided to dig deeper and inform myself so that I could potentially become an advocate of this cause – after all I would much rather convert my PCs to Vista than give up driving my car to save the environment (well, as the melting of the arctic ice continues we may have to soon give up the car and much more but if there is anything that would help us postpone that I am all for it).

As I started reading the paper the first thing that struck me was the disclaimer section which in essence says “if you believe anything we say here don’t blame us it’s your own fault” – I found this a bit discouraging but given that nowadays everybody and their uncle seems to have some claim against Microsoft I thought that this was the prudent thing for them to do and continued reading the paper.

So, what did the paper tell me? It told me that yes, potentially, assuming your PC stays on for 24 hours with neither the monitor nor the PC ever being turned off or going to “sleep mode” then yes the energy savings you could realize by either turning the PC off for the 16 hours you don’t use it or by replacing the operating system with Windows Vista, which by default will make the PC go to sleep mode when not in use, are significant. Based on published and accepted averages the carbon dioxide (CO2) you would keep off the atmosphere is about 1,178 lb which is the equivalent of about 10% of the CO2 emitted by an average car on one year. So, yes with those calculations 10 PCs converted to Vista equal one average car off the road. But, is the scenario considered in these calculations realistic? Absolutely not! Even in enterprises where the employees maybe instructed to leave their machines on so that they are available for software installs, patching and remote access the monitors are likely set to go to sleep mode so that alone removes a good portion of the presumed savings. Furthermore, what’s really critical to remember is that Windows Vista may make realizing energy savings easier and more convenient but those potential savings are available and can be realized without having to upgrade to Windows Vista.

Please note that this is not a case against upgrading to Windows Vista – the reasons for upgrading are abundant in many scenarios – but it is rather taking aim at bold and not always well founded marketing claims.

Is this smart marketing? I would say yes, in a World that is becoming more and more aware of the environment a claim like this has a high chance of getting the attention of the potential customers – it certainly worked on me.

Does it work beyond getting attention? The answer to this is a bit more complicated. While it may sometimes backfire, often it works. Here is why. For a mid level manager that is trying to make the case for upgrading, such claims made by reputable companies are a huge help. These managers don’t need convincing for themselves - they may have a lot of other, well founded but hard to explain to upper management, reasons for upgrading; what they need is ammunition that will help make their case a simple no-brainer and such claims give them exactly that ammunition.

Is it ethical? If the claim was false then this wouldn’t be a question but it is not false. I think the ground is a bit shaky here and so is my answer. To be completely on the safe side the statements should highlight the average scenario instead of the extremes but I can't quite say it is unethical to highlight the extremes.

MCDBA with no experience OR 5 years of experience

If you had to choose between hiring an MCDBA who has very little real life DBA experience and someone who has 5 years of practical DBA experience but no certification which one would you go for?

Please submit your vote on the right hand panel here and if you wish explain your rational on a comment to this posting.

Please note that this is completely hypothetical – no one’s fate is being decided by your response here but it would be very interesting to see if there is an overwhelming opinion in favor of one or the other.

If you are a Microsoft MVP you can get it FREE

Yes, you heard it right – if you are a Microsoft MVP you can get any of our tools for free – simply send us a link to your MVP profile and let us know which products you are interested on (you can email us at sales[select char(64)]xsqlsoftware.com).

Here are some simple rules:
- the complimentary licenses you receive are for your own personal use only (includes your own consulting practice).
- The offer is limited to a single user / single machine license for xSQL Object, xSQL Data Compare, xSQL Builder, xSQL Documenter.
- The offer is limited to a single installation and five SQL Server instances for xSQL Profiler and RSS Reporter.

A programmer who knows everything

I got this resume the other day – there was a big section that listed technologies that this guy seemingly had experience with, and the list was long… really long, C#, VB.NET, C++, WCF, WPF, WF, Visual FoxPro, PHP, SQL Server, MySQL, Oracle, Access, T-SQL, PL/SQL, XML, UML, Erwin, Ajax, Flash, J2EE, Spring, Struts, JSP, JMS, JMX, LDAP, SSO, BEA ALUI etc., you name it. I had a hard time recalling any names or acronyms that were missing from his list and believe me, being a manager type, I know acronyms and names, it is my job to know those things exist and to have a good idea of what they are but would I ever dare to say I have experience on them? Absolutely not unless I would be stupid enough to think that having spent a couple of hours on something qualifies me to claim I have experience with it.

After I trashed his resume I started thinking and came to question my reasoning – maybe it is not this guy's fault, maybe it is the stupid recruiters and managers who often make the requirements section a laundry list of technologies and this person, like many others who are trying to find a job, is simply responding to this madness the best he can: by trying to cover all bases. Maybe this guy who has the courage to list everything is trying to convey the message: “I am a programmer. I can deal with any technologies you can through my way.” Or maybe his message is: “since you don’t really know what you are looking for I am as good as any other candidate out there”.

For the moment I don’t have an answer, just questions in my mind. However, one thing I know is that those type of job ads and resumes make for a very inefficient hiring process. When I am hiring a programmer I have two basic cases – case 1: my excellent resource planning provides me with sufficient time and money to find a great programmer and give him/her adequate time to catch up with the technologies he will need to use. If he is already experienced on those technologies all the better but that is not my main concern, my goal is to find the best programmer I can find with the particular technology being a distant second; case 2: due to my poor resource planning I need a programmer “yesterday” who must be productive on day one (like you know who… pun intended) – in this case, unfortunately I may have to sacrifice on the quality of the programmer and go for someone who has experience with the particular technology. So, the requirements section for the case one would focus on degrees, certifications and years of programming experience and I would simply indicate the technologies you are expected to work with. What I care about is that you are a great programmer and that you are willing to work with those technologies even if you have never worked on them before. Whereas the requirement section for case 2 would look something like: x years of hands on experience with C# and SQL Server. In other words just what I need you to work on the first day you come in the office. You may have a PhD in computer science but unfortunately it is not going to do me any good in the short term if you have never worked with C# and SQL Server.

I believe that a well written job ad that tells the potential candidates exactly what you are looking for will significantly reduce the amount of useless job applications while increasing the quality of the applications that come in. So, to all the recruiters and hiring managers out there – please take the time to write clear, concise, fluff-less job ads - you will be doing yourselves, and all those looking for a job, a big favor.

Finally, thanks for reading this and whether you agree with it or not, if you are a programmer and/or database administrator don't forget to check out our cool SQL Server tools at http://www.xsql.com/ - almost all of them have a fully functional totally free version that never expires and never asks you to register or activate - just download and save yourself hours of otherwise tedious work.

SQL Server 2008 developed with the customer in mind!

“Microsoft developed this release of SQL Server with the customer in mind,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft…

What a surprise! So, who did Microsoft have in mind when developing the other releases of SQL Server!?

We all misspeak at times but I find it very surprising (in a bad way that is) that a Microsoft corporate VP would do so on such a major occasion as the release of SQL Server 2008!

Tracking database schema changes - why and how

There are many scenarios that highlight the need for tracking changes to the database schema – security, auditing and performance issues often require that you be able to pinpoint what changed when in the database schema. An index may have been dropped, a stored procedure may have been changed, a trigger may have been added and so on, and any one of those changes, however “small”, could have huge un-intended consequences from performance degradation to loss of data integrity and more.

Why can’t we use the daily full database backups to always go back and figure out what changed when – after all the database backup does not only capture the data but it also captures the database schema? Yes, BUT:
- full database backups are in many cases only available for the last few days / weeks and you are not able to go back beyond that period;
- it takes a long time to let say restore 20 different versions of a large database and figure out what changed in the schema from one version to the other
in short, even if it is possible it is not practical.

A nifty feature in xSQL Object called “schema snapshot” addresses this issue in very elegant and practical way – it allows you to take snapshots of the database schema that:
- capture 100% of the database structure – not the data, just the schema;
- have a very small footprint thus enabling you to store them forever – the database itself could be in the tenths or hundreds of Gigabytes in size whereas the schema snapshot could be a few Kilobytes in size;
- can be compared with each other and with the live database instantly without having to restore anything – instead of taking days restoring and comparing trying to track down a change you can do that in minutes using the snapshots.

xSQL Object is free for SQL Server Express with no limitations and is also free for other editions of SQL Server as long as the number of objects in the database does not exceed certain limits that you can find here…

There is still time to get your RSS Reporter license for free

It's about 10 am EST right now which means there are about 12 hours left to claim your free RSS Reporter license.

RSS Reporter for SQL Server is a great little utility that allows you to generate aggregate RSS feeds containing job status information from multiple SQL Servers eliminating the need for the annoying email alert notifications. A really cool feature is also the ability to right any T-SQL query and with a click of a button generate a standard RSS feed containing the results of that query.

To claim your free license go to: --this offer is no longer valid--

4th of July celebration - huge software giveaway

In celebration of the 4th of July we are giving away 1000 RSS Reporter licenses worth $99 each. You can read the details here: --this offer is no longer valid-- but hurry the offer is limited to the first 1000 people and it expires at the end of the day on July 4, 2008.

Script database – schema and data together

Scripting a SQL Server database schema is a fairly trivial task if the dependencies between the objects are ignored. The trouble is that if you generate a schema script on server A and try to execute it on server B to create a copy of that database you will soon realize how critical those dependencies are – the script will simply fail to execute.

That’s where xSQL Object and xSQL Data Compare come in – both of those tools allow you to script an individual object in the database or the whole database schema and the script you get is properly ordered respecting the dependencies between objects so that it executes flawlessly.

Furthermore, those tools allow you to script the data as well or you can script both the schema and the data in one place. Scripting both schema and data together is very cool as it allows you to transfer a whole database from one server to another even if you can’t use a backup and restore. For example, you have a database hosted on a web server somewhere and you can execute T-SQL scripts against the database but you don’t have access on the server to be able to place a copy of your database backup which you can then restore. No worries, you simply generate a schema and data script of the whole database and execute that on the target server and there, you have transferred the whole database.

Best part: the scripting functionality included in the xSQL Object and xSQL Data Compare is completely free – no limitations, no expiration.

Synchronize with bulk insert – xSQL Data Compare

When comparing and synchronizing data between two SQL Server databases if you have many new rows on one database that need to be “copied” over to the other database, by default the sync script that xSQL Data Compare generates will contain one INSERT statement for every new row being copied. Now, that is fine when the number of those insert statements is not very big but when you have hundreds of thousands of INSERT statements the script becomes very hard to manage and the performance will suffer.

It is for that reason that we included the ability to synchronize with bulk insert – to do that the user simply checks the “synchronize with bulk insert” option and specifies a folder where the bulk insert files will be stored temporarily. The resulting sync script is easy to manage and will perform a lot better.

Tracing MSDE and SQL Server Express

About a month ago we released xSQL Profiler and advanced SQL Server profiling tool that allows you to define granular traces, schedule them to run when you want on multiple SQL Servers simultaneously, and automatically collect the trace data into a central data repository which you can freely query - all from one location without any agents on the target servers.

We have mentioned that one of the best things about xSQL Profiler is that it is free for up to two SQL Server instances but what we neglected to specifically mention is that not only does xSQL Profiler support higher editions of SQL Server 2000, SQL Server 2005 and SQL Server 2008 but it also supports MSDE and SQL Server Express. Yes, you can now use xSQL Profiler to trace MSDE and SQL Server Express installations.

Download, enjoy and don’t forget to send us your comments and suggestions!

When “can’t be done” is not an option

When your boss, under fire from his boss, tells you “this must be done by Monday morning…” and you feel / know it can’t possibly be done the way you think it should be done what do you do?

1. tell your boss “yes boss I will take care of it” and then when Monday morning comes you tell him “sorry, I tried but I couldn’t get it done...” and then wait for him to fire you?
2. tell your boss “yes boss I will take care of it”, throw your professional standards and pride off the window and “proudly” deliver a shoddy piece of work on Monday morning and wait for him to shower you with appreciation before he realizes that what you delivered was crap and fires you?
3. tell your boss “this can’t be done” and that he is an idiot for trying to impose such an unreasonable order and just resign from your job?
   
   OR
4. you assure your boss that you understand the urgency of the matter and politely ask for permission to go sit down in your corner, come up with a game plan and then meet with the boss to go over your plan?

Well, this is a no brainer you would say – yes, I know I don’t need to tell you that #4 is the best choice but I have seen enough programmers choose 1 and 2 and less frequently choose 3 that I believe a reminder can’t hurt. While number 4 is the obvious choice it is not necessarily the first thing that comes to mind when you are under pressure.

A small clarification - when I say that all three options 1, 2 and 3 will result in you getting fired I am exaggerating a bit to make the point but even if you don’t get fired at the very least your reputation will be severely damaged.

Choosing option 4 is a testament to your professionalism and experience. It gives you the much needed time to break the job down into more manageable and easier to estimate tasks. While doing that you may discover that it is possible to get it done by the deadline if certain resources are made available to you which would be great! But, even if your initial intuition / estimate is proven right and the job can not possibly be done you go back to your boss with an action plan that details what can be done by the deadline and when the whole job can be completed. At that point your boss has the ammunition he needs to go back to his boss and secure an extension of the deadline or modify the requirement / scope of the job so that you can deliver what may be the most critical part of the job by the deadline. Details aside, this approach leads to “happy endings” – the boss will respect you a lot more and chances are that he will never again tell you that “it must be done by Monday” but will rather ask for your professional opinion on what maybe doable and what not.

Tell me what you think – feel free to leave your comments here.

Google Adwords – increase quality or bid $$

I am sure that in a lot of cases when advertisers are trying to mislead the customers this makes perfect sense but it gets to be incredibly frustrating when it does not work as I would think it should.

Here is the source of my frustration: we have a cool tool for documenting databases, xSQL Documenter that allows the user to automatically generate compiled CHM and HTML documentation for databases on virtually any platform – you can document SQL Server databases, Oracle databases, DB2 databases, MySQL databases, Informix databases, Access databases, PostgreSQL databases.

Now one would think (at least I think) that the keyword/phrase “document database” is as relevant as it can possibly get and a browser who plugs in “document database” on the google search box would be well served when directed to a page like the xSQL Documenter page. But, unfortunately the google algorithm does not “think” so – it keeps pushing me to increase quality or raise the bid and since there isn’t much I can do to improve the quality I am left with the other choice, that is, “raise the bid”! Are they price-gauging or is it just an imperfect algorithm?

Please feel free to comment or tell your google adwords story.

Making changes to your production SQL Server database

Not a week goes by without running into a software developer that is still manually making changes to the production SQL Server databases! What’s wrong with that you may say? It is risky and a big, big waste of time!

Here is a common scenario I run into:

A while back you have developed a web site that uses SQL Server on the back-end. The initial deployment was a breeze and everything worked perfectly. Now it is time to tweak and enhance the website with new features and functionality. In addition of making changes and additions in your code you start making changes to the database as well:

• added a couple of new tables
• modified a handful of tables (added columns, changed column types, added indexes, added /changed constraints, added new relationships etc.)
• added a few new stored procedures and views and modified some more of them
• added / modified a dozen user defined functions
• added / modified triggers
• etc., etc…

And all the while you diligently kept track of every change you were making knowing that you would soon need that log of changes.

After two months of hard work and sleepless nights, after testing and retesting everything you are ready to deploy your new version of the website – you know that deploying the application is a click of a button but the application is not going to work unless all the database changes you made are correctly propagated to the production database. You look at your long list of changes and realize that it is going to take hours to manually apply those changes. You wish you could just back up your development database and restore it on the production server but the production database contains live data which must be preserved.

So, you tell your client that you need to take the website off-line from 10 PM on Saturday night to 6 AM on Sunday morning. And when that day comes you take the site off-line, make a full backup of the database and start applying those changes one after the other. The cascading “can’t change this 'object' because it references this other 'object'” messages start to drive you insane but you have to do this so you keep moving along. After hours of frustration you seem to have reached the end of your list and as the sun is dawning you start getting more cheerful. You click on that “button” and publish your application – you run through some tests and everything seems to be working great!

You write an email to all people involved to let them know that you successfully published the new version of the website – pet yourself on the back and go to take a few hours of much deserved sleep. You have just fallen asleep when the phone rings and you get up again to take your client’s frantic call - customers are getting errors and not being able to complete their transactions – something is very wrong! Your nightmare scenario has come true! You jump into action again, quickly look at the error logs and try to determine if the cause is something you can fix quickly or whether you need to restore the old version of the database and application. Fortunately for you this time you discover that the culprit is a single Stored Procedure that had been modified but which somehow you missed. You change that SP and everything is fine – you are finally relieved, it could have been a lot worst!

The question is why anyone would go through this pain when they can use a tool like xSQL Object which in matter of minutes can identify all the database changes that were made and generate a safe SQL change script that will propagate those changes to the production server. It appears to be even more puzzling when considering that xSQL Object is completely free for SQL Server Express and it is also free for other editions of SQL Server as long as the database contains less than a certain number of objects. Even if you were in the small group of users (less than 10% of our user base) who work with larger databases and have to pay a couple hundred dollars xSQL Object more than pays for itself in just one time you use it. The answer unfortunately lies in our inability to reach out to all those potential users to let them know what they are missing. So, if you read through this please help spread the word – let all your friends and colleagues know that they can download this tool and use it to save many valuable hours of their time.

Free SQL compare tools - no pestering

Most of the software vendors claiming to provide free software do one or a combination of the following:

• load the app with annoying messages trying to push you to purchase the commercial version of their software;
• severely limit the functionality of the application and every time you click on let's say a button that presumably gives you access to some critical functionality you are invited to purchase the commercial version;
• implement trial periods after which you can't use the software anymore.

Are those helpful? Yes, absolutely, they allow the user to get a feel for the product before deciding to purchase but... saying that you are giving something for free to the customer is a bit misleading to say the least.
Enter xSQL Object (schema) and xSQL Data Compare (data) two of the best database comparison and synchronization tools in the market - when you download xSQL Bundle that includes those two awesome tools you get a truly free set of very helpful tools.

• no requirement to register or activate;
• no annoying reminders inviting you to purchase the higher version ;
• full functionality

So, what's the catch? Do we have a paid for version? There is no catch and yes we do have a paid for version that is no different from the free version with one important difference: only when one of the databases in the comparison is on a SQL Server edition higher than SQL Server Express AND there are more than certain number of objects in the database then you get a gentle reminder that this edition of xSQL Object / xSQL Data Compare does not support this database.
Judging from our statistics over 90% of our users fall into the free category that never needs to pay a dime for those tools and is never bothered by reminder messages.

RSS feed from SQL Server – cool

RSS Reporter is a very simple tool and that is the beauty of it. In addition of providing one of the best ways to monitor SQL Server jobs it provides something even cooler – it allows you to write any T-SQL query you want and with one click the results of your query are streamed in an elegant, standard RSS feed that you can subscribe to from any device, now that is beautiful!

No more wasting time updating your many bosses with the results of whatever ad-hoc queries they keep asking you to run for them, no more IT meetings to decide whether you need to take the time to implement a report that you think you will be asked to generate again and again. With RSS Reporter you write your query once and you send your bosses the link to the automatically generated RSS feed and they can get an update anytime they want, on whatever device they want without having to bug you ever again for that same query.

Is this cool tool expensive you ask? How does free sound to you? Yes, it is completely free for one SQL Server instance and only $99 for 5 SQL Server instances.

You can read more about it and download your free copy from: http://www.xsql.com/download/rss_reporter/

Instead of email alerts – monitoring SQL Server jobs

Anyone out there who has been tasked with monitoring SQL Server jobs running on multiple servers knows what a pain that is. First you have to spend hours going through the process of setting up email profiles for SQL Server Agents on each and every one of the servers to be monitored so that you can get email notifications if a job fails or some other condition occurs. Next, despite your effort and unquestionable diligence you have to come to terms with the fact that sometimes those email alerts may be delayed and some other times may never reach the intended destination so once in a while you must log in and check those server logs to make sure everything ran correctly (imagine having to check 20 or 30 logs) and then if you see something that did not run correctly you will need to use SSMS to navigate to the given job on the given server to see what happened.

So, what is a DBA to do? Well, fortunately the solution is very simple – it is called RSS Reporter for SQL Server (http://www.xsql.com/products/rss_reporter/) - it is free for one SQL Server instance and extremely affordable for more instances. You simply add each server you need to monitor into the RSS Reporter workspace, set the options to filter only the jobs and conditions you want and the RSS Reporter automatically generates an aggregate feed that gives you a complete view of all the jobs from all the servers and even better, it allows you to drill down on each job and view the details. You can subscribe to and read the feed from any device without having to worry that the alerts are not coming. You go on vacation – no worries – someone else can easily monitor the job status feed – nothing to setup or configure, the other user simply subscribes to the same feed you get. Now that’s beautiful!

Check it out for yourself and spread the word to the community – let’s not keep it a secret anymore.

xSQL Profiler - what sets it apart from the competition

We just released xSQL Profiler yesterday and thought we should answer the natural questions that come into every potential user's mind - "how is this different from what's already out there?" and "how can it help make my job easier?".

Before we get to "what sets xSQL Profiler apart" we need to first explain what it does so here is the brief list of what it allows you to do:

• register SQL Server instances and databases in the xSQL Profiler workspace;
• define highly granular events that you may want to trace (a predefined set comes with the product);
• define traces (choose the servers / databases you wish to trace; choose the events; set filters so that you can trace exactly what you want nothing more, nothing less);
• schedule traces - you can define as many start/stop intervals as you wish and set those intervals to occur only once or recur daily, weekly, monthly;
• view trace data using the xSQL Profiler predefined viewing panel that allows you to filter sort and group the events;
• query the central repository directly using T-SQL.

xSQL Profiler will automatically start and stop the traces in all the servers/databases you have selected and pull the trace data from each server periodically. In other words you set it and forget it.
Now, how is this different from the rest of the tools out there:

• you can deploy this monitoring and auditing platform in minutes days, weeks or months - there are no agents on the servers that will be traced - you simply install the xSQL Profiler on a workstation or server where the xSQL Profiler service will also be running and you are done with the installation.
• allows a high level of granularity in defining events / traces thus eliminating the unnecessary load placed on servers when you trace more than you need to.
• It is so simple even a "cave man could do it" - these geico commercials are getting to me :)
• IT IS ABSOLUTELY FREE (only for up to two SQL Server instances that is - what did you think, someone's got to pay for all that work we have done) <- -="" any="" couldn="" fine="" finer="" here="" i="" it="" li="" make="" print="" read="" t="" than="" the="" this="">

Lastly, let's get to the "how does this help me" question. Well, if you manage SQL Servers for living you don't really need an answer to this and if you don't manage SQL Servers for living then what are you doing reading this - have you got nothing better to do? Just kidding you are welcome to read this and tell everybody in the World how great it is (after you try it yourself of course). You can read more about this cool product here: http://www.xsql.com/products/sql_server_profiler/ and you can download your free copy from here: http://www.xsql.com/download/sql_server_profiler/

Would you do an exclusive advertising deal if you could?

This is a business ethics question that has been bothering me for a while so I decided to post it here and see what other people think. Let's say, hypothetically, we had the power to get into an exclusive deal with google whereby anytime any one in the World searches for database comparison and synchronization tools, xSQL Software (http://www.xsql.com/) and its products, xSQL Object and xSQL Data Compare, would be the only results that would come up - no other company would be allowed to place an ad for that type of search! The little greedy me says "that would be awesome" - we would quickly capture that niche market and literally crush the competition, and wait, it gets even "better", we don't even need to invest anymore (not much at least) on developing those products since no competition can possibly threaten us! The good me says "no - that won't be fair and even worst it would completely suck the fun out of developing those kind of tools".

Now, at this point, this is not a moral dilemma for us yet as we have a long way to go to get to the position where we may have enough power to get to such exclusive advertising deals - of course not with google but with small user group sites etc., but we have more than once experienced knocking on some doors and getting turned down with a "sorry, we can't advertise your products as they compete with the products of one of our special sponsors". Of course that does not feel good... but when reason overpowers those bad feelings we find ourselves asking the question "wouldn’t we do the same thing if we could?".

So the question to all of you is: if you had the power would you do it or not, and why yes or no?
Thanks for your comments!

xSQL Script Executor a cool and free tool for SQL Server

xSQL Script Executor which can be found here: http://www.xsql.com/products/script_executor/ is a very simple tool that allows the user to select a list of T-SQL scripts and folders containing T-SQL scripts and execute them in the desired order, with one click, against one or more databases.

The user can choose to wrap all the scripts in one big transaction, wrap them in separate transactions or execute them without wrapping them in transactions.

So how is xSQL Script Executor helpful for database administrators? Well, here is a simple scenario:

• you are the database administrator in charge of executing the scripts that individual developers and dbas from different units in your organization place in a repository daily.
• Each unit has its own “folder” where its developers and dbas place their scripts.
• Every night you are faced with this simple but daunting task – execute those scripts against one or multiple databases. What are your options:
  o Manually open those scripts one after the other and execute them and hope to be done by next morning; OR
  o Get xSQL Script Executor, spend a few minutes creating the list of folders/scripts to be executed, order them, automatically generate command line commands – one for each server/database the scripts need to be executed against, put those in a batch file, schedule the batch to run when you want it to run and you are done. There, that part of your job has been converted from many boring hours into… well, whatever you want it to be – you can still pretend you are busy running those scripts though.

Here is the best part of all – this tool is completely free, no strings attached, no activation, no registration nothing – simply download and enjoy.
Don’t forget to do your friends a favor – tell them about this life saving (well for some it is) tool – here is the link again: http://www.xsql.com/products/script_executor/.

Tight deadline killing the project

“Dear CEO,
Project x is in jeopardy! While you may have been led to believe that the project is near completion that is as far from the truth as it can get, not only is the project nowhere near completion but if things continue the way they are it will never be complete…”

I can only guess that hundreds of commanders on the ground in Iraq have in their mind probably written a similar letter addressed to the Commander In Chief when he declared “Mission Accomplished” at a time when the mission had just started. Tenths of engineers and workers at Boeing subcontractors have probably done the same as Boeing suffered one embarrassing delay after the other on its Dreamliner.

At one capacity or another we have all, especially those of us in the IT field, likely been actors in similar “plays” at some point in our careers and we know how those usually unfold. The question I wish to discuss is: what are the factors that create the conditions for such “doomed from the start” projects and how can an organization reduce or eliminate the likelihood of such conditions forming?

Before we get to the answer of that question let’s clarify something that has the potential to confuse the analysis / discussion. There are “do or die times” when a critical business need dictates what would normally be considered unrealistic deadline – in such cases everyone from the very top to the very bottom is aware of the situation – if the people involved are able to pull it off they will be considered, deservedly so, heroes – if they fail everyone understands. Such scenario is not subject of this article – what I wish to analyze and discuss here is unreasonably tight deadlines that are not unequivocally dictated by a true business need but are brought about by other factors.

Here are what I believe to be some of the main contributing factors:

• ignorant management that has no idea of the effort required coupled with an autocratic style (one may argue that ignorance of management often leads to autocracy);
• lack of trust on the commitment of the team coupled with lack of productivity measuring tools and techniques;
• overambitious and cowardly managers trying to please their bosses at any cost;
• failure of the management to recognize that team members are humans and have lives beyond work;
• insecure team members choosing to deal with failure later rather than facing the management now;
• inexperienced team ready to “bite” any size project oblivious of its “chewing” capability;

An occasional project failure may be an indication that one or more of those factors came into play when that project was defined and approved but the organizations self-correcting mechanisms that are derived from that organization’s culture are working properly. A frequent occurrence of project failure on the other hand would be an indication of a much deeper problem related to the culture of the organization in question which is hard to change.

How can an organization cultivate the right culture – the one that produces success rather than failure?

There are two ways to cultivate a certain culture amongst a social grouping:

• the “bible way” – that is, if you do or don’t do something you are going to hell / heaven; In this case the motivation of the individuals to do or not do something is their natural desire to secure a better future for their next life.
• The “reason way” – that is if you do or don’t do something those are the direct consequences and this is how those consequences ultimately affect you. As a reasoning being you are of course expected to choose to do / not do what benefits you the most or hurts you the least in this life.

For the purpose of this discussion I would classify the “if you do/don’t do this you will be fired” under the “bible way”.

While the “bible way” has been extremely effective in cultivating a culture where stealing, cheating, hurting others etc. are unacceptable I believe that it wouldn’t be very effective in cultivating the right culture in an organization. An organization must rely on the “reason way”. You could put a big banner in the main entrance of your headquarters building stating: “Autocracy is not the remedy for ignorance, education is!” but is that going to create a culture where autocracy is not acceptable – no, it is not going to accomplish anything. A long and persistent educational campaign dissecting the autocratic style and identifying the advantages and disadvantages of it and how it affects all involved is needed.

The same is true for any and all aspects of an organizations culture – slogans don’t produce culture, reason does.

This just scratches the surface of this deep and very interesting subject but it makes the point I wanted to make, namely that when a project fails the organizations culture should be scrutinized and furthermore it should be recognized that adjusting the organization’s culture can not be mandated – it can only be accomplished through a long and persistent educational process.

Feel free to leave your comments here – I can’t promise to respond but I will definitely read them all.

Update table without where clause!

What - an update table statement without the where clause? What are you talking about – such horrendous thing should never happen! Unfortunately, the reality is that sometimes it does happen. The first thought that comes to mind in those moments of panic after you have pressed that ever helpful ctrl+E shortcut and you realize that you either forgot to write the where clause to filter only the records that you really wanted to update or you simply didn’t select the complete statement before executing it, is to STOP the execution, but oh no, unfortunately for you the results window is displaying the message “1 million records were affected”!

Another thought flashes through your mind – how could Query Analyzer/Enterprise Manager / SSMS do this – shouldn’t I get a warning of the type "hey you are trying to update this table but I don't see a where clause. Are you sure you want to do it?"? Sure, you can blame the tool for your stupidity but that’s not going to help – the tool is just that, a tool and if it does what you tell it to do then it is really doing its job.

Then, you start beating yourself up: "why o why didn't I wrap this in a transaction" - after seeing that all rows were affected I could have simply rolled back and no harm would have been done. Well, you deserve the beating!

Are you completely doomed? Not necessarily, it is not time to run yet so pull yourself together and think this through. First if the table/columns you updated contain data of critical importance it is likely that those columns are being audited – so you can look for that audit table and use that to clean up the mess you just made. If no such thing is available then your next and likely last lifeline is the backup – restore the most recent full backup as a new database, apply the subsequent differential backups and the logs up to the last log backup before you made the mess - after you do that you can safely repair most of the damage. If for whatever reason you don’t have a backup either then you are officially doomed – just run for cover.

I emphasized here the most extreme case, that of a completely missing where clause but this applies equally to the more likely case of an incorrect “where clause” that filters the wrong set of rows. The difference is the scope (the number of rows affected) and the fact that while it is very likely that you will immediately realize what you did in the case of the missing where clause in the case of an incorrect where clause you may not even realize that you messed up until a future event uncovers the mess.

So, what’s the lesson here:

• if at all possible do not run ad-hoc update queries on a production database; A production database should only be updated through thoroughly tested interfaces that implement proper checking and warning mechanisms to avoid un-intentional changes to the database.
• if you must (that should be rare) then:
  o make a copy of what you are about to change – if you have to reverse the changes this copy will save you a lot of time and frustration;
  o always wrap your statement /script on a transaction and before you commit the transaction make sure to verify the number of rows being affected. You may also want to spot check some of the rows that were updated to ensure that what happened is what you intended to happen.
  o Insert detailed comments/explanation in the query/script you executed including the server and database against which you executed it and the exact data and time you executed it and save it.
• Always keep in mind that updating a production database is no joking matter so be safe, don’t take shortcuts!

My job is moving offshore!

Judging from the buzz, or lack of it, it appears that the rhetoric against offshoring has faded somewhat and especially the technology sector seems to have come to terms with the practice. However if one pays close attention it is not hard to notice that there still is quite a bit of resentment – so much so that a lot of businesses try to conceal from their customers the fact that they utilize offshore resources!

A shortsighted Lou Dobbs’ type analysis would conclude that offshoring is hurting the American people – high paying jobs are moving overseas at a fast pace and the Americans are getting the short end of the stick! The people who conduct such analysis are strong advocates of protectionism as the remedy – they want the government to intervene and close the gates to offshoring. While to many programmers and people from other professions who have lost their jobs to their offshore equivalents this may sound great, in the long term such policy would have devastating consequences.

Let’s rewind a few decades and draw a parallel (albeit not a perfect one) with the time when the government intervened to protect the once flourishing auto industry by restricting the imports of the foreign cars to the country. In the short term that was great for the American auto industry and consequently for the armada of people that the industry employed – our great auto companies continued to prosper un-challenged. That is exactly what set them up for the huge catastrophic failure that we have been “watching” unfold in the last few years. By shielding them from the competition in essence the government ensured that they would gradually turn into impaired, intellectually challenged, slow reacting, bureaucratic organizations incapable of competing in the open market. How would that have played if the auto industry was left to face the challenge on its own without government help? I am sure there would have been some “bruising” but the American ingenuity and entrepreneurial spirit would have certainly emerged victorious from the “battle” and we would all be now driving reliable, safe, comfortable, elegant, and highly efficient, American made cars.

Is it painful when the lower cost of the overseas resources puts downward pressure on the compensation of American employees or even worst when an American employee’s job moves offshore? Yes, it is very painful but it is a pain we have to endure for the sake of the long term survival. The government intervention would be like delaying an urgent and absolutely necessary surgery for a patient and giving him painkillers instead. At some point the painkillers loose their power and the patient is in great pain but unfortunately at that point it is too late for surgery…

It must be understood that this phase we are going through is not a common, normal phase – the World has moved somewhat abruptly from a World of multiple closed National Economies with some level of strictly controlled exchange between each other into an open market / global economy that is currently undergoing the greatest transition it has ever seen. It is like having some 200 separate bodies of water all at different levels and each of them connected to each of the other ones through a highly complex system of locks – if all of the sudden all the lock gates were open one can only imagine the kind of havoc that event would wreak, but eventually a new equilibrium state will be reached and things will be peaceful and normal once again although completely different. Until the water settles all we citizens of the World (not just we Americans) will have to learn to swim on those water currents.

Feel free to leave your comments here whether you agree or not and if you are a programmer or a database administrator and work with SQL Server please don’t forget to check out our great SQL Server tools here: http://www.xsql.com/ and read about our “pay if you can” licensing here: http://xsqlsoftware.blogspot.com/2008/04/unique-hassle-free-software-licensing.html.

Unique, hassle free software licensing - pay if you can

When we decided to implement this unique licensing model last year we were worried that we were giving the store away – after all we had spent tremendous amount of time and energy developing those tools and now we were considering giving them away!

Almost every software company out there gives away some version of their software but usually the give away version is either a watered down version with severely limited functionality or a version loaded with annoying messages trying to get the user to pay for the full version.

Our approach however was fundamentally different. First, we “discovered” that not everyone gains the same amount of benefit from our software – while for some it pays off with a single use for some others it may take multiple times to get a return. Second, while for some a couple of hundred dollars for a useful piece of software is nothing, for some others it is indeed a big deal and it so happens that, most likely, the ones who would really think twice before spending any money on software are the ones who whose benefit would be relatively smaller. So, we decided that anyone, individual or company, for whom a couple of hundred dollars are indeed a big deal should not have to pay at all – only if and when they would reach a certain threshold then they would have to pay.

The challenge then became how to determine who should and should not pay for our software. Well, while that may not generally be a trivial task in our case it wasn’t very hard since our products are tightly related to SQL Server and we know that:

1. if you are using the free SQL Server Express then there is a high likelihood that you have not yet made it in the big league and you are counting every penny;
2. if the databases you work with, regardless of what edition of SQL Server you may be using, have a small number of objects in them (tables, views, stored procedures etc) then not only is that generally an indication of a small business but the benefit you gain from utilizing our tools is directly correlated with the size and complexity of the databases you work with.

So then the model became clear and here is what we did and continue to do:

1. everyone downloads a fully functional version of our software from our website – no hassle, no registration, no reminders, no limitations.
2. after 2 weeks have passed the software checks the edition of the SQL Server and if it is SQL Server Express then it simply continues to work without hassling the user in any way shape of form; If it finds that you are trying to compare and synchronize a database that is on another edition of SQL Server then it performs another simple check – it looks at the number of objects you have on the database and if those are below a certain limits then again it does not bother the user at all - in fact the user does not notice anything the product simply continues to work.
3. Only if the 2 weeks have passed AND it is a SQL Server edition other than SQL Server Express AND the number of objects in the database exceeds certain limits THEN we kindly remind the user that this edition of our software does not support comparing those databases. At this point we have established two things – first that in this user’s case our software will pay off for itself right away (in fact it has likely paid off already during the first two weeks) and second that this user can certainly afford it:)

So how has this model worked for us? We can only say that we are very glad we did it this way – the “A friend told me” is the second most frequent answer (google being first) to our “Where did you learn about us” question – now that is telling!

A few words about the tools discussed above: xSQL Object allows programmers and database administrators that work with SQL Server to compare the structures (schemas) of two databases, see where the differences are, generate the change script that synchronizes the structures and execute the script. It supports SQL Server 2005, SQL Server 2000 and will soon support SQL Server 2008. All objects are supported – tables, views, stored procedures, user defined functions, users, permissions, roles, assemblies, service brokers, synonyms, assemblies etc. xSQL Data Compare provides for comparing and synchronizing the data in two SQL Server databases.

Extending the trial period of a software

I ran across this page where someone had explained different ways of extending the trial period of a software and also listed a bunch of software (likely malware) that presumably help you extend the trial period.

Extending the trial period of a product without permission is an immoral shameless act of stealing that does not merit any discussion but what caught my attention was one of this idiot’s assumed reasons as to why one would want to go through the trouble of engaging in such activity. Says he: “Or you may be decided that the shareware is not worth to pay…”! Truly astounding! In today’s World we are constantly trading – trading our time and energy for money, our money for goods and services we need etc., and we make hundreds of “is it worth decisions” but most of us understand that worthiness is relative and the decision we make in that regard simply determines whether we choose to engage in the trade or not but it does not, it can not, entitle us to anything.

xSQL Software to give $100K in software to non-profit organizations

We are giving away $100K in software to non-profit organizations around the World whose primary objective is to support or engage in activities of public interest. You can read the complete press release here: http://www.xsql.com/News/press18.aspx

If you work for such organization and would like to obtain a license please contact us.

xSQL Profiler released - enhanced SQL Server profiling

We are very excited to announce the beta release of xSQL Profiler, a significantly enhanced SQL Server profiling tool for monitoring multiple SQL Servers from a central location. You can use xSQL Profiler for performance, auditing and compliance related tasks. You can read more about xSQL Profiler on our website at: http://www.xsql.com/products/sql_server_profiler/ but here is a quick list of what this new tool brings:

• Trace multiple SQL Servers - no agents to install
• Use predefined events or define custom events
• Powerful filtering capability, precise tracing
• Schedule traces to run when and where you want
• Flexible reporting interface
• Support for SQL Server 2008/2005/2000

Download a copy of the xSQL Profiler from: http://www.xsql.com/download/sql_server_profiler/ and help us make this a great tool by telling us of any issues you may discover as well as any suggestions you may have - post your suggestions/issues at: http://www.xsql.com/suggestfeature.aspx

College degree better than experience...

Short of finding someone who has both the degree and the experience would you hire someone with a degree and no experience or someone with experience but no degree?

Is there a rational behind the equivalency of a degree with certain number of years of experience?

To make those questions more specific and relevant for this audience: would you rather hire someone with 5 years of programming experience but no degree or someone with a Computer Science degree but no programming experience? How many years of programming experience does it take to make up for a computer science degree?

Before attempting to answer such questions it is critical to identify and analyze the differences between a degree (the work to obtain one) and the real life experience.

The most fundamental difference between the two is the focus that each has. The real life experience focuses on HOW things work – years of experience will help one become a master of a given trade and remain a master for as long as that trade does not experience any radical changes. A college degree on the other hand focuses on WHY things work the way they work. The HOW part of a degree is generalized – how things should work but not necessarily how they work in a given implementation.

An analogy that makes the difference clearer would be that of the experienced mechanic versus the mechanical engineer. A mechanic that has spent 5 years working on fixing and maintaining BMWs is likely a master of the trade – he can diagnose and fix BMW problems very quickly and efficiently. A mechanical engineer on the other side may have no clue of the particular BMW implementation – he knows how cars in general are put together and why they are put together that way but not how to fix a BMW.

Now we can begin to answer the questions we posed at the beginning.

Which one would you hire? Clearly the answer is: it depends! If you need someone to handle a well defined task that does not require creativity but simply diligence and efficiency then an experienced person is clearly better. But, if you need someone that can easily adapt to different scenarios, if you need someone who can learn new technologies faster and consequently have lower probability of becoming obsolete despite the evolution of the trade in question, if creativity is important to the job then the degree has a clear edge over the experience.

Can one make up for a degree with number of years of experience? No, it is not possible! Please do not confuse this with the case when an individual dedicates an equivalent amount of time to studying the trade in question, in essence doing the work that is required to obtain a degree – I consider such person a degreed person regardless of whether a formal degree was obtained or not. When I say ‘No’ I am speaking strictly in terms of work experience. A mason can spend a lifetime laying bricks and never learn how to calculate the load on a load bearing wall or beam. Experience provides one with what I call “superficial knowledge” that lacks the foundation and can not be used to derive new knowledge – for example: you know that if you touch an electrical wire (household voltage) with a dry wooden pole you will not be electrocuted but you can not extrapolate from that the new knowledge that if you get on top of an wooden chair you can safely touch a single wire with your bare hands (remember, a single wire only – if you grab two you will be the light bulb!). A degree provides solid knowledge that can be used for generating new knowledge – experience teaches you that touching the wire with a dry wooden pole is safe whereas school teaches you what the electricity is and how it works. An electrical engineer does not need to be told about the dry wooden pole he can deduct that knowledge and a lot more from the foundational knowledge that he has obtained in school.

There, I have made my point.

Before you react to this please keep in mind that:

• I am comparing degree versus experience on a specific trade;
• A degree does not necessarily mean a formal degree – but rather that one has paid his dues in time and energy spent studying the trade;
• I am comparing two individuals with similar levels of intelligence/aptitude.

Feel free to leave your comments here.

xSQL Bundle receives highest marks and praise from SSWUG

Just realized that we never published a link to this review of our database comparison tools xSLQ Object and xSQL Data Compare. The review was conducted by Stephen Wynkoop, Microsoft SQL Server MVP and SSWUG founder who was duly impressed with our products and gave them the highest rating possible in all aspects of the review, overall score, installation, usage and real-World usefulness. You can read Stephen's review here:
http://www.xsqlsoftware.com/reviews/sswug_xsql_compare_review.pdf

User an idiot no more!

Not more than 10 years ago it was not unusual to hear programmers vent their frustration about the users who were all “idiots“, and see users sheepishly admit of having clicked the “wrong button”. During those 10 years things have changed dramatically in both sides – the users are increasingly savvy and their expectations have increased significantly while the programmers, no more shielded by the mystique of the profession, dread hearing a user proclaim “who is the moron that wrote this code!”. Nowadays it is common to see puzzled users tell their programmers “what do you mean that can’t be done…?”, nowadays there is no such thing as “the wrong button”, nowadays if something goes bad with the application it is not the user who is the idiot but rather the programmer is the inept moron.

Not trying to make any point here – just an observation on one of the aspects of the evolution of the profession. Do you agree with this observation?

SQL Server Resource Index

Here is a great page http://www.sqlserverindex.com with links to SQL Server resources: official sites; global user groups; learning resources; events; books; third party SQL Server tools, local user groups, and local SQL Server consultants on the major US metropolitan areas.

SQL Server Profiler – a lesson learned the hard way

Background: I was recently at the site of a client who was concerned that the CPU utilization had shot up in the last few months without any apparent reason. Since this was a node on an Active/Active cluster and was supposed to be the failover node for their more important node they had moved quickly and upgraded from a 64-bit dual processor to a 64-bit quad. The CPU utilization had dropped from over 70% to somewhere in the 30-35% range however the concern was still there – they wanted to find out what caused the trouble in the first place.

Trouble starts: After spending a bit of time understanding the environment, looking at the trends and documenting the current status I decided to start a trace on the effected node and save a few minutes of data on a SQL Server table. I was feeling pretty good about it until I clicked on “start” and the trace fired away. I looked at the CPU utilization and I noticed, to my utter surprise that it dropped significantly and it was staying down – I did not like that and stopped the trace immediately (it must have been running for about 65 seconds).

There, now I had another puzzle on my hand – was that a simple coincidence that as soon as I started the trace it so happened that the “demand” on the server dropped, maybe a great number of users just logged off and went to eat lunch or was it something else?! Soon after a very concerned user came buy and was telling the resident IT person in charge that this third party application had gone haywire – it had started assigning case numbers from the beginning of time creating duplicate entries in the system etc…

Now I am not going to go into how totally unacceptable it is for a professional software application to behave that way… regardless, it was my trace that somehow caused this trouble that ended up taking hours of manual labor to clean up.

So, now I am stuck, I am looking at all kinds of reports but without current trace data I can’t see what’s going on and after what happened I don’t dare start another trace.

Solution: After I ran out of alternative options I decided to give it another try – this time I was not going to run a trace from SQL Server Profiler on my remote client machine but instead a server side trace. Furthermore, instead of storing the trace data on SQL Server I directed it to file on another drive. I got the script ready and keeping my fingers crossed executed it while watching the CPU utilization – this time I was pleasantly surprised to see that nothing seemed to change, the trace was not affecting the CPU utilization at all – I also had someone monitoring that third party application to make sure that it wasn’t going haywire again – everything seemed good.

I let the trace run for a whole hour. I downloaded the trace files (a couple of gigs of data) and uploaded all the data in a SQL Server database on my local machine. Now things were a lot easier – it did not take more than a few minutes to identify the query that was causing all the trouble - a trigger from that third party had failed and a couple of tables had ballooned to hundreds of thousands of rows when they were supposed to have no more than something like 50 rows! Trigger was fixed and CPU utilization went down to 10% and it is staying at that level.

Lesson: Overall it was a great success but not without some bruising on the way. The lesson learned – never run a client side trace on a sensitive production system it can bring the server to its knees.

Related Info: In case you don’t have much experience and are wondering how to run a server side trace here is a quick guide:

• Start SQL Server Profiler on your local machine;
• Click on “New Trace” and connect to a non-sensitive server – if you have an instance of SQL Server on your local machine all the better – just connect to that one;
• Define your trace;
• Start the trace;
• Stop the trace;
• Go to File / Export / Script Trace Definition / For SQL Server [2005 / 2000];
• Open the just saved script and change the path/file where you want your real trace data to go. Also, make sure all the other parameters are what you want them to be.
• When you are ready to start the trace on the “real” server simply execute the script against the target server.
• Note down the trace id so that you can stop and delete it when you are done.

Lastly, check out our products a lot of them are free - see the left panel here.

xSQL Documenter released

We are very excited to announce a new addition to our suite of cool SQL Server tools, xSQL Documenter a professional grade tool for documenting your databases.You can read more about xSQL Documenter on our website at: http://www.xsql.com/products/database_documenter/ but here is a quick list of what this new tool brings:

• Supports all objects in all major dbms-es including SQL Server, Oracle, DB2, MySQL
• Generates HTML and compiled CHM output
• Generates dependency and primary/foreign key graphs
• Shows DDL/XMLA code for all documented objects
• You can run it from the command line
• You can easily brand the documentation

You can download an evaluation copy of the xSQL Documenter from: http://www.xsql.com/download/database_documenter/ Please do post your comments and suggestions regarding this products or any of our other products at: http://www.xsql.com/suggestfeature.aspx.