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.

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.

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

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

03-15-2007, 08:10 AM

PAVB

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.

03-15-2007, 10:23 AM

dbasan

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.

All times are GMT -4. The time now is 11:48 AM.