Error passing accented characters to Oracle

28 Feb 2007 3:21 PM

Solarfish

I am trying to insert a record into a table on a linked Oracle server from
Query Analyser. Everything works fine until I add an accented character.

For example:

INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION VALUES
('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,'Verkaufsstellenumsatze')

works fine. However if I add an accented character:

INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION
VALUES('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,'VerkaufsstellenumsÃ¡tze')

then the insert fails with the following message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-01401: inserted value too large for
column]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow
returned 0x80004005: ].

Any suggestions?

28 Feb 2007 10:54 PM

Erland Sommarskog

Solarfish (Solarf***@discussions.microsoft.com) writes:
Show quote

> I am trying to insert a record into a table on a linked Oracle server from
> Query Analyser. Everything works fine until I add an accented character.
>
> For example:
>
> INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION VALUES
> ('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,
> 'Verkaufsstellenumsatze')
>
> works fine. However if I add an accented character:
>
> INSERT INTO CONDOR_SKYE..CONDOR.BW_LANGUAGE_TRANSLATION
> VALUES('QRYELT','YCH_L_1038_ZEUSG_M00','EN','20070208',null,
> N'Verkaufsstellenumsátze')
>
> then the insert fails with the following message:
>
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: ORA-01401: inserted value too large for
> column]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow
> returned 0x80004005: ].
>
> Any suggestions?

What is the datatype on the Oracle side?

Try using N'Verkaufsstellenumsátze' to get a Unicode string.

Also, what version of Oracle are you using? I don't think MSDAORA is
any good for Oracle 10, and hardly even Oracle 9. Oracle has their own
OLE DB provider, which fully supports Oracle 10.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

6 Mar 2007 9:26 AM

Solarfish

"Erland Sommarskog" wrote:

> What is the datatype on the Oracle side?
>

The data type on the Oracle side is Char.

> Try using N'VerkaufsstellenumsÃ¡tze' to get a Unicode string.
>

I had already tried that but it made no difference

> Also, what version of Oracle are you using? I don't think MSDAORA is
> any good for Oracle 10, and hardly even Oracle 9. Oracle has their own
> OLE DB provider, which fully supports Oracle 10.
>

The Oracle database is version 9 although it will be moved to version 10
later in the year so I do need something that will work for both. Can you
point me towards some instructions for using the OLE DB provider from Query
Analyser?

6 Mar 2007 12:09 PM

Solarfish

OK, I have made some progress, I now have a different error message!

I installed the Oracle 10 client and created another Linked Server using the
Oracle OLE DB provider. However if I try to open that connection, or even to
just view the Tables, I get the following error message:

Error 7399: OLE DB provider 'OraOLEDB.Oracle' reported an error. Access Denied

This linked server is using the same connection information, name and
password as the MSDAORA based connection which works perfectly (apart from
the accented characters of course).

I have searched on the internet and have found a few others with the same
problem but the only working solution I have come across is to revert back to
MSDAORA which of course is no good.

Any further advice would be most welcome.

6 Mar 2007 11:25 PM

Erland Sommarskog

Solarfish (Solarf***@discussions.microsoft.com) writes:

> "Erland Sommarskog" wrote:
>> What is the datatype on the Oracle side?
>>
>
> The data type on the Oracle side is Char.

And what is the character set for the column? Seems to me that it is
UTF-8, and then you get a problem. But I don't know much about Oracle.

And even less do I have a clue why the Oracle Provider does not work. Maybe
you should try an Oracle forum.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13 Mar 2007 12:30 PM

Solarfish

"Erland Sommarskog" wrote:

>
> And even less do I have a clue why the Oracle Provider does not work. Maybe
> you should try an Oracle forum.
>

Well I tried an Oracle forum and found the answer in this thread
http://forums.oracle.com/forums/thread.jspa?threadID=226117&tstart=45

Specifically I needed to perform the following additional task to get the
Oracle Provider connection to work:

1. Right-click on the linked server you created, select Properties from the
popup menu.
2. In Linked Server Properties dialog box, click Options... button under
Provider Name dropdown combo.
3. Check (or enable) "Allow InProcess" option.
4. Click Apply button.
5. Click Ok button.

I hope this might be of use to others.