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!