Smalldatetime error

If you see this error:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value

it means you have bad date to convert from string to smalldatetime data type in database. It usually occurs when you have a web form that submitted some data to save into database and one of the data is a date string.

For some reason, people will type the date wrong such as 1/1/2008 becomes 1/1/2998. But the smalldatetime data type can only hold a date smaller than June 6 2079.

For ASP.NET, you can add this to your customized validation:

Private Sub ValidateStartDate(ByVal sender As Object, ByVal args As ServerValidateEventArgs)
Dim IsValid As Boolean = False
Dim strSalesDate As String
strSalesDate = Clean(Me.txtAdStart.Text)
If IsDate(strSalesDate) Then
IsValid = True
Dim intYear As Integer
intYear = Year(CDate(strSalesDate))
If intYear > 2075 Then
IsValid = False
End If
End If
args.IsValid = IsValid
End Sub

Can't edit DTS package email notifications

After installed SQL Server 2005 Management Studio, my SQL2000 Enterprise Manager encountered an error while modifying the email notification component in DTS packages.

The error message is like this:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: Cannot load MAPI interface layer for DTS. Please make sure that semmap90.dll is installed.

Execution cannot continue as the language dependent resources file ...\1033\semmap90.dll could not be loaded.


The error came up because after you installed SQL2005 management studio, the windows registry has updated to newer versions.

The fix is quite simple. If you installed the SQL2000 Desktop Engine in default location then it can be done in this folder:
c:\Program Files\Microsoft SQL Server\80\Tools\BINN\Resources\1033\

(You can copy and paste this link into your PC's Start->Run box and hit "Enter".)

In this folder, copy the semmap.rll and paste in the same folder, then rename it to semmap90.rll. That's all you need to do.

Note: this is RLL file, not DLL.

T-SQL: Use “Having” or “Where”

When writing T-SQL query in SQL Server, one has two options to limit the returned data. You can either use “HAVING” or “WHERE”. The question is when and how to use them?

For example:
Query1

Select tblEmployees.EmployeeID, SUM( tblEmployees.intHourThisWeek) as TotalHoursWorked
FROM tblEmployees
WHERE tblEmployees.Active=True
AND tblEmployees.DateofWork < '1/1/2008'
AND tblEmployees.DateofWork >= '1/1/2007'
GROUP BY tblEmployees.EmployeeID

and query2

Select tblEmployees.EmployeeID, SUM( tblEmployees.intHourThisWeek) as TotalHoursWorked
FROM tblEmployees
WHERE Active=True
GROUP BY tblEmployees.EmployeeID
HAVING tblEmployees.DateofWork < '1/1/2008'
AND tblEmployees.DateofWork >= '1/1/2007'

will return the same result. It summarize the total hours of each employee worked in year 2007.

But the difference is that the “WHERE” search condition is applied before the grouping operation occurs and the “HAVING” search condition is applied after the grouping operation occurs.

So if the search condition is limiting the search result into a small subset of the original table(s), it is better to apply the search condition before the grouping operation because this let the database engine to calculate the result set on a smaller subset of data.

Applying this principle to some of the queries while I am fine-tuning the database performance, it is easy to see a 60% to 75% CPU time improvement.

But you can't always move HAVING clause into WHERE clause. Because only HAVING can contain aggregate functions. HAVING clauses can reference any of the items that appear in the select list.

For example:
One company has a promotion that any salesperson who sold more than five refrigerators of one new model in the promotion date period, will qualify for a special bonus.

SELECT intSalespersonID, sum(intUnits) as totalSold
FROM tblSales
WHERE txtProduct = 'Refrigerator ABC'
--AND dateSold between @Date1 and @Date2
GROUP BY txtProduct, intSalespersonID
HAVING sum(intUnits) > 5

If you tried to move the HAVING clause to WHERE clause, you will see this error:
“An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.”