Post reply

Access 97, Linked Servers, and SQL

  • 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

  • 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

     

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply