May 16, 2011 at 3:00 pm
I have two servers who behave quite differently accessing the same Oracle 10G instances.
Server 1 is a MSW2k3 Svr w/ SQL Server 2005 SE with native MS-OLEDB connectivity with linkservers connecting to the company production Oracle instance(s) (15 are archived online with 1 production). The linkservers are used in an ongoing ETL to data warehouse solution and have not stopped working (ever).
Server 2 is a MSW2k8 Svr w/ SQL Server 2008 R2 SE with (now that MS no longer provides OLE-DB access to Oracle) an Oracle 10G client for drivers to support the OLE-DB connectivity.
On the host servers I can ping and query the oracle data equally without error. However, on the new server WS2k8 w/ SS2k8R2 I get an error running the exactly same stored procedure against the exactly same Oracle data object.
On Server 1 the procedure executes completely in a few minutes and on Server 2 it fails almost immediately with
the following error :
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' for linked server 'MYORADB' returned data that does not match expected data length for column '[OraOLEDB.Oracle].'MYORADB''. The (maximum) expected data length is 64, while the returned data length is 12.
This leads me to believe the error is not the data dictionary that many references point to.
Why? because one server hits the same data and has NO problems with it, the other can't return a record because it beleives the size is wrong.
So I need some help with solving this problem. I can hit all of the other database objects in our Oracle environment with the new server except for the one production server I need to hit.
Is it possible the Oracle driver doesn't know what its doing (on one machine but does on the other?) possibly and I'll look to Oracle for that solution but in the meantime this is (to me) a SS2008R2 issue.
May 16, 2011 at 3:28 pm
You may want to check the data types involved.
I suggest that you narrow down the columns transfered to a few columns to isolate the problem.
MCDBA, MCITP, MCSE, OCP
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2011 at 12:05 am
Windows Server 2003 32-bit/64-bit ?
Recent Updates for the OS ?
SQL Server 2005 SP4 ?
SQL Server 2005 32-bit / 64-bit ?
Windows Server 2008 R2 32-bit/64-bit ?
Recent Updates for the OS ?
SQL Server 2008 R2 CU7 or SP1 ?
SQL Server 2008 R2 32-bit / 64-bit ?
Oracle10g client version ?
Linked Server create with ORACLE PROVIDOR FOR OLEDB?
Linked Server create with Microsoft OLE DB providor for ODBC Drivers ?
take a snapshot of Linked Server Oracle 10g then send it here
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply