I have three Microsoft SQL Servers 2005 Enterprise Edition.
We installed IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider version 10.124.107.200 on all of them.
We created the linked server PS_DWH using this provider to perform queries agains a DB2 database.
On one of those servers, I'm having issues to run the following query:
select * from openquery(ps_dwh,'select substring(abalph,2,4) from proddta.f0101')
It raises the following error:
OLE DB provider "IBMDA400" for linked server "ps_dwh" returned message "SQL0104: Token ,2 was not valid. Valid tokens: , FROM.
Cause . . . . . : A syntax error was detected at token ,2. Token ,2 is not a valid token. A partial list of valid tokens is , FROM. 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 ,2. 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.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
select substring(abalph,2,4) from proddta.f0101
" for execution against OLE DB provider "IBMDA400" for linked server "ps_dwh".
If I add spaces among the substring function's parameters, the query works:
select * from openquery(ps_dwh,'select substring(abalph, 2, 4) from proddta.f0101')
Does anybody know why?
Remembering that it works on the other two SQL Servers, it doesn't matter whether the spaces exist or not. Do I need to set something?