RE: Problems with SQL Server Link Server to ORACLE

[Loughmiller, Greg]

Title: Problems with SQL Server Link Server to ORACLE

See if your connection can perform some sort of select via the ODBC in SQL....

 

I have been playing around with moving data from Oracle to SQL via a DTS package....  And got some nasty error messages when the columns were mismatched(for example, moving a varchar to float,etc),  permissions on the ORalce side, and permissions on the SQL side...

 

I actually had to create a user(same name and password) on each database.. Shouldn't have to-but that got around one of my problems...

 

HTH

-----Original Message-----
From: Denham Eva [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 30, 2001 3:15 AM
To: Multiple recipients of list ORACLE-L
Subject: Problems with SQL Server Link Server to ORACLE

Hi List,

Problem receiving this error on a Link Server from SQL Server 7.0 and 2000 to Oracle 817

INSERTED INTO IR TABLE IR_MAXIMO_WORKORDER [SQLSTATE 01000] (Message 0) INSERTED INTO MAXIMO TABLE MAXTFMC..MAXIMO.IR_WO_IN [SQLSTATE 01000] (Message 0) OLE DB provider 'MSDAORA' could not INSERT INTO table '[MAXTFMC]..[MAXIMO].[IR_WO_IN]'. Unknown provider error. [SQLSTATE 42000] (Error 7343) [SQLSTATE 01000] (Error 7312). The step failed.

The ODBC that has been setup on both these SQL Servers is Oracle ODBC driver, I have personally created the connections and tested them - thus it can be assumed that the ODBC links work.

The enviroments are as follows:
Sql Server 1
Sql Server 7.0
Win NT 4.0 Service Packs 6a.
Oracle ODBC Driver

Sql Server 2
Sql Server 2000
Win NT 4.0 Service Packs 6a
Oracle ODBC Driver

Oracle System
Oracle 8.1.7
Win 2k

The problem appears to be resolved when the Link Server is rebuild using Microsoft OLE DB Provider for ODBC Drivers, which is substantial slower that the Microsoft OLS DB Provider to Oracle which has worked for a couple of weeks and yesterday suddenly decided not to work anymore.

Does anyone have any idea of what is happening here?

TIA
Denham

---

This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit www.marshalsoftware.com

---