468,140 Members | 1,129 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

Linked 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

Mar 10 '06 #1
1 9714
(ch***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 11 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Neil | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.