Post reply

Create table from SQL Server to Oracle

  • September 14, 2004 at 2:03 am

    #83752

    Hai,

    Is there any way to create table from SQL Server to Oracle using query analyzer?

    Please help. Thanks.

  • September 14, 2004 at 6:04 am

    #522929

    Disclaimer #1: I've never used Oracle.

    Disclaimer #2: I'm not sure what you do and don't know how to do.

    Disclaimer #3: I've never actually tried to create an object via a linked server, but I see no reason it shouldn't work.

    First, you can get the SQL Server SQL to perform the drop/create from either query analyzer (right click on the table name and "Script object...") or Enterprise Manager ("Generate SQL Script").

    More likely what you are after, to access Oracle you should be able to use the linked server interface. Look up "linked servers" -> "access Oracle database instance" in BOL. You should then be able to prefix the generated script object names with the server/database/owner desired in Oracle. Following is an example to add a linked server that I stripped from the above BOL entry.

    exec sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB'

  • September 14, 2004 at 10:22 am

    #523006

    Disclaimer #1 goes me too ... some day they'll make the mistake and give me too much power ...

    If the linked server has the proper permissions then you should be able to perform this via an OPENQUERY.



    Everett Wilson
    ewilson10@yahoo.com

  • September 17, 2004 at 2:02 am

    #523464

    Thanks for you answer.

    Let me clarify my problem.

    I try to run this query in Query Analyzer.

    SELECT * FROM OPENQUERY(ARIES_ATD, 'CREATE TABLE ABC (location varchar(4), lot varchar(12))')

    and it return me this error message.

    Could not process object 'CREATE TABLE ABC (location varchar(4), lot varchar(12))'. The OLE DB provider 'MSDAORA' indicates that the object has no columns.

    OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='MSDAORA', Query=CREATE TABLE ABC (location varchar(4), lot varchar(12))'].

     Anybody have idea what this error message means.

    Thanks.

  • September 20, 2004 at 10:44 am

    #523695

    I believe this is an MS SQL error.  Can you run the Oracle part only of the above statement on the Oracle server via SQL Plus?



    Everett Wilson
    ewilson10@yahoo.com

Viewing 5 posts - 1 through 4 (of 4 total)

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