Oracle from SQL analyser...

Oracle from SQL analyser...

Post by anand » Thu, 05 Sep 2002 18:05:23



How can I execute SQL statements in Oracle database, from SQL server
query analyser?

--
Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by anand » Thu, 05 Sep 2002 18:39:46


I know that this can be done using linked servers.  But what parameters
should I supply for creating the linked servers.  Thus, if my Oracle
database server is A, Username is B and password is C, how should this
be created.  What should be supplied in place of Provider and
Datasource?  How should I create the connection string?  When I tried to
do this, it was always giving either of the two errors.

1.  [OLE/DB provider returned message: [Microsoft][ODBC driver for
    Oracle][Oracle]ORA-01017: invalid username/password; logon denied]

2.  [OLE/DB provider returned message: ORA-12560: TNS:protocol
    adapter error]

I don't have any idea why these are coming?

Please help...

--
Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by Daniel Morga » Thu, 05 Sep 2002 23:57:51



> I know that this can be done using linked servers.  But what parameters
> should I supply for creating the linked servers.  Thus, if my Oracle
> database server is A, Username is B and password is C, how should this
> be created.  What should be supplied in place of Provider and
> Datasource?  How should I create the connection string?  When I tried to
> do this, it was always giving either of the two errors.

> 1.  [OLE/DB provider returned message: [Microsoft][ODBC driver for
>     Oracle][Oracle]ORA-01017: invalid username/password; logon denied]

> 2.  [OLE/DB provider returned message: ORA-12560: TNS:protocol
>     adapter error]

> I don't have any idea why these are coming?

> Please help...

> --
> Posted via http://dbforums.com

Oracle's native connection string is username slash password at-sign
service_name as in the following:


How one might pass that from SQL Server is outside of my experience.

Daniel Morgan

 
 
 

Oracle from SQL analyser...

Post by lummer » Fri, 06 Sep 2002 06:49:42


anandk,

setup oracles client side software on the machine running
mssqlserver. then you can define an odbc source pointing
to your oracle-server.
the odbc source you can use with a linked server that you
define with
sp_addlinkedserver
Look up the syntax, hints and examples (including an oracle
example) in books online(the mssql-help)
  "Transact SQL Syntax"->"System Stored Procedures"
->"sp_addlinkedserver"
cheers,

     robert

 
 
 

Oracle from SQL analyser...

Post by Dan Guzma » Fri, 06 Sep 2002 12:14:46


Quote:> Thus, if my Oracle
> database server is A, Username is B and password is C, how should this
> be created.  What should be supplied in place of Provider and
> Datasource?

The provider is 'MSDAORA' and the Data Source is your Oracle service
name.  Example below.  Since you are getting a ORA-01017 message, I
believe you've successfully setup the server but not the login.

EXEC sp_addlinkedserver




EXEC sp_addlinkedsrvlogin




--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:

> I know that this can be done using linked servers.  But what
parameters
> should I supply for creating the linked servers.  Thus, if my Oracle
> database server is A, Username is B and password is C, how should this
> be created.  What should be supplied in place of Provider and
> Datasource?  How should I create the connection string?  When I tried
to
> do this, it was always giving either of the two errors.

> 1.  [OLE/DB provider returned message: [Microsoft][ODBC driver for
>     Oracle][Oracle]ORA-01017: invalid username/password; logon denied]

> 2.  [OLE/DB provider returned message: ORA-12560: TNS:protocol
>     adapter error]

> I don't have any idea why these are coming?

> Please help...

> --
> Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by anand » Fri, 06 Sep 2002 13:20:18


Thanks Daniel,

I tried the same statements as you say.  The remote logins are
created now.

Following are the SQL statements which I executed (Server name =
'ANAND'; User name = 'srvltadm'; Password = 'srvltadm')




But, when I try to access the table (say 'Table1') as

SELECT * FROM [TEST].[TABLE1]

it gives the following error : Invalid object name 'TEST.TABLE1'

When I try

SELECT * FROM [TEST].[ANAND].[TABLE1]

again, it gives the following error : Invalid object name
'TEST.ANAND.TABLE1'

What could be wrong?  Are the linked server and the logins
created properly?

One more doubt? How can I drop remote logins from the 'master' database
of SQL server?  Should I use 'sp_droplinkedsrvlogin' or
'sp_dropremotelogi'.  Both of them don't work anyway...

Thanks a lot to all,

--
Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by John Bel » Fri, 06 Sep 2002 15:13:05


Hi

Try:

SELECT *
FROM ANAND..MARY.TABLE1

Where MARY is the Oracle user that owns the table

John



>Thanks Daniel,

>I tried the same statements as you say.  The remote logins are
>created now.

>Following are the SQL statements which I executed (Server name =
>'ANAND'; User name = 'srvltadm'; Password = 'srvltadm')





>But, when I try to access the table (say 'Table1') as

>SELECT * FROM [TEST].[TABLE1]

>it gives the following error : Invalid object name 'TEST.TABLE1'

>When I try

>SELECT * FROM [TEST].[ANAND].[TABLE1]

>again, it gives the following error : Invalid object name
>'TEST.ANAND.TABLE1'

