Posted by John Bell on 10/08/52 11:35

Hi

Did you check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;822841
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;197459

John

"Crazy Cat" <danbredy@hotmail.com> wrote in message
news:1135272215.755328.95640@g49g2000cwa.googlegroups.com...
> Hi all,
>
> I am having trouble getting linked Oracle 9 server in MS SQL Server
> 2005 Express to work properly. My machine is running Windows XP.
>
> The Microsoft and Oracle OLE DB Providers have problems dealing with
> Oracle's Numeric Data Type, so I decided to use Microsoft's OLE DB for
> ODBC Provider and an Oracle ODBC source. When using the Microsoft ODBC
> for Oracle Driver in my ODBC source I have inconsistent behavior.
> Sometimes my queries are processed properly, then other times I get the
> following error
>
> OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returned
> message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> failed".
> OLE DB provider "MSDASQL" for linked server "ODBCBEAST" returned
> message "[Microsoft][ODBC driver for Oracle][Oracle]".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL"
> for linked server "ODBCBEAST".
>
> I have no idea why sometimes I can connect to the linked server with no
> problems and
> why other times it performs like this. I'm not changing anything about
> the system I can think of. When I use an Oracle client (PL/SQL) I have
> absolutely no problems connecting. TNSPING returns that the connection
> is good.
>
> This is unacceptable so I decided to try my luck with the Oracle 10g
> ODBC driver. However when I use this and perform an openquery select
> against the linked server I get back only 11 rows, when I know that the
> database has over 100 rows (in fact when using the Microsoft ODBC
> driver and it works that's what I get). I figured maybe the buffer
> setting needed to be raised in the ODBC configuration so I took it from
> 64000 to 600000 (a magnitude of 10) but I still get back only 11 rows.
> I'm at my wit's end.
>
> Any suggestions on resolving one or the other problem would be much
> appreciated.
>
> Thanks much
>

[Back to original message]