I recently tried to install SQL Server 2008 Express Edition, only to receive a nice error message: I found this rather strange, as I already have VS2008 SP1 installed: I found a few posts where other people were having the same problem, but most fixes usually involved uninstalling Visual Studio first and I really didn’t want to go through this. Other workarounds suggested it would work if you unticked SSMS, but this wasn’t something I wanted either!

One of my Windows Server 2003 R2 boxes started showing CPU bumps of 0 to 10% late last night. In investigating this a process cidaemon.exe was found to be the process causing the small but consistent utilization. This process is associated with full text indexing on Windows. Even if you don’t use full text indexing, Windows does. I found 3 catalogs setup on the server. One of the defaults being the system volume. Repeated attempts to configure full text indexing and fix the wild CPU usage all failed. The only way I was able to fix this problem was the delete the catalogs and recreate them. After that full text indexing went back to normal processing and cidaemon.exe was sent to the bottom of the processes list in task manager.

SQL Server 2008, Service Pack 1 has been released. download it here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19 *** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

MS SQL Server 2000 has a limitation of 8000 characters in a varchar variable. The historical reason for this limitation, I believe, is related to the 8k size of data pages, where in-row data can’t exceed something like 8060 bytes (actual numbers vary a little). But what if you need to work with data that is longer than 8000 characters? When storing data in a table, you can use the text datatype which is stored out-of-row (though there are options about storing strings shorter than 8k characters in-row and then moving them out-of-row if they grow). But you can’t use the text datatype as a variable. Look:

I thought I would share a recent question that was raised on LinkedIn, which asked: Starting the Holy Wars on ESB? Who brings the best package deal to the table i.e IBM, MSFT…… Look at this way " A mid sized cash crunched has to implement ESB what are the options it has" or rather who should it be married to. Any thoughts Tons of ESB from various players are in the market IBM, MSFT, Oracle , SAP but remember its a cash crunched company which requires the ESB. I can provide more details on the technical side. The idea is to throw open the debate to get various perspective.

While untangling a pretty nasty correlated subquery someone had written at work that was going three layers deep with IN (select X from Y) type of stuff, I used twitter to vent some of my rage SQL Training Wheels. This found its way to facebook, where one of my friends (and fellow LessThanDot members) asked “How So”. Well, the answer is simple. Like training wheels, they are easy. Damn easy. But, also like training wheels, they can really get in the way. As such, use of IN is often good for quick ad-hoc queries, but for anything to be run more than once it’s probably worth at least exploring the option of using a derived table and a join.

Time for another episode of the SQL Friday, The Best SQL Server Links Of The Past Week show. I skipped last week because of some mysterious reason. Here is what I found interesting this past week in SQL Land: The death of datetime? Bart Duncan explains why you should almost always use DateTimeOffset instead of Datetime Who is Active? v8.40 – Now With Delta Power! Adam Machanic has updated his Who is Active with a huge number of changes, fixes, and enhancements

It is good practice to show the user a message if your report does not return any data. There are a few methods to do this and here is a simple one that can be added to most existing reports that are subscription based. Create a blank report. Add a dataset that returns some data. You can use the following query to create your example… select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 union all select 1 col1,2 col2,3 col3,4 col4,5 col5 Of course you need a data source to create a report so just create one to a test system or local instance.

Have you ever needed to remove the time part of a date variable, or remove the date part of a date variable? I recently had a requirement to do this, and my first reaction was to use the format function because it is very flexible and quite simple to use. Unfortunately, it does’t perform very well. First, the format method for removing the time part. Debug.Print Format(Now, "Short Date") 4/1/2009 Debug.Print Format(Now, "Short Time") 09:16 Seems simple enough to remove the date and/or time portion of a date variable, but is it the best way? If you only want to display it, it probably is, but if you need to use the values for calculations, it is not the best way to remove the date or time portions.

You may notice that now in SSRS 2008 you are missing a critical reporting table named ExecutionLog. Well, you really aren’t missing it. This table was the bread and butter of trending for administrators. If you weren’t using it to gauge your reporting services activity then you should. A simple query like select Count(*) cnt,[Name] ReportName from executionlog a join catalog b on a.reportid = b.itemid Where convert(varchar(10),timeend,101) >= @st And convert(varchar(10),timeend,101) <= @end And [Status] = 'rsSuccess' group by reportid,[Name] order by [name] Could buy you a new reporting server. Don’t forget we need baseline proof to justify upgrades. This could even push version upgrades.