sqlserver HS ODBC

**jennifer2007** · 03-14-2007 09:13 AM

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.

**dbasan** · 03-14-2007 01:37 PM

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.

**jennifer2007** · 03-14-2007 08:07 PM

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

**jennifer2007** · 03-15-2007 03:49 AM

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 :(

Reply With Quote

03-15-2007 08:10 AM #5

PAVB

Senior Advisor

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.

Reply With Quote

03-15-2007 10:23 AM #6

dbasan

Advisor

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.