Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Msg 7321, Level 16, State 2, Line 1 occurs on only one of three servers

Posted on 2008-10-02
Last Modified: 2013-12-06
I have three Microsoft SQL Servers 2005 Enterprise Edition.
We installed IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider version 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?


Question by:Carlos_Felipe
LVL 26

Expert Comment

ID: 22626603
I'm just guessing here, does the problem SQL server have the same collation with the other two?

Accepted Solution

Carlos_Felipe earned 0 total points
ID: 22626919
Yes, they all have the sam collation.

I figured that out!

The linked server must be set to "Be made using this security context" with a specific remote login and password.

Thanks for your attention!


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
Windows 7 does not have the best desktop search built in. This is something Windows 7 users have struggled with. You type something in, and your search results don’t always match what you are looking for, or it doesn’t actually work at all. There ar…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question