servers

I have 3 SQL servers, lets call the s1, s2 and s3. I wanted to link s1 to s2
so I did it using the new linked server option in s2 and it worked great.
Now I want to link s1 to s3 so I open the new linked server option in s3 and
do the same thing, but when I try to run a query in s3 using a database in
s1 I get this error:
Error 7399: OLE DB Provider 'Exoledb.DataSource.1' reported an error. Quthen
tication failed.
OLEDB error trace [OLE/DB Provider 'Exoledb.DataSource.1' IDBInitialize:
:Initialize returned 0x80040e4d:
Authentication failed.].
So I did the same thing in both servers, one worked and one failed. How do I
fix the one that didn't work?
--
El que persevera insiste
Sergio Florez M.
Medelln, ColombiaHi
You may want to check the properties of each of the linked servers. The
authentication modes of the database servers (themselves) and the network
configuration of each of the servers (e.g are they both on the domain).
John
"Sergio Florez M." <sergioflorezm@.hotmail.com> wrote in message
news:%23hAfBxhVEHA.3512@.TK2MSFTNGP12.phx.gbl...
I have 3 SQL servers, lets call the s1, s2 and s3. I wanted to link s1 to s2
so I did it using the new linked server option in s2 and it worked great.
Now I want to link s1 to s3 so I open the new linked server option in s3 and
do the same thing, but when I try to run a query in s3 using a database in
s1 I get this error:
Error 7399: OLE DB Provider 'Exoledb.DataSource.1' reported an error.
Quthentication failed.
OLEDB error trace [OLE/DB Provider 'Exoledb.DataSource.1'
IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
So I did the same thing in both servers, one worked and one failed. How do I
fix the one that didn't work?
El que persevera insiste
Sergio Florez M.
Medelln, Colombia

server via ODBC and stored procedure

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ......

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe(chloe.crowder@.bl.uk) writes:
> We're trying to use call a stored procedure to update information in a
> remote Ingres database. We've linked the server, and can read
> information using SELECT * FROM OPENQUERY (........), but we can't
> find a suitable syntax for executing a procedure.
> Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
> string gives a message about not returning any columns - not surprising
> as there aren't any, and trying to execute the procedure more directly
> using:-
> EXECUTE abrs..vipdba.ats_reader_pi0 ......
> Gives the error
> Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
> [OLE/DB provider returned message: Parameter type cannot be determined
> for at least one variant parameter.]

Assuming that you are on SQL 2000:

Does the procedure have any "difficult" parameters?

First of all, I would examine whether there is an OLE DB provider
for Ingres, rather than using the MSDASQL provider.

If there is no OLE DB provider available, I would first try a parameterless
stored procedure. If this fails, then it seems that the ODBC driver
have problems to retrieve parameter information at all.

If there is a tool similar to Profiler on the Ingres side, you could
use that to see what calls the ODBC driver makes.

One thing that looks suspicious to me is that the third component is
empty, but I don't know Ingres, so this may be alright.

Unfortuantely, linked servers to other products can be a bit of trial
and error. There is a generic OLE DB layer which you have little control
over.

If you are on SQL 2005, there may be an easy way out. To wit you
can say:

EXEC('ingres-SQL here') AT abrs

to send a pass-through query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx