SQL State: 28000; SQL Code: 18456 -error while connecting to SQL Server 200

143430

I have set up everything according to what I think to be good instructions yet I still get this error.
-------------
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 'BTEST'. (SQL State: 28000; SQL Code: 18456)
-------------
I have tried the odbc connection and it works. It seems that Oracle is not sending the proper connection information like passwork.

anyone, any thoughts?

Much appreciated,
Ara

567269

when you created the db link did you specify the username/password in quotes?

create database link <link> connect to "btest" identified by "btest" using 'OracleIsBetterThanSQLServer';

That happens to me when I connect to sybase.

-Joe

143430

Joe, you are a genius! It worked. I can' thank you enough.

Best Regards,
Ara Jeknavorian

143430

Joe, do you have any experience in running SQL Server functions and procedures from Oracle. I can connect and do a SELECT on a SQL Server table, but nothing seems to work for executing a function or procedure. Any thoughts?

Ara

567269

I haven't used it myself, but it looks like functions are available:

Sections 4.6 and A 1:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14232/toc.htm

143430

I read through the above link and it is clear that user-defined functions and stored procedures are not supported. I could have misread but that is my current conclusion. Thanks for your help.

Best regards,
Ara

user1997992

Joe I love you !!!!
You have a friend in Madrid!!

MMEGE-Oracle

Iif you use HSODBC , you have a way to execute SQL SERVER procedure by using PL/SQL package:
DBMS_HS_PASSTHROUGH


Here an example of calling stored procedures saved in MS SQL Server
from an Oracle database via HSODBC.

1. Create the following stored procedure within MS SQL Server:
(For example, using the Enterprise Manager tool of SQL Server)
CREATE PROCEDURE InsertRows @start_value int
AS
declare @loop_counter int, @start_val int
set @start_val =@start_value-1
set @loop_counter=0
while (@loop_counter&lt;5)
begin
insert into mytable values (@start_val +1,"neue Zeile&
quot;)
print (@start_val)
set @start_val=@start_val +1
set @loop_counter= @loop_counter +1
END

2. Create the following table within MS SQL Server:

Table name: mytable
column1: int
column2: char(10)

3. If HS is not set up correctly, please follow the
description in Note 109730.1.

4. Create a database link in the Oracle database:

SQL> create database link sql7_odbc
connect to <SQL SERVER USERNAME>
identified by <SQL SERVER Password>
using '<HSODBC ALIAS>';

5. Calling stored procedures can be done by DBMS_HS_PASSTHROUGH
packages:

SQL>
DECLARE
c INTEGER;
BEGIN
c:[email protected]_ODBC
('execute InsertRows 800');
END;
/
commit;


NOTE: The commit at the end ends the transaction.
If the commit is missing a select at the MS SQL
Server waits until the transaction is rolled back
or committed.


Regards,

Mireille

696493

thanks

719923

Hi,

I did all the connections,but while creating the DB link i have to give the password as null.

So how to create a link if the sql server password is null.

MMEGE-Oracle

Hi,

If your password is null, here is an example:
CREATE DATABASE LINK dblink CONNECT TO "sa" IDENTIFIED BY " "
USING 'alias_name';

You have to use double quotes , blank, doublequotes for the password.

I hope it helps you.
Regards

Mireille