This discussion is archived
3 Replies Latest reply: Mar 15, 2013 9:12 AM by mkirtley-Oracle RSS

Issue while accessing a SQL Server table over OTG

996953 Newbie
Currently Being Moderated
Hi,
I have been learning oracle for about 1.5 years and am just starting to learn some OTG pieces. I am wondering about an issue. The issue is:

"We need help with an issue we are having while accessing a SQL Server table over OTG. We are getting the following error message in Oracle :
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver]Unicode conversion failed {HY000}

The column it is failing on is "-----------" in the view --------------- in the SQL Server database pointed to by the Oracle DB Link ------------------- thats created in the Oracle instances ---- and -----.

This was working before, but is now failing, we suspect its due to new multi-byte data being added to the base table in the above column."

I took out the details and added ---- instead. I am wondering your guys thoughts on fixing this issue and helping me learn along the way. Thanks
  • 1. Re: Issue while accessing a SQL Server table over OTG
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    To investigate further could you give the following information -

    1. What is the character set of the Oracle RDBMS being used. What is returned by -

    select * from nls_database_parameters;

    2. What is the character set used by SQL*Server ? Run the following -

    SELECT databasepropertyex('<db_name>', 'Collation')

    it should return something like -

    Latin1_General_CI_AS

    and then use that value in the following select -

    SELECT COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage');

    it should give a result similar to -

    1252

    3. What is the SQL*Server create statement for the table giving the problem or at least the dataype of the column giving the problem ?

    4. What is displayed from SQL_PLUS if you issue -

    describe <table_name>@gateway_link

    - do not put a semicolon at the end of the statement.

    5. What is the full name and version of the gateway being used and the version of the Oracle RDBMS ?

    Regards,
    Mike
  • 2. Re: Issue while accessing a SQL Server table over OTG
    996953 Newbie
    Currently Being Moderated
    Hi Mike,
    Thanks for the response, here are the details:

    1. What is the character set of the Oracle RDBMS being used. What is returned by -

    select * from nls_database_parameters;

    NLS_CHARACTERSET
    AL32UTF8

    NLS_NCHAR_CHARACTERSET
    UTF8

    We get SQL_Latin1_General_CP1_C1_AS and 1252 as Collation Property and Code Page

    The datatype of the column in question in SQL Server is nvarchar(100).

    When I do a describe on the SQL Server view ( desc CK_DATA_FOR_OPL@------- ), I get the error below;
    ERROR: object CK_DATA_FOR_OPL does not exist

    Select * from CK_DATA_FOR_OPL@------ where rownum =1 does get me a row.

    create table tmp_tab as
    Select * from CK_DATA_FOR_OPL@----- where rownum =1;
    desc tmp_tab shows the datatype of the said column in the table created in Oracle as NVARCHAR2(150).

    Not sure why a column defined with size 100 in SQL Server should come across as 150 when seen over OTG. We see something similar in DB2 tables we access over OTG as well.

    Edited by: 993950 on Mar 15, 2013 8:49 AM
  • 3. Re: Issue while accessing a SQL Server table over OTG
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Thanks fo rth einformation so far.
    It will still be useful to know the name of the gateway you are using - Database Gateway for SQL*Server (Dg4MSQL) or Database Gateway for ODBC (Dg4ODBC) and the version.

    For the describe youmay have to issue it as -

    describe "owner"."table_name"@db_link

    where the "owner" and"table_name" are in the correct case for the objects as they are named in SQL*Server.
    Could you post the SQL*Server create statement for the table so we can compare ?

    Can you also post the gateway init<sid>.ora file so we can see the parameters being used.

    If you are selecting columns using an Oracle Unicode database then there can be differences in column lengths because characters translated to Unicode can use more bytes than they use in a non-Unicode database.
    That is why we need to know the actual gateway you are using, the character sets involved and the gateway init<sid>.ora file.

    Regards,
    Mike

    Edited by: mkirtley on Mar 15, 2013 4:12 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points