>What could be wrong?  Are the linked server and the logins
>created properly?

>One more doubt? How can I drop remote logins from the 'master' database
>of SQL server?  Should I use 'sp_droplinkedsrvlogin' or
>'sp_dropremotelogi'.  Both of them don't work anyway...

>Thanks a lot to all,

 
 
 

Oracle from SQL analyser...

Post by anand » Fri, 06 Sep 2002 22:39:31


The same statements worked, but with a SYSTEM remote login.  Now, I have
a different problem.  While querying some tables, I get the following
error.  Some others do work without any problems...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: Oracle error occurred, but error
message could not be retrieved from Oracle.]
[OLE/DB provider returned message: Data type is not supported.]

WHen I checked the columns in the table, I found there is an 'ntext'
column which I suspect may be causing the problem.  Now, when I cast the
above 'ntext' column to nvarchar (100)** like

select cast (COL1 as nvarchar (100)) from [TEST1]..[DBADMIN].[TABLE1]

still it doesn't work.

Interestingly, selecting only one column ('int' type) from the same
table, also gives the same error as above; while it would work in any
other tables without 'ntext' columns...

What is the solution?

Thanks for your help..

**Note that 'nvarchar' columns get selected without errors from tables
which don't have any 'ntext' columns.

--
Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by Dan Guzma » Sat, 07 Sep 2002 12:10:46


AFAIK, ntext is a SQL Server data type, not an Oracle one.  The
Microsoft OLEDB Provider for Oracle has some limitations with LOB data
types with later Oracle versions which will result in the symptoms you
describe.

As a workaround, you might try creating an Oracle view that eliminates
the incompatible column(s).  The link below details known limitations of
the driver.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q244661&

--
Hope this helps.

Dan Guzman
SQL Server MVP


Quote:

> The same statements worked, but with a SYSTEM remote login.  Now, I
have
> a different problem.  While querying some tables, I get the following
> error.  Some others do work without any problems...

> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Oracle error occurred, but error
> message could not be retrieved from Oracle.]
> [OLE/DB provider returned message: Data type is not supported.]

> WHen I checked the columns in the table, I found there is an 'ntext'
> column which I suspect may be causing the problem.  Now, when I cast
the
> above 'ntext' column to nvarchar (100)** like

> select cast (COL1 as nvarchar (100)) from [TEST1]..[DBADMIN].[TABLE1]

> still it doesn't work.

> Interestingly, selecting only one column ('int' type) from the same
> table, also gives the same error as above; while it would work in any
> other tables without 'ntext' columns...

> What is the solution?

> Thanks for your help..

> **Note that 'nvarchar' columns get selected without errors from tables
> which don't have any 'ntext' columns.

> --
> Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by anand » Sat, 07 Sep 2002 15:22:49


Thanks,Dan for the link...

It was indeed useful. So, that lefts us in a lurch. We can't use the SQL
server to access an Oracle database, seamlessly. The idea was to use
Query analyser of SQL server to retrieve the data from an Oracle
database, since the Oracle's own sqlplus seemed highly unusable to me
(Windows users:-)...

The problem with SQLPlus is that formatting of results is highly
difficult; for example, I still don't know how to restrict the column
widths of the retrieved results. I have a table which has 10 columns,
the third columns has about 1000 characters in it. While retrieving
using query, it gives the first 3 columns and that is it. It doesn't
display further columns. How can set this right?

BTW, is there any freely available tool, which can do this for me ie.,
where I can write my SQL statements and execute on an Oracle database,
but returns me the results in much formatted way;-)

Thanks again for the help

--
Posted via http://dbforums.com

 
 
 

Oracle from SQL analyser...

Post by Dan Guzma » Sat, 07 Sep 2002 23:52:40


I agree that SQL*Plus is fairly arcane but I believe it provides
considerable control over formatting and can probably address your
Oracle query column width requirement.  However, this is a bit OT for
this newsgroup so you'll probably get more help in an Oracle forum.
I've only used SQL*Plus for DBA tasks.

IMHO, reporting is best handled by a specialized tool or application
program.  There are a number of good reporting products for Windows
which can handle heterogeneous data sources.  Crystal Reports, for
example,  is included with many of the .NET developer products.  I'm not
familiar with free ones though.  You'll probably get what you pay for.

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:

> Thanks,Dan for the link...

> It was indeed useful. So, that lefts us in a lurch. We can't use the
SQL
> server to access an Oracle database, seamlessly. The idea was to use
> Query analyser of SQL server to retrieve the data from an Oracle
> database, since the Oracle's own sqlplus seemed highly unusable to me
> (Windows users:-)...

> The problem with SQLPlus is that formatting of results is highly
> difficult; for example, I still don't know how to restrict the column
> widths of the retrieved results. I have a table which has 10 columns,
> the third columns has about 1000 characters in it. While retrieving
> using query, it gives the first 3 columns and that is it. It doesn't
> display further columns. How can set this right?

> BTW, is there any freely available tool, which can do this for me ie.,
> where I can write my SQL statements and execute on an Oracle database,
> but returns me the results in much formatted way;-)

> Thanks again for the help

> --
> Posted via http://dbforums.com