Select problems from 9i to SQL Sever - Heterogeneous Connectivity

Hi
I have configured a databae link from a 9i Server to an MS SQL Server. I tested the link through 9iAS and it says the connection was successfull. I am know trying to run a simple select query against the Northwind DB in the SQL Server with the following statement.
Select * from Employees#sqlserv01.domain_name ==>sqlserv01.domain_name is my link which works fine.
I get the following error.
ORA-00942: table or view does not exist
[Generic Connectivity Using ODBC]393330(0,0,1[[]])
ORA-02063: preceding 2 lines from SQLSERV01 (WWV-11230)
Invalid SQL statement or, you do not have privileges to select from the Table/View. (WWV-17138)
Any ideas.
Thanks,

Related

Discoverer 4.1 and sql server 2000

Hi,
I am having difficulty creating an EUL using discover41
againstr SQL Server 2000. I have created an ODBC source
which i can connect to and Discoverer Administrator
starts to create the EUL but always fails at the end
giving the error:
Creating tables and views...
Populating tables with default data...
Install failed, cause : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'suid'
can anyone help?
Also if i create the EUL in Oracle and then connect
to Administrator then create a new Business Area and then
select the database as a database link i've created.
I select the sql server user and select the tables
but as it tries to import them i get the following error:
ORA-03113: end-of-file on communication channel.
can anyone help with this to?
thank you,
Jonathan.

Error conection dblink windows2000 to sql-serever 2008 with windows2008 R2

Hi,
I receive the error below after a select is issued
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 'LIBRA'. (SQL State: 28000; SQL Code: 18456)
ORA-02063: preceding 2 lines from a ICARO
I have 2 dblink working correctly to others servers with Windows 2003 ;
and all file adjustments ( tnsnames.ora, listener.ora, initICARO.ora ) are fine.
The odbc works if i use it from microsoft access database.
and if i change the odbc with other sqlserver the dblinlk works too.
Any idea?
Best Regards. 
Have a look at the error message:
Login failed for user 'LIBRA'. (SQL State: 28000; SQL Code: 18456)
=> Is the userid on the SQL Server really in capital letters? Commonly a SQL Server userid and password is case sensitive and thus you need to surround the username and password in the create database link statement to preserver the case.
Example when the SQL Server userid is in lower letters = libra:
create database link icaro connect to "libra" identified by "<pasword for userid libra>" using '<your tns alias>'; 
The create database link is correct is quoted and lower case. 
Do you still get the same error message?
If yes, please post the message (just to cross check) and the create database link statement. 
Yes,
create database link dbicaro
connect to "LIBRA" identified by "pass" using 'icaro'; 
The SQl Server claims the user LIBRA is not allowed to connect to the SQL Server database.
In your create database link statement you have "LIBRA" which means the user is in capital letters. Is this really correct? Please have a look at the SQL Server directly by querying: select name from sysusers
Or you can also post the details from a working database link by querying the dba_db_links or user_db_links views. 
Well,i missed one step, in this server I didn't add the user like my others sql-servers XD
Thanks.

Oracle 8.1.7 to SQL Server 7 using Generic Connectivity

