RE: Oracle Computed column datatype

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi, Kevin
Just came back from holidays.
In my last message, I described my situation. The column weight is defined
as number(5). There is a behavior difference between MS OLE DB Oracle driver
and the SQL linked server layers in treating a computed column since it is
returned as a number in the fairt case and as a character in the other case.

My issue is the following:
If I use the local MS OLE DB Oracle driver, computed columns are returned
as adNumeric. If I use a linked server connection, computed columns are
returned as adVarWChar. Why is there a difference ?

Case 1) Using local MS Oracle OLE DB driver and SQL*Net
COra.ConnectionString = "Provider=msdaora;" & _
"Data Source=ORACLE;" & _
"User Id=OracleUser;Password=OraclePassword;"
Sql = "select weight, weight/2000 tons from abc"
In the recordset returned by this query, column weight is adNumeric and
tons is adNumeric. This is what is expected and all is perfect.

Case 2) Using linked server on SQL Server DB. ORA linked server is defined
on the SQL Server.
COra.ConnectionString = "Provider=sqloledb;" & _
"Data Source=MySQLServer;" & _
"Integrated Security=SSPI"
Sql = "select * from OPENQUERY(ORA,'select weight, weight/2000 tons from
abc')"
In the recordset returned by this query, column weight is adNumeric and
tons is adVarWChar. This datatype difference is my problem.
I do not understand why the tons column is returned as character and this is
causing my apps to fail when converting the value on computers where the
decimal separator is a comma. I want to know what is the logic behind this
conversion of numeric to char. Is there anything I can do to correct this
behavior ?

TIA
SP



"Kevin Yu [MSFT]" wrote:

> Hi SP,
>
> Yes, I tried directly connecting to Oracle server which works fine. Would
> you please try to specify both precision and scale?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>
>
.

Relevant Pages

  • Re: Linked Server Performance
    ... an ole db for oracle driver. ... In my case the queries go thru SQL OLE DB ... Using a linked server in SQL, they are slow and SQL cpu usage goes to ...
    (microsoft.public.sqlserver)
  • OLE DB Provider for ODBC and Oracle
    ... I created a linked server for MS SQL Server 2005 Express to an Oracle ... database using the OLE DB Provider for ODBC. ... I'm using the OLE DB Provider for ODBC instead of the Oracle OLE DB ...
    (comp.databases.ms-sqlserver)
  • Re: Linked server to as400 - for the love of god please help.
    ... > There is an ODBC DSN on the server machine. ... Creating a linked server using ... > OLE DB provider 'MSDASQL' reported an error. ... > So i instead create a linked server using IBM's own OLEDB provider. ...
    (microsoft.public.sqlserver.server)
  • Re: Linked Server MS SQL 2000 and Pervasive 7.82
    ... If I remember the OLE DB Provider didn't work with MS SQL ... Linked server stuff until Pervasive.SQL 2000i SP4 or thereabouts. ...
    (comp.databases.btrieve)
  • Delete via linked server
    ... I am trying to delete records in a table pointing via linked server to Sybase ... OLE DB provider 'MSDASQL' could not delete from table '"dbo"."all_metric"'. ... OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows ...
    (microsoft.public.sqlserver.server)