Insert data through View using openquery on Linked Server fails

• sqlTobi

  Right there with Babe

  Points: 739

  More actions

  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

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  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.

• sqlTobi

  Right there with Babe

  Points: 739

  More actions

  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

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  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.