Post reply

Insert data through View using openquery on Linked Server fails

  • June 7, 2011 at 6:53 am

    #251310

    Hi experts,

    I've got an oracle linked server.

    In my SQL Server 2008 R2 DB i've a view pointing to a table on the oracle server via openquery.

    All text columns on the oracle server are defiend as varchar2.

    In the sql server view all text columns are shown as nvarchar (why?).

    When I try to insert data into the oralce table via the view and I use the maximum length for a text column and use a special character (like the german ö ä ü) in the text, I get the error message

    'ORA-12899: value too large for column "TEST"."TABLENAME"."TEXTCOLUMN" (actual: 52, maximum: 50)'

    I think thew problem is the mismatch between varchar2 and nvarchar.

    But how can I get around this problem?

    I've the same problem using 4-part-notion instead of openquery.

    Linked server is created with:

    EXEC master.dbo.sp_addlinkedserver @server = N'ORACLE', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORACLE'

    The view is defiend as:

    CREATE view [dbo].[VIEW2ORACLE] as

    SELECT * FROM OPENQUERY(ASSINT, 'select PK, TEXTCOLUMN from TABLENAME')

    Thanks &

    Best regards

    Tobias

  • June 8, 2011 at 3:32 pm

    #1335974

    Tobias Ortmann (6/7/2011)

    ORA-12899: value too large for column "TEST"."TABLENAME"."TEXTCOLUMN" (actual: 52, maximum: 50)

    Have Oracle side columns defined their sizes in bytes? like varchar2(50 bytes)?

    Double the size of the columns or define sizes in characters.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • June 8, 2011 at 11:06 pm

    #1336031

    Thanks, Pablo.

    Yes, the columns are defined in bytes.

    We changed them to charaters and it works.

    But I don't understand why 🙂

    We read from varchar2(50 bytes) and insert into varchar2(50 bytes), why do we get the error message?

    Can you or someone else explain it?

    Tobias

  • June 12, 2011 at 5:55 am

    #1337451

    Tobias Ortmann (6/8/2011)

    Thanks, Pablo.

    Yes, the columns are defined in bytes.

    We changed them to charaters and it works.

    But I don't understand why 🙂

    We read from varchar2(50 bytes) and insert into varchar2(50 bytes), why do we get the error message?

    Can you or someone else explain it?

    Tobias

    You are welcome Tobias.

    Here is why... mutibyte character set.

    Source database is set for a single byte characterset meaning that each character uses ONE byte while target database is set for multibyte characterset meaning that each character uses (let's say) TWO bytes therefore the string "OHMYGHOSH!" needs 10 bytes on first database but as many as 20 bytes on the second one

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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