ADO data type validation is inconsistant with SS2K's

[Bob]

I have a sql command with a SearchDate parameter of type date. When I
execute its stored procedure from Query Analyzer (to SS2K) with a date out
of the valid datetime range,

EXEC [SomeSP] @SearchDate = '11/1/0200 12:00:00 AM'

I get no error because the stored procedure properly handles out of range
dates by ignoring them.

If I send the sql command on its merry way properly paramaterized in .Net,
on the other hand, I get this error whether or not my stored procedure can
handle the out of range date:

"System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be
between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

Either a client-side ADO method is disagreeing with server-side data
validation, showing unneeded redundancy, or QA and ADO are using drivers
with differing data validation methods, causing inconsistant behavior.

Which is it? Either way reveals a poor design decision.

Bob

 

[Mary Chipman]

What is happening is actually a good thing, performance-wise, and is
working as designed and is actually a very sound design decision. Your
client code is raising the exception because you've strongly typed
your parameter as SqlDateTime, which has the date range mentioned in
the error message. If it didn't work the way it does, bad data would
get passed to the server where the server would have to raise an
error, wasting a round trip for nothing. If you want all bad data to
be handled by your stored procedure, then type the stored procedure
parameter as varchar and pass it a string.

--Mary

 

[Bob]

But the problem is that it isn't bad data because no exception is raised
server side. Sure, it might have been a mistake in the DB engine design to
allow out-of-range dates through in the first place - regardless,
normalization of logic is always preferable to performance. Performance
always gets better, while demoralization often leads to inconsistency. If
they wanted to have data validated client-side for better performance, they
should have duplicated the server's behavior exactly, used the same DLL if
possible.

All ranting aside, now that I know it's client-side validation I guess I can
just leave out the paramater if it's not valid. There's apparently no
directly supported convertion between DateTime and SqlDateTime, will the
function below always work?

'Imports System.Data.SqlTypes
Private Function IsValidSQLDate(ByVal d As DateTime) As Boolean
Return CDate(SqlDateTime.MinValue.ToString) <= d _
AndAlso CDate(SqlDateTime.MaxValue.ToString) >= d
End Function

Bob

 

[Mary Chipman]

What you need to understand is that behavior that is considered to be
a fatal error or exception on the client and needs to be explicitly
handled is not necessarily considered to be a fatal error on the
server. For example, if a statement that modifies data in a batch
fails on the server, the server simply moves on to the next statement
unless you explicitly catch @@error and @@rowcount and do something
about it. @@error is automatically reset to 0 on execution of the next
statement, and a non-zero value does not cause execution to halt as an
equivalent non-zero error code value in client code would. If your
stored procedure code does not validate input parameters, and you try
to write an out-of-range value to a datetime column, the update will
fail, preserving data integrity. Whether or not you catch and see the
failure is up to you the stored procedure programmer -- the engine
isn't going to commit bad data to the table. That's the fundamental
difference between client and server code execution, and it was
designed that way for a reason. SQL Server always guarantees the ACID
test -- atomicity, consistency, integrity and durability, but if it
halted execution with a fatal error each time a statement in a batch
updating a million-row table failed with an unhandled error,
processing on the server would grind to a halt, no? So that in a
nutshell is why you need to handle as many possible errors in your
client code as possible, and send only valid data to the server.

As far as CLR types and SqlTypes, SqlTypes are intended to map
precisely to SQLS data types, and CLR types are more general and need
to support multiple platforms. SqlDateTime has a more limited range of
January 1, 1753 through December 31, 9999 whereas the CLR DateTime has
a range of January 1, 0001 December 31, 9999 A.D. So if your DateTime
value is in the acceptable range it can be successfully converted to a
SqlDateTime.

--Mary

 

[Bob]

What you need to understand is that behavior that is considered to be
a fatal error or exception on the client and needs to be explicitly

value is in the acceptable range it can be successfully converted to a
SqlDateTime.

I appreciate you taking the time to write such a useful and detailed
response, deeper understanding is often hard to come by without a teacher of
sorts.

Thanks,
Bob

 

[Mary Chipman]

I appreciate you taking the time to write such a useful and detailed
response, deeper understanding is often hard to come by without a teacher of
sorts.

Thanks,
Bob