Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
--
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.