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

问题描述

我有一个使用 OLE DB 提供程序IBMDA400"从 SQL Server 2008 R2 到 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')

错误

链接服务器JTEST"的 LE DB 提供程序IBMDA400"返回消息SQL0199:不需要关键字 FETCH.有效标记:FOR WITH ORDER联盟优化.原因 .....: 关键字 FETCH 不是预期的这里.在关键字 FETCH 中检测到语法错误.部分名单有效令牌的数量为 FOR WITH ORDER UNION OPTIMIZE.此列表假设该语句在意外关键字之前是正确的.错误可能在语句的前面，但语句的语法似乎到此为止有效.恢复 ...: 检查 SQL在指定关键字的区域中声明.冒号或 SQL可能缺少分隔符.SQL 要求对保留字进行分隔当它们被用作名称时.更正 SQL 语句并尝试再次请求.".消息 7321，级别 16，状态 2，行 1 发生错误在准备查询Select * from QSYS2.SYSCOLUMNS FETCH FIRST10 ROWS ONLY" 用于针对 OLE DB 提供程序 "IBMDA400" 执行链接服务器JTEST".

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 链接服务器从 DB2 (V4R5M0) 中选择 x 行时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！