Access 97, Linked Servers, and SQL
-
I would like to use an Access 97 table in a linked server to update a table in my SQL database.
We are doing data manipulation for a client who sends us periodic updates in an Access 97 .mdb file.
I want to have my operations dept place the file in a location on my network, and then use a stored procedure to update my SQLServer 2000 database.
Everything works, except for one problem. If there is a null value in a date column, I get an error about converting data type DBTYPE_DBTIMESTAMP to datetime.
I am not a SQL guru, and I know less about Access 97. There must be some magical CAST or CONVERT or CASE to use, and I have tried a few.
Any help?
Terry
-
HI Terry,
I think the best way to handle this situation is to set you date column in your SQL Server database to allow nulls. Your other option is to detect a null in the date column and put a default date before the insert. Hope this helps.
Mike of Saint Paul
-
Thanks. Unfortunately, I can not include the column with the null date in a select statement.
Select
MemberID,
CASE
WHEN "Member DOB" IS NULL THEN Cast('No Date' As varchar(50))
Else
Cast("Member DOB" as varchar(50)) End as MemberDOB,
MemberFName
From AccessDB...MemberInfo
Returns
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Terry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply