LINKED server problem..

Hi All,
Here is the problem:
I want to link my sql server 2000 with oracle server and i used Linked
Servers feature under Security in SQL Server Enterprise Manager. I succeed t
o
configure and can view all the table name (not content) whihc belong in
Oracle database.
So, i try to use Query Analyser to pull some data from the linked server.
After i run, here is my error message:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Any ideas? Please advice.
thank you in advance.
regards,
Ocurnos.Any idea expert?Help needed.
thanks
"Daniel" wrote:

> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||Any idea expert?Help needed.
thanks
"Daniel" wrote:

> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Ex
ecute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||I don't think this is security related since you can establish the initial
connection without error. You might try using the Microsoft OLEDB Provider
for Oracle instead of the Oracle driver.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:76511FDC-6852-48A3-BC5E-E6B758AEDC84@.microsoft.com...[vbcol=seagreen]
> Any idea expert?Help needed.
> thanks
>
> "Daniel" wrote:
>|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.|||you'd want to start here:
http://support.microsoft.com/kb/280106
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:0475374F-7DA1-441B-B30E-4BDC7295149D@.microsoft.com...
> Hi All,
> Here is the problem:
> I want to link my sql server 2000 with oracle server and i used Linked
> Servers feature under Security in SQL Server Enterprise Manager. I succeed
> to
> configure and can view all the table name (not content) whihc belong in
> Oracle database.
> So, i try to use Query Analyser to pull some data from the linked server.
> After i run, here is my error message:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
> ICommandText::Execute
> returned 0x80040155].
> Any ideas? Please advice.
> thank you in advance.
> regards,
> Ocurnos.

Linked server connection

I use a system with one main server (SQL 2005) and three other remote linked
servers. I need to know if one of remote server is running or not to avoid m
y
procedure fails. My procedure contains remote query and distributred
transaction. If one of the remote server is not running and i know, i can
avoid failure of my procedure.
How can i know remote server state (is running or not)?
ThanksYou could do a simple SELECT on a table on linked server. See if you get a
response
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> I use a system with one main server (SQL 2005) and three other remote
linked
> servers. I need to know if one of remote server is running or not to avoid
my
> procedure fails. My procedure contains remote query and distributred
> transaction. If one of the remote server is not running and i know, i can
> avoid failure of my procedure.
> How can i know remote server state (is running or not)?
> Thanks|||If i run a SELECT i've got this error:
...
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
I don't want my procedure fails with error if one server is not running.
How can i catch this error or knowing if my server is running or not?
Thanks
"Jack Vamvas" wrote:

> You could do a simple SELECT on a table on linked server. See if you get a
> response
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:A363D6C0-C800-4516-8182-74AE61278D04@.microsoft.com...
> linked
> my
>
>|||Trap the exception in a TRY...CATCH block.
You could also design a CLR procedure to check for servers via SMO.
ML
http://milambda.blogspot.com/|||I need to do this all in my SQL stored procedure, not from a C# class or cod
e.
I've got a sp that run query on linked server, begin a distributed
transaction and inf ok commit. If one of the linked server is not running th
e
SP fails. I need to control this and to need to know if the server runs
before run the query and the transaction, but inside my SP.
Tanks.
"ML" wrote:

> Trap the exception in a TRY...CATCH block.
> You could also design a CLR procedure to check for servers via SMO.
>
> ML
> --
> http://milambda.blogspot.com/