DB2 linked server from sql server 2005

[barmatt80]

Ok, I do not know what I am doing wrong. I have linked servers setup from SQL server 2005 to db2 using odbc dsn.

I open sql server management studio and try to call a stored procedure on db2 system.

Linked server name: PADEV
Database name: PADEV1
Database owner: PADEVM
Stored procedure name: PAWHAT

I type in for new query:

EXEC PADEV.PADEV1.PADEVM.PAWHAT

Errors:

OLE DB provider "MSDASQL" for linked server "PADEV" returned message "[IBM][CLI Driver][DB2] SQL0969N  There is no message text corresponding to SQL error "-114" in the message file on this workstation.  The error was returned from module "DSNXEVPN" with original tokens "PADEV".  SQLSTATE=42961
".
OLE DB provider "MSDASQL" for linked server "PADEV" returned message "[IBM][CLI Driver][DB2] SQL0104N  An unexpected token "1" was found following "".  Expected tokens may include:  "( END SET CALL DROP FREE HOLD LOCK OPEN PARM ALTER BEGIN CLOSE ".  SQLSTATE=42601

The stored procedure needs one variable input and outputs 9 variables. I figured in the above call knowing i left the variables off it would say, expecting EmpID(the input variable) and not supplied but i keep getting the above error.

What am i doing wrong? Something wrong in the link server setup, in my sql statement or on the db2 side?

Thanks again. Greatly appreciate it.

[ck9663]

I have not done this yet, so this answer would be more of theoretical, than actual.

The error is coming from your DB2. Your SQL Server is trying to interpret it as much as it can but can't seems to. That's why you're getting a some sort of generic error.

I also don't know about DB2. But if it's like SQL Server that you can set default values to parameters, you can check it inside the DB stored proc and return the necessary error for proper handling.

-- CK

[barmatt80]

Thanks CK.....I have been side tracked with some other work and just now getting able to get back to it. I'll see what I can find out.

[barmatt80]

well no luck.

I still get the error.

There was a permission problem, but that is fixed now. The app development team said they can call the stored procedure using cold fusion like they always do.

So i am still trying to hash out the issues in calling the stored procedure via linked servers in sql server.

The server admin says the connection is set up fine, I have asked him 2 times to confirm and it is as he says.

[barmatt80]

I THINK i have made some progress.

EXECUTE ('CALL PAWHAT ', 9999) AT PADEV

That produces this error:

OLE DB provider "MSDASQL" for linked server "PADEV" returned message "[IBM][CLI Driver][DB2] SQL0440N  No authorized routine named "PAWHAT" of type "" having compatible arguments was found.  SQLSTATE=42884

Still get an error, but it looks like it is atleast making the call, i just don't have the parameters correct.

The 9999 is my employee ID as the only input, with 9 output parameters(or values) returned.

I have tried putting " ", " " for all 9 but that doesn't work?

Any idea? I think it has to be treaded like a pass through query.

[ck9663]

EXEC() can pass a return status. But you might need to modify the DB2 to return it as a single string separated by comma.

Also you might be able to do a

INSERT INTO @TableVar
EXEC ()

Whatever the returned result of EXEC will be inserted on the @TableVar. Create your @TableVar with 9 columns.

-- CK

[barmatt80]

I'll give it a shot! Thanks!