I have been trying to set up a database link between Oracle and SQL Server, currently both on the same Windows NT machine.
I managed to create the link as follows:
CREATE PUBLIC DATABASE LINK ioi
CONNECT TO IOItest IDENTIFIED BY mba
USING 'hsodbcxtrad';
The link is created successfully.
I then try to run a query and get the following error :
select * from dealer#ioi
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: [Microsoft][ODBC SQL Server
Driver][SQL Server]Login failed for user '\'. (SQL State: 28000; SQL Code:
18456)
ORA-02063: preceding 2 lines from IOI
I have added a UID and PWD to the registry settings for the SQL Server DSN but this makes no difference. Do I need to specify a username and password somewhere else ?
Thanks
Hugh
Do you have all of your parameter files set up correctly?
You need to have a hsora file in the hs/admin directory for ioi for instance, initioiDB.ora. In this file you need to set up your hs init parameters. This is an example of what's in one of mine:
HS_FDS_CONNECT_INFO = Mapics
HS_DB_NAME = MAPICS
HS_DB_DOMAIN = WORLD
HS_FDS_TRACE_LEVEL = ON
The you need to have an entry in the tnsnames.ora file in the network/admin directory for hsodbcxtrad. The sid should be set to ioiDB.
You can find documentation on how to manage heterogenous services in the online documentation.
Managing Heterogenous Services Using Generic Connectivity
Hope this helps
Eric
I have been trying to set up a database link between Oracle and SQL Server, currently both on the same Windows NT machine.
I managed to create the link as follows:
CREATE PUBLIC DATABASE LINK ioi
CONNECT TO IOItest IDENTIFIED BY mba
USING 'hsodbcxtrad';
The link is created successfully.
I then try to run a query and get the following error :
select * from dealer#ioi
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: [Microsoft][ODBC SQL Server
Driver][SQL Server]Login failed for user '\'. (SQL State: 28000; SQL Code:
18456)
ORA-02063: preceding 2 lines from IOI
I have added a UID and PWD to the registry settings for the SQL Server DSN but this makes no difference. Do I need to specify a username and password somewhere else ?
Thanks
Hugh 
Thanks Eric,
Your comments confirm the steps I have taken. I now have connectivity - the SQL login problem occurred because I had selected Windows NT authentication instead of SQL authentication in the SQL DSN. Oracle also complained when I tried to run a query against the linked database because my HS_DB_NAME and HS_DB_DOMAIN values were in lower case.
Having resolved these issues, if I run a "select *" against the linked database, I am only given the columns which are NOT varchar. If I specify a varchar column name, I get an Oracle error:
ORA-00904: invalid column name
Any ideas would be welcome !
Thanks
Hugh
Try putting the column names in quotes - just make sure the case you type in is the same case that the column actually is in the sqlserver database.
Eric 
Yes, I've tried that but it makes no difference. Strange ...
Hugh,
Please check that the ODBC driver meets requirements listed in the Heterogeneous Connectivity Administrator's Guide
Vira

ERROR  CONNECTING NON ORACLE DATABASE

Hello,
I have a SQL Server Database and I need to link this DB to Oracle Discoverer.
I created a Data Source (DSN) linked to my SQL Server DB.
When I start Discoverer Admin, I'm able to connect to SQL Server using user/password#ODBC:MyDSN.
Then Discoverer asks me to create an EUL and it starts automatically creating all EUL views. After all views are created it starts populating those views and when the process is almost completed, I get the error message: "The following error occurred during installation-
Install failed, cause: Invalid column name 'suid'".
I check the file EULINST.LOG and I find this statement:
CREATE VIEW EUL4_ODBC_SCHEMAS AS SELECT name AS 'os_schema_name' FROM sysusers WHERE suid > 1 AND uid <> gid. This is causing the error.
When I go to the table sysusers in SQL Server, the column 'suid' indeed does not exist.
I am using Oracle Discoverer (Version 4.1.37).
please e mail me:alpmehmetengin#ttnet.net.tr
Thanks a lot.
Hello
I got an error like this , If your problem has been solved can you help me , an tell me the solution
Thanks Ehsan

OWB Connectivity to SQL Server

Hello,
I'm having trouble connecting OWB (10.2.0.1.31) to our SQL Server. After creating the DB Link (setting the access to a fixed user), we can connect over and execute queries against the linked server.
That being said, however, I'm having issues connecting to this server under OWB -- the error message that the system is coming back with is:
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 'ONYX_SA'. (SQL State: 28000; SQL Code: 18456)
ORA-02063: preceding 2 lines from OWB_346
the editor screen requires an input of both user name and password, but even if I put in the credentials used for the fixed user, I'm getting the message.
Any advise would be greatly appreciated.
thanks
Lawrence 
Does 'ONYX_SA' user has connection privileges into the sql server box and as well into the sql server database? I hope it does'nt exist. Check! 
Hi Zetabouy,
The onyx_sa user definitely has access to all the tables and views I would like to use since from SQL+ I can actually query all those tables.
At any rate, here's one for the books --- the password has to be encapsulated in double-quotes! I was reading another forum article and the recommended wrapping username and password around in double quotes (Re: SQL State: 28000; SQL Code: 18456 -error while connecting to SQL Server I tried that when recreating the database link, but it still didn't work.
finally, out of curiosity, I encapsulated the placed double quotes around the password in OWB's edit location, and that worked! I'd been banging my head over this for 2 weeks now and this eureka really made my day.
thanks for your suggestion.

Categories

Resources