UPDATE - 01/29/2009 - I just received a solution to this issue, that does not require a temp table, which was my wimpy solution, thanks to Shawn who left this great solution in the comments:

Select * 
    From #checkDates
    where convert(datetime,case isdate(rptDate) when 1 then rptDate else null end)
Between
        '02/27/2009' and '02/28/2009'

The isdate() function does not work properly (or at least as I expect it would) when it is used inside a derived table or a Common Table Expression.  This is really frustruating as it changes the way I think of derived tables and CTE's.  I always assumed that the inner query or CTE was resolved first, but if you follow the TSQL below, you can see that is not the case.

This may affect other functions, isnumeric() or even custom functions, so test them carefully.

I was working on a report issue that was generating an invalid derived date from a varchar, 02/29/2009.  Very simple to filter out, but due to the way the where clause is resolved / short circuited in tsql, it was going to be necessary to use a derived table or a CTE, but neither would work.  I ended up resorting to using an additional temporary table (wimpy, wimpy), I don't like this solution and would like to find one that does not require the temporary table.

Here is the problem:

create table #checkDates
    (chkDtId int identity(1,1) primary key,
        rptDate varchar(10))

Insert into #checkDates (rptDate) values ('02/26/2009')
Insert into #checkDates (rptDate) values ('02/27/2009')
Insert into #checkDates (rptDate) values ('02/28/2009')
Insert into #checkDates (rptDate) values ('02/29/2009')

-- This fails below with invalid date
Select * 
    From #checkDates
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- put the isdate function as part of where clause
-- select statement still fails
Select * 
    From #checkDates
    where isdate(rptDate) = 1 and
        convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- switch to a derived table, the thought being to
-- remove the invalid dates from the inner select 
-- somehow in my mind i think the inner query should be resolved first,
-- but this is not the case !
Select * from 
    ( Select * 
    From #checkDates
    where isdate(rptDate) = 1 ) x
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

-- wimpy solution is to select the "good" records into 
-- a temp table and then perform the next select, yuck
-- BUT IT WORKS
Select * into #InterimResults 
    From #checkDates
    where isdate(rptDate) = 1 

Select * from #InterimResults
    where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'
--
-- CTE SOLUTION, DOES NOT WORK EITHER 
--
WITH goodDate_CTE AS (
    Select * 
    From #checkDates
    where isdate(rptDate) = 1 
)
SELECT *
FROM goodDate_CTE
where convert(datetime,rptDate) Between
        '02/27/2009' and '02/28/2009'

