Access 97, Linked Servers, and SQL

• Terry Hill

  Newbie

  Points: 9

  More actions

  May 23, 2004 at 8:02 pm

  #65965

  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

• Michael Fong

  SSC Journeyman

  Points: 80

  More actions

  May 25, 2004 at 7:18 am

  #507644

  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

   

• Terry Hill

  Newbie

  Points: 9

  More actions

  May 25, 2004 at 4:48 pm

  #507767

  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