Programmatically receiving profiler events (in real time) from SQL Server 2005

Check out the SQL Programmability & API Development Team Blog for an excellent post about capturing profile events programmatically.

SQL Server 2005 SP1 Cumulative Hotfix

Service Pack 1 has just been released and here is a cumulative hotfix for things that didn't make it to the Service Pack.

One of the most interesting ones is probably this, it sounds so familiar.

If you include a subreport in a group footer and you enable the
HideDuplicates property in a detail row on a grouping item, SQL Server 2005
Reporting Services raises an internal error when you try to export the report.
The error also occurs when you click Print Preview on the Preview tab in Report
Designer.

Be sure to read the important notes!

• SQL Server 2005 hotfixes are now multilanguage. There is only one cumulative hotfix package for all languages.
• You must install each component package for your operating system.
• You must enable the SQL Server Management Object (SMO) and SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before you install the hotfix package. For more information about the SMO/DMO XPs option, see SQL Server 2005 Books Online.Note SQL Server 2005 Books Online notes that the default setting of these stored procedures is 0 (OFF). However, this value is incorrect. By default, the setting is 1 (ON).
• You must install all component packages in the order in which they are listed in this article. If you do not install the component packages in the correct order, you may receive an error message.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
  919224 FIX: You may receive an error message when you install the cumulative hotfix package (build 2153) for SQL Server 2005

From CHAR to VARCHAR

Last week we found a Reporting Services graph which all of the sudden was showing double entries. Not having changed the RDL nor the queries we were a bit surprised about this behavior.

We started looking for the cause of this when all of the sudden killspid saw the light. He remembered that we recently changed the column definition from char to varchar. Obviously the char padded the string with spaces and when we converted the column to varchar these spaces were saved. The string "XXX " is most certainly different from "XXX".

A simple UPDATE tblTable SET myField = RTRIM(myField) solved our problem.

It doesn't always have to be rocket science now does it? :-)

To BLOB or not to BLOB

If you are into freaky stuff you should check out http://research.microsoft.com once in a while. The guys there are really really really smart :-)

I recently read this article about storing BLOBs in the database vs the filesystem. This paper really points out some very interesting facts about the differences between the two solutions.

A must read if you are into BLOB's!

Xcopy deployment of databases using SQL Server Express

For one of our new projects we are trying to figure out the easiest way to deploy local databases that will be used by SQL Server Express.

We have several options (there may be others but these are under consideration):

1. Script the whole thing (including data)
2. Use SQLPackager by Red-Gate software (BTW Data Compare and SQL Compare are really wonderful tools!)
3. Xcopy deployment

I prefer the xcopy deployment as this saves us a lot of trouble. Why is it so easy? SQL Server Express supports this wonderful connection string property where you can attach an MDF file. This is a really powerful feature that gives you a lot of flexibility. Do notice that the SQL Native Client is required to support this option.

Server=.\SQLExpress;AttachDbFilename=c:\Data\myDB.mdf;
Database=myDBName;Trusted_Connection=Yes;

Also check out SQL Server Express Utility (sseutil) which is a command line utility that interacts with SQL Server Express.

*EDIT*
Want to know more about xcopy deployment? Check out this link I just found :-( Always check the books online st*pid :-)

KILL @@SPID

Ah, don't we DBA's all love this statement?
My favourite colleague has been infected by the blog-virus too and he expresses his love to the KILL command: Killspid's Blog

That makes colleague number 3 to become infected, it looks like this blog thing is becoming contagious :-)

SSIS: The service did not respond to the start or control request

Apparently there are some problems with Service Pack 1 and SSIS. Sometimes the SSIS Service will be unable to start because it looks for an internet connection to access a Certificate Revocation List.

Lucky us, there is a solution... check out this post.

SQL Server 2005 Books Online (April 2006)

Another update of the books online here to go with Service Pack 1.

On a side note, Internet Explorer 7 Beta 2 has been released too.
Also find the readiness kit here.

Deferred constraint checking?

Greg Low has a nice suggestion imho.
Basically the idea is the following (from ADO.NET):

a) start a transaction
b) send all the updates in any order
c) commit the transaction (and check the RI constraints at this point)

Read his post and vote if you think it is a good idea.

SQL Server 2005 Service Pack 1

Ladies and gentlemen... it has arrived!
Download

Internal storage of the DateTime datatype

There are many different opinions on how the DateTime value is stored internally in SQL Server. Some people say that it is converted to UTC, some think that some other magic stuff happens but none of this is true.

When you look at the books online this is the explanation:

"Values with the datetime data type are stored internally by the Microsoft
SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store
the number of days before or after the base date: January 1, 1900. The base date
is the system reference date. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight."

It may be hard to believe but this is exactly what SQL Server does. You send him a date and time and he calculates the number of days since 1900-01-01 and the number of ticks since 00:00 for that date. SQL Server does NOT care about timezone and daylight savings time. If you really need to take these into account I suggest you save the offset between the UTC date and the local date and the UTC date. I give preference to take both the times from the client so you don't create problems because of time differences between the server and the client (unless you can guarantee in-sync clients).

Here is a little script that demonstrates the internal storage:

DECLARE @theDate datetime
SET @theDate = '2006-04-14 14:00'

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 1, 4) AS int)

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 5, 4) AS int)
SELECT CONVERT(char(10),DATEADD(d, 38819, '1900-01-01'), 120) AS 'theDate'
SELECT CONVERT(char(10), DATEADD(s, (15120000 / 300), '00:00'), 108) AS 'theTime'

SSIS Performance Whitepaper

I found this great whitepaper on SSIS performance thanks to Jamie Thomson.
This is a must read if you want high performance SSIS Packages.

Best Practices Analyzer for SQL Server 2005

Some good news on the SQL Server Relational Engine Manageability Team Blog. They have decided to start developing a Best Practices Analyzer for SQL Server 2005.

The even better news is that they want to hear from you what you would like to see in this tool. So visit their blog and post your comments!

SQL Server 2005 Service Pack 1

According to Euan Garden's blog Service Pack 1 for SQL Server 2005 will be released this month!

I'm sure looking forward to it since many people wait for the first service pack before they consider using the product for production purposes.

SQL Server 2005 Upgrade Handbook

Check out this article on Technet giving you some insights in the upgrade process to SQL Server 2005.

Blank message box when starting SQL Server Management Studio

A couple of my colleagues were having a problem when starting Management Studio. Everytime the application started it would show a blank messagebox with just an OK button. Apparently this can be caused by installing the .NET Framework 1.1 (or one of it's updates) after the .NET Framework 2.0.









The SQL Server 2005 readme has the solution (although the problem description is not exactly the same - it seems to help in this case too):

1. In Control Panel, open Add or Remove Programs.
2. Click Microsoft .NET Framework 2.0.
3. Click Change/Remove.
4. Click Repair, and then click Next.
5. When the repair is completed, restart the computer if you are prompted to do this

Random record CTE type thing

One of the developers asked me if it was possible to repeat a single query multiple times. He was trying to generate test data for stress testing and wanted to repeat a simple SELECT ID, NewID(). He told me Oracle could do it so I obviously had to prove that SQL Server was able to do this too :-)

Here is the solution I came up with, it's a little fun with CTEs and the RowNumber function.

WITH RepeatCTE (RowNumber, LogID)
AS
( SELECT ROW_NUMBER() OVER(ORDER BY newID()) as RowNumber, newID() LogID UNION ALL
SELECT RowNumber + 1 as RowNumber, newID() LogID FROM RepeatCTE
)
SELECT TOP 400 * FROM RepeatCTE OPTION (MAXRECURSION 0);

An unexpected error occurred in Report Processing. (rsUnexpectedError) - Hotfix pending

For those of you who are having trouble with this. I have received the confirmation that the hotfix request has been accepted by the developers and a hotfix is on the way. Because we are planning to migrate to SQL Server 2005 we have chosen to request the hotfix for Reporting Services 2005.

As soon as I receive information about the hotfix I will let you know.

*EDIT*
I have received the hotfix and will probably be testing it next week. The hotfix only solves the Report Manager side of the problem since the Visual Studio side of it has to be fixed there. There is no hotfix planned for Visual Studio 2005 but "it will be fixed in the future" was the answer to my question.

Jolt winner - Database Engines and data tools

And the winner is... click
Can you guess without clicking? :-D

One big lookup table vs many small lookup tables

Recently I was reading SQL Magazine when I came across an article "Designing for performance: lookup tables" (instadoc: 48811 - Michelle A Poolet). The question was whether or not one big lookup table was better than many small ones.

While I do agree that one big lookup table complicates things in terms of integrity and data management there are a couple of arguments I disagree with.

Locking and blocking increases

The nature of a lookup table is read-only and given this I would suspect that only shared locks are being taken on the lookup table. So how would you experience locking and blocking in such a scenario?

You create a hotspot on the disk

I don't see why you would have hotspots on a read-only lookup table. Being a hot table means that all the pages of that table would probably be in your data cache - meaning less disk I/O once the pages have been cached. You can even force the table to stay in memory by pinning it, but I don't think many people actually use this technique since SQL Server handles data caching quite well.

I think like any database question the only answer here is: "it all depends". We have many domain specific lookup tables but we also have a big lookup table with over 300 different types. In a database with 400 tables I wouldn't want to create 300 additional tables for simple lookups where most of them would contain 3 to 4 records.