none
Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime2(7)

    Question

  • Hi,

    I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL:

    stor_id     char(4)     not null,
    ord_num  varchar(20) not null,
    date         datetime    not null

    I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016:

    select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales')

    The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime].

    So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005:

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k

    Starting copy...

    30 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.)

    C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k

    Starting copy...
    SQLState = 22008, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
    SQLState = 22008, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
    SQLState = 22008, NativeError = 0

     

    I can change the data type on the 2008 table from datetime2 to datetime, bcp it out and then it works fine.

    Question is - why the change in datatype mapping?  Do we expect the non MSSQL Server data type of datetime to now map to datetime2?  Shouldn't it remain as datetime?  I see compatibility problems when folks move from MSSQL 2005 to 2008 (as in this situation).  Is this a known issue?


    Thank you,

    -Paul

     

    • Moved by Dan Benediktson Friday, September 03, 2010 4:42 PM Linked server datatype remapping belongs in Engine forum (From:SQL Server Data Access)
    • Moved by Tom PhillipsModerator Friday, September 10, 2010 7:04 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Thursday, September 02, 2010 5:16 PM

All replies

  • Hi Paul,

    datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. I suppose this is why Sybase ASE maps Sybase datetime to SQL Server datetime2.
    I would suggest you contacting Sybase for more information about this change.

    Also, the error "Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format" occurs because of the datetime2 is only supported in SQL Server 2008 or later. A value with datetime2 is not able to be stored in SQL Server 2005.

    For more informaiton about datetime2, please see:
    http://technet.microsoft.com/en-us/library/bb677335.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, September 06, 2010 8:51 AM
    Moderator
  • Hi Jin,

    Yeah, I am at Sybase.  I'm going to run some tracing - I don't think our Provider would know how to map over to the SQL Server datatypes, that is our SQL server, ASE, doesn't have a datetime2, but quite possibly since our datetime does have ranges matching datetime2, this could be why the current mapping occurs.  I'll run some diagnostics to see what's going on there and I might want to post some questions on this thread to get some clarification, etc.

    Thank you,

    -Paul

     

    Friday, September 10, 2010 5:35 PM
  • Hi Jin,

    When I check our data in the Sybase TDS, the data type is still the same from the Sybase ASE database server:

    It's a TDS_DATETIME and data looks like this:

    1985-10-31 00:00:00.0

    My thinking is the MSSQL Server 2008 has changed the way it is interpretting the data type from a non SQL Server backend.  My thought is this shouldn't be the case, especially due to backward compatibility issues (such as importing the data to older MSSQL Server like 2005).  I'm going to check with the customer to see if they have ability to log cases with Microsoft to get this reported.  I'll also talk to my Engineer's to see if there's something else that can be done.

    Thank you,

    -Paul

     

    Tuesday, September 14, 2010 6:48 PM
  • Hi Paul,

    You may be right.

    Alternatively, we can submit a feedback at https://connect.microsoft.com/sql.
    Connect site is a connection point between you and Microsoft, and ultimately the larger community. Your feedback enables Microsoft to make software and services the best that they can be, and you can learn about and contribute to exciting projects.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, September 16, 2010 8:57 AM
    Moderator