SQL Server Performance

Linked server to Lotus notes database

Discussion in 'SQL Server 2005 General DBA Questions' started by shivi, Oct 29, 2008.

  1. shivi New Member

    Hello,
    I have successfully set up linked server from sql server 2005 (sp2) to a lotus notes database ( using NOTESSQL ver 8.0 ) and can query the tables with text columns in the domino server using management studio.
    But when i query a lotus notes table with 'date' datatype , I get the following error message
    select inserted_dte from table_1OLE DB provider "MSDASQL" for linked server "lnk_svr_nme" returned message "[Lotus][ODBC Lotus Notes]Data value is not a valid date, time or timestamp".
    Msg 7330, Level 16, State 2, Line 2
    Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "lnk_svr_nme".
    Has anyone come across this issue?
    Please share your experience.
    Thanks
    Shivi
  2. MohammedU New Member

    May be it is conversion issue...
    Try to use OPENQUERY option to get the data using query window/analyzer....
  3. shivi New Member

    Thank you for your response.
    I did try select date_column from linked_server...table
    and
    select * from openquery (linked_server,'select date_column from table')
    Both are not working. When I googled , I came across the same issue faced by many but no one has posted a solution.
    I hope someone would have come across this and found a solution.
    I guess it is not an issue with the driver.
    Please help me .
    Shivi.
  4. dhay1999 New Member

    I have had that issue on AS400 DB2. It has to do with how the different vendors store dates. I'm imagining that as in DB2 a "null date" is stored as '0001-01-01', which is not a valid date in MS SQL. Valid dates for MS SQL are between January 1, 1753 to December 31, 9999, while IBM goes from '0001-01-01 to 12-31-9999. If you want to know why, you'll need to talk to a Monk (Gregorian Calendar). You have to explicitly convert or cast the column to VARCHAR/CHAR datatype, or wrap it is some isdate function.I don't know what the syntax is for Cast/Convert in Domino, but this is basically the idea.
    select * from openquery (linked_server,'select cast(date_column as varchar(25)) as New_Date_Column from table')
    Hope that helps!
    David
  5. boomer New Member

    I am having problems connecting to Lotus Notes database using a linked server in SQL Server 2005. Could you please explain how you set up the linked server? I am using Lotus Notes 6.03 and NotesSQL driver 8.0.
  6. satya Moderator

Share This Page