sqlserver HS ODBC
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sqlserver HS ODBC

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    sqlserver HS ODBC

    Hi friends,

    I successfully made connection to ACCESS DB and FOXPRO DB using
    HS Connectivity.

    But when I tried making HS to SQLSERVER DB using the same procedure
    i got this error:

    select count(*) from EMP@SQLSVR_db
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL
    Server]Cannot open database requested in login 'master'. Login fails. (SQL
    State: 37000; SQL Code: 4060)
    ORA-02063: preceding 2 lines from SQLSVR_DB


    I created EMP table on MASTER DB on PROD_SERVER.
    Why does it fails login on Master my default config DB when I did successful
    connect during the config set-up.

    My sql login userid is SA and with no password.


    Thanks a lot.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    1) who is the owner of table EMP?
    2) Is this the same user you use in the dblink to connect?

    Master stores all the user info and logins.
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    Hi dear,

    The owner as i said is "master" db and the connection is configured
    in ODBC. By the way "master" db is just similar to "northwind" db, a pre-created default databases in sqlserver. Do you mean i have to transfer the
    EMP table to "northwind" db?

    Anymore ideas?

    Thanks

  4. #4
    Join Date
    Feb 2007
    Posts
    212
    Hi Friends,

    Can you help me find docs on how to configure generic connectivity (HS)
    specific for MS SQLSERVER pls.

    I have found the following docs but not so complete one. It says:
    ========
    Oracle RDBMS 9.0.1.x on WIN2K using Heterogenuous Service (HSODBC) connect
    to MS SQLSERVER 2000


    Symptom(s)
    ~~~~~~~~~~

    Setting up HSODBC connect from Oracle 9.0.1 database to SQL Server 2000


    When query a valid table from SQL 2000, get:
    select * from @hsodbc;
    ORA-1005 "null password given; logon denied"


    Change(s)
    ~~~~~~~~~~

    ODBC test from WIN2K shows connection via ODBC to SQL SERVER 2000 works
    fine. In ODBC test, the username connect to SQL Server is "sa", password
    is blank.


    Cause
    ~~~~~~~

    Oracle database link require a valid user name and password


    Fix
    ~~~~

    Setup password at SQL Server side for user "sa", then recreate the database
    link in step 9:

    create database link hsodbc connect to "sa" identified by "sa"
    using 'sqlsvr_db.world';

    select count(*) from employees@hsodbc;

    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'. (SQL State: 28000; SQL Code: 18456)
    ORA-02063: preceding 2 lines from HSODBC
    ========

    but still the above error :(
  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by jennifer2007
    Hi dear,

    The owner as i said is "master" db and the connection is configured
    in ODBC. By the way "master" db is just similar to "northwind" db, a pre-created default databases in sqlserver. Do you mean i have to transfer the
    EMP table to "northwind" db?

    Anymore ideas?

    Thanks
    I'm sorry but MASTER is not like NORTHWIND, SQL Server MASTER is more like SYS or SYSTEM schemas in Oracle... you DON'T want to create anything on MASTER database.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Generic connetivity requires either ODBC (drivers) or gateway license to connect Oracle and non-oracle databases. Using ODBC has limited functionality and gateway has more than ODBC can do(check docs).

    which connection you have in your case?
    you can further search knowledge base at metalink.oracle.com.

    query:
    select count(*) from employees@sqlsvr_db.world; (OR)
    select count(*) from all_catalog@sqlsvr_db.world; to see if you can connect to the remote db.

    You can create a separate database at SQL server and create/move table EMP to test once the connectivity is established. Better not mess up creating objects in Master.
    "What is past is PROLOGUE"

  7. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width