update from oracle db

  • newbieuser

    SSCrazy Eights

    Points: 9413

    Hi friends,

    Currently, we have SQL Server 2005 database linked to Oracle database (ANSI). We are planning on converting Oracle database to Unicode. Our customer has some scripts running on SQL server database that updates some SQL Server ANSI fields getting data from Oracle database(using linked server). Would like to know if there could be any known issues updating SQL Server ANSI fields from an Oracle Unicode database (using linked server)? Also, how can I find out if a SQL Server column is ANSI?

    Thank you so much for your help..

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    newbieuser (4/5/2015)


    Hi friends,

    Currently, we have SQL Server 2005 database linked to Oracle database (ANSI). We are planning on converting Oracle database to Unicode. Our customer has some scripts running on SQL server database that updates some SQL Server ANSI fields getting data from Oracle database(using linked server). Would like to know if there could be any known issues updating SQL Server ANSI fields from an Oracle Unicode database (using linked server)? Also, how can I find out if a SQL Server column is ANSI?

    Thank you so much for your help..

    Cannot remember any issues although I've always "Staged and Explicit Convert" instead of relying on implicit conversions.

    😎

    To view the data types use something like

    SELECT

    T.object_id

    ,T.name AS TABLE_NAME

    ,C.name AS COLUMN_NAME

    ,TY.name AS DATA_TYPE

    ,C.collation_name AS CHAR_COLLATION

    FROM sys.tables T

    INNER JOIN sys.columns C

    ON T.object_id = C.object_id

    INNER JOIN sys.types TY

    ON C.system_type_id = TY.system_type_id;

  • Ed Wagner

    SSC Guru

    Points: 286942

    Eirikur Eiriksson (4/6/2015)


    Cannot remember any issues although I've always "Staged and Explicit Convert" instead of relying on implicit conversions.

    It's been quite a while since I've had to convert data between SQL Server and Oracle, but Erikur hit the nail on the head. I had nothing but problems until I started explicitly converting everything. I'd recommend that you adopt the same approach.

  • newbieuser

    SSCrazy Eights

    Points: 9413

    THank you for the replies. May be I misinterpreted about the conversion... We are converting Oracle database from ANSI to Unicode. SQL Server 2005 database remains the same, so we are hoping the existing scripts on SQL database that updates SQL ANSI field from the data in Oracle database(converted to unicode) will work without problems..

    Also, the sql provided above gives just the datatype and char_collation of the database columns but how do I find out if it is ANSI field?

    THank you so much for your support

  • MMartin1

    One Orange Chip

    Points: 27375

    I hope I am understanding your question. Combine the results of the sql provided above with the information presented here :

    https://msdn.microsoft.com/en-us/library/bb177899%28v=office.12%29.aspx

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • MMartin1

    One Orange Chip

    Points: 27375

    I have also heard the terms 'ansi' and 'ascii' used interchangeably. If that is what you mean then the simple answer is that unicode text starts with a "N" in Sql Server (Nvarchar versus Varchar). Again I hope I am understanding you correctly.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

Viewing 6 posts - 1 through 6 (of 6 total)

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