467,134 Members | 983 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

DB2 linked server from sql server 2005

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:
Expand|Select|Wrap|Line Numbers
Expand|Select|Wrap|Line Numbers
  1. 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
  2. ".
  3. 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.
Sep 29 '08 #1
  • viewed: 4395
6 Replies
Expert 2GB
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
Sep 29 '08 #2
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.
Oct 2 '08 #3
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.
Oct 9 '08 #4
I THINK i have made some progress.

Expand|Select|Wrap|Line Numbers
That produces this error:

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 9 '08 #5
Expert 2GB
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


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

-- CK
Oct 9 '08 #6
I'll give it a shot! Thanks!
Oct 9 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by RandyMartin | last post: by
6 posts views Thread by pb648174 | last post: by
6 posts views Thread by Neil | last post: by
reply views Thread by cliffeh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.