-
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.
"What is past is PROLOGUE"
-
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 :(
-
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.
-
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"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|