Mountpoints are fun and easily can solve issues with more than 26 drive letters (don't ask), but recently we ran into several issues with monitoring the mountpoints.  In particular we have a report that is based on past growth history of a database, disk size and disk free space, and it estimates when a drive will be at 80% capacity and when it will fill up.   With 100's of database servers this report can prioritize and pro-actively identify which server will encounter a problem next and when.  Of course the limitation is in bold, drive. 

Report Example:

When we began using Mountpoints the report was not as accurate and it needed to be adjusted.  We have some internal services that collect the drive size and free space to a central DBA database.  Review of this monitoring reveals it is using a WMI Query, a quick review of the WMI SDK shows another call that will pick up mountpoints, Select * from WIN32_Volume.  Life is good.

Not so quick after hurdling from drives to drives and mountpoints, a problem was revealed where the WMI call failed on two servers.  Englightenment.  These two sql servers have also been giving us odd issues with SQL Management Studio (SSMS), which is highly dependent on WMI, SMO, .NET and probably some other stuff.  Fixing WMI on these two servers fixed the issue and corrected the SSMS issues.

Steps to fix WMI (Thanks to our Windows Team for the steps below):

Here is a short quick Visual Basic Script (VBS) you can save to a text file with a .VBS Extension to see the call to WMI to check disk space for mount points or drives.  It filters out certain mount points for Polyserve as we don't want to monitor those, also for some reason z:\ is mapped in our environment and this wmi script pulls that with nulls, so you need to test for those.  You can also use Powershell, but it is using a wmi call underneath the hood as well, and we have yet to install powershell on all our servers.

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//BCCMAPP02")_
    .ExecQuery("Select * from Win32_Volume")
For Each objItem In DiskSet
    Ignore = False
    if len(objitem.name) >= 51 then
        If UCase(Left(objitem.name,51)) = _
            UCase("C:\Program Files\PolyServe\MatrixServer\conf\mounts") Then
            Ignore = True
        End If
    End If

    If Ignore = False Then
        msgbox(objItem.Name & vbCrLf & "Percent Free = " & _
            round((objItem.freespace/objItem.Capacity)*100,2) & _
            " = " & objItem.freeSpace & " = " & objItem.Capacity)
    End If

Next

This is a custom report developed to list user permissions.

Often DBA's need a consolidated list of permissions for a user, this custom report does this.  Developed by Tom Reeves.

User_Permissions.txt - Right click and save with a RDL Extension. (37.25 KB)

Altering a table column from varchar to datetime is pretty straight-forward in the SQL Server Management Studio (SSMS), until you look at the tsql generated.  For many operations SSMS will generate a tsql script that will:

• create a temporary table
• drop all the foreign keys
• copy the data to the temporary table
• create the new table with the correct data type
• copy the data to the new table
• drop the temporary table
• add the foreign keys back

That is a lot of operations and on a really large table of millions of rows may take a very long time to complete.

SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.

SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.

In this particular case we were modifying a varchar(10) to a datetime.  All the data was in a valid format.  The easiest method is to alter the column, another method to complete this is to:

• rename the existing column (tmp_varchar etc)
• add a new column with the correct name
• update the new column (in batches if necessary)
• drop the original column (now with a tmp_name)

This is very quick, much safer operation and is much "nicer" to the database log file.  Example of tsql is below:

CREATE TABLE dbo.testing
(test_id bigint NOT NULL IDENTITY (1, 1) primary key,
some_dt varchar(10)
)
GO

insert into testing (some_dt) values ('2009-01-01')
insert into testing (some_dt) values ('2009-01-02')
insert into testing (some_dt) values ('2009-01-03')
insert into testing (some_dt) values ('2009-01-04')
go

EXECUTE sp_rename 'dbo.testing.some_dt', 
    'Tmp_some_dt', 'COLUMN' 
GO

Alter Table testing
Add some_dt DateTime Default('1900-01-01') NOT NULL
GO

Update testing
    Set some_dt = Convert(DateTime,Tmp_some_dt)
GO

Alter Table testing
    DROP Column Tmp_some_dt
GO

select * from testing
go

--drop table testing

The server could not load DCOM. [SQLSTATE 42000] (Error 7404).  The step failed.

Began receiving this error from failed sql server jobs on a sql server 2000 box and a windows 2000 box, after a reboot.

We didn't spend too much time researching the issue, as we were still in a maintenance window, so we performed another reboot and things worked fine.

I've seen many dcom errors over the years, but not this particular one from SQL Server, most likely com+ or ms dtc sub-systems (services) did not start or failed to start on the reboot.  There were no system or event log entries that were immediately helpful, though there were other odd entries that seemed related to dcom not working:

I twisted my head around a select statement that wasn't returning what I wanted.  Eventually I focused in on the column list, that had column names with spaces, so it required using brackets [].  These brackets when mixed in the column list can lead to tsql parsing that is not expected.  In this case I mixed some columns with brackets and some without, ultimately leaving a comma off.

You'd expect the tsql to not compile, but it does and runs with no errors.  Once tsql encounters a column list with brackets, it parses based off the brackets and commas ignoring the additional field i wanted to include in the select list..

grrrr...

create table #testTable
    (tableid int identity(1,1) primary key,
        [login name] varchar(20),
        [is_password_validation] int,
        [is_password_expiration] int,
        [login type] varchar(20))

Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 1',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 2',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 3',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 4',1,1,'SQL')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 5',0,1,'SQL')

select * from #testTable -- good

select [login name],
    is_password_validation -- NOTICE THE MISSING COMMA
    [login type]
    From #testTable

Recently ran into an issue with a linked server between sql server and an as400, using the iSeries access driver via ODBC. 

The linked server was not returning all the rows and it was not throwing any errors either.

There is an advanced settings option on the iSeries as400 setup that needs to be "unchecked", by default the option is checked and can lead to missing records.

Once it is unchecked, the query returns the correct counts or rows.

Just a quick placeholder on the progress of SQL Server 2005 service pack 3.

This update is important, if nothing else to ensure your on the latest patch which addresses some security vulnerabilities.

There is also a cumulative update for service pack 3, generally I don't go down the cumulative update path unless i've been advise by Microsoft (ie, it addresses a problem being experienced) or the SQL Community in general highly recommends a specific CU.

I always start with patching my local developer edition, x86 Instance.

Slowly working through a few boxes, so far we've patched the following with no issues:

• 64 Bit named instances running under Polyserve 3.6.1 - both dev, qa and production, no issues, Enterprise Edition.
• 64 Bit named instance stand-alone server, Enterprise Edition.
• 64 Bit named instance stand-alone server, Standard Edition.
• 64 Bit Clustered SQL Server, Active / Active Configuration, no issues, did require reboot of both nodes and fail over, Enterprise Edition.
• 32 Bit SSIS stand alone installations (no dbms) no issues.
• 32 Bit Reporting Services Web Farm, authentication issues afterwards.

I had an issue with the Reporting Services Web Farm, which uses SQL Authentication for connectivity, caused the login to be disabled.  I had quite a difficult time getting it re-enabled and changing the password, but no errors in applying the service pack.

Where did the add remove programs go in windows 2008 ?

Of course, it was renamed to "Programs and Features", still under control panel.

Also from Start --> Run --> appwiz.cpl

Aaron James becomes the first to make the Emergency Room visit to get stiches.

Pushing a bike or running and trying to jump over a bike is how the story goes.

Oddly he seems to have actually enjoyed the experience!