Post reply

Problems using Linked Server to Oracle

  • March 20, 2003 at 4:58 am

    #58999

    Can someone help me here? I have a linked server to an Oracle database. I can see the tables in enterprise manager but when I try to access the tables using Linked_Server..schema.table I get an error : OLE DB provider 'MSDASQL' returned an invalid column definition. This occurs for all tables. I have tried accessing the tables via DTS using an ODBC link and they read in OK. Are there any settings I can alter to get this to read OK?

  • March 20, 2003 at 6:33 am

    #451548

    You will need to use 4 part naming

    eg

    select * from ORACLEDATABASE..<Tableowner>.<Tablename>

    Also I think everything needs to be in uppercase

    Steven

  • March 20, 2003 at 7:09 am

    #451551

    I do use the four part naming, and have tried uppercase but still get the same error. I get limited success with openquery but only a few lines are received before the error occurs again.

  • March 20, 2003 at 8:12 am

    #451565

    Does the query run from SQLPlus (ie oracle itself)

    Steven

  • March 20, 2003 at 8:40 am

    #451572

    Query runs fine from either SQLPlus or within a dts transformation query

  • March 20, 2003 at 9:02 am

    #451577

    I installed Client for Oracle and use Oracle ODBC Driver to configurate Data Sourse and

    I manage to get data from tables but executing query from Query Analyzer takes much more time than in DTS? Why does it happen?

  • March 20, 2003 at 9:08 am

    #451578

    Via,

    I think DTS may optimise these things a litte. What settings did you use for your linked server?

  • March 21, 2003 at 5:00 am

    #451670

    1. I created System DSN using Oracle ODBC Driver

    2. I created Linked Server with parameters:

    Provider name- "Microsoft OLEDB provider for ODBC Drivers"

    Data Source - My DSN name

    3. On Sequrity tab I selected "Be made using this sequrity context" and entered valid Oracle user name and password.

    4. I QA I exequte a query using 3 part naming eg SELECT * FROM <LINKED SERVER NAME>..<Tablename>. Everything works.

  • March 21, 2003 at 5:06 am

    #451671

    Did you run the DTS job from the server itself? (as DTS is a client side app)

    Steven

  • March 21, 2003 at 5:12 am

    #451672

    I've been testing this on my own PC which has SQL, Oracle client and ODBC connection. The Oracle I'm testing against is on Windows 2000 but may be on Unix when deployed. It is finding the connection though, it just doesn't seem to be interpreting the columns properly.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply