尝试使用OPENQUERY通过sql server linked server从DB2（V4R5M0）中选择x行时出错 [英] Error trying to Select x rows from DB2 (V4R5M0) via sql server linked server using OPENQUERY

问题描述

我有一个从SQL Server 2008 R2的链接服务器到使用OLE DB提供程序IBMDA400的DB2数据库（V4R5M0）

I have a Linked Server from SQL Server 2008 R2, to a DB2 Database (V4R5M0) using OLE DB provider "IBMDA400"

链接服务器检测

EXEC master.dbo.sp_addlinkedserver 
     @server = N'JTEST', @srvproduct=N'IBM OLE DB Provider for DB2', 
     @provider=N'IBMDA400', @datasrc=N'TestName'

工作正常：

SELECT * FROM OPENQUERY(JTEST, 'Select * from QSYS2.SYSCOLUMNS')

但以下语句会产生错误：

But the following statement produces an error:

SELECT * FROM OPENQUERY(JTEST, 'Select * from QSYS2.SYSCOLUMNS FETCH FIRST 10 ROWS ONLY')

错误

LE DB提供程序IBMDA400用于链接服务器JTEST返回消息
SQL0199：关键字FETCH没有预期有效的令牌：FOR WITH ORDER
UNION OPTIMIZE。原因...。：关键字FETCH不是ex在这里检查
。在关键字FETCH处检测到语法错误。部分列表
的有效令牌是FOR WITH ORDER UNION OPTIMIZE。此列表假定
该语句与意外的关键字正确无误。错误
可能早于该语句，但语句的语法似乎是
才能生效，直到此为止。恢复。 。 。 ：检查指定关键字区域中的SQL
语句。冒号或SQL
分隔符可能会丢失。当用作名称时，SQL要求将保留字分隔为
。更正SQL语句并再次尝试
请求。消息7321，级别16，状态2，行1发生错误
，同时准备查询Select * from QSYS2.SYSCOLUMNS FETCH FIRST $ b $对于OLE DB提供者IBMDA400为
链接服务器JTEST执行b 10 ROWS ONLY。

LE DB provider "IBMDA400" for linked server "JTEST" returned message "SQL0199: Keyword FETCH not expected. Valid tokens: FOR WITH ORDER UNION OPTIMIZE. Cause . . . . . : The keyword FETCH was not expected here. A syntax error was detected at keyword FETCH. The partial list of valid tokens is FOR WITH ORDER UNION OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "Select * from QSYS2.SYSCOLUMNS FETCH FIRST 10 ROWS ONLY" for execution against OLE DB provider "IBMDA400" for linked server "JTEST".

这是因为这个版本的DB2不支持FETCH FIRST X ROWS ONLY？但是有没有办法在这个版本中只选择一个有限的记录集？

I think it's because FETCH FIRST X ROWS ONLY is not supported in this version of DB2? But is there any way of selecting only a limited record set in this version?

推荐答案

不需要FETCH FIRST需要ORDER BY吗？否则，它会得到哪些10行？

Wouldn't FETCH FIRST need an ORDER BY? Otherwise, which 10 rows would it get?

就我在DB2中可以看到SQL文档，它是支持当前和较旧的版本，虽然它没有声明（并且不清楚）如果ORDER BY是必须的FETCH FIRST

As far as I can see in the DB2 SQL docs, it is supported on current and older versions, although it doesn't state (and it not clear) if ORDER BY is mandatory with FETCH FIRST

这篇关于尝试使用OPENQUERY通过sql server linked server从DB2（V4R5M0）中选择x行时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！