MS SQL Server中的链接Informix表是否忽略条件? [英] Linked Informix table in MS SQL Server ignoring criteria?

问题描述

我在MS SQL Server 2008r2中链接的Informix表有问题.当我查询此表时，似乎忽略了我要传递给它的一些条件，但没有其他条件.例如，如果我在rowdate字段上放置条件，则执行计划的远程查询部分不会显示任何WHERE子句，但是如果我在其他字段(例如ACD)上放置条件，则它会显示.

I’m having a problem with a linked Informix table in MS SQL Server 2008r2. When I query this table, it seems to ignore some of the criteria I’m passing to it but not others. For example if I put a condition on the rowdate field the remote query part of the execution plan does not show any WHERE clause but if I put criteria on another field such as ACD it does show.

似乎在rowdate字段上没有通过任何条件，但在所有其他条件上都通过了.

It seems it does not pass any criteria on the rowdate field but does on all others.

我知道该字段在Informix端被索引.如果可以帮助我链接的表来自Avaya CMS，并且通过OpenLink ODBC驱动程序进行了链接.

I know the field is indexed on the Informix side. If it helps the table I’m linking is from Avaya CMS and it is linked via the OpenLink ODBC driver.

据我所知，它是Informix Dynamic Server 2000，它在Solaris上.该列显示为正确的DATE数据类型.我尝试通过以下标准:"2010-08-03 00:00:00"，"2010-08-03"，CONVERT(日期，"2010-08-03")和其他一些变体.当数据返回到SQL Server时，其格式为yyyy-mm-dd.

As far as I know it is Informix Dynamic Server 2000 and it is on Solaris. The column comes up as a DATE data type which is correct. I have tried passing the criteria as ‘2010-08-03 00:00:00’, ‘2010-08-03’, CONVERT(date,’2010-08-03’) and a few more variations. When the data is returned to SQL server it is in the format yyyy-mm-dd.

当我查看执行计划时，我可以看到带有所有其他条件的远程查询，后跟仅用于rowdate字段的过滤器.

When I view the execution plan I can see the remote query with all the other criteria followed by a filter for only the rowdate field.

我知道rowdate已建立索引，并且驱动程序通常会在我们在其他应用程序(业务对象和MS Access)中使用该信息时传达这些信息，并且它们没有问题

I know that rowdate is indexed and that the driver does normally communicate that information as we use it in other applications (Business objects and MS Access) and they don’t have a problem

推荐答案

我设法弄清楚了，但这是有史以来最奇怪的事情.我走了以不同格式传递日期的路线.我的默认设置是使用正常的YYYY-MM-DD当然不能使用，因此我尝试了YYYY-MMM-DD，仍然没有.仔细研究了组合之后，我发现了一个适用于Mmm-DD-YY的组合，它一定就是这样！ SEP-21-2010无法正常工作，但2010年9月21日.

I managed to figure it out but it is the strangest thing ever. I went down the route of passing the date in different formats. My default is to use the normal YYYY-MM-DD that of course did not work so I tried YYYY-MMM-DD, still nothing. After going through LOTS of combinations I found one that works Mmm-DD-YY and it has to be exactly that! SEP-21-2010 wont work but Sep-21-2010.

我想知道这是否只是Informix奇怪的挂断或驱动程序中的某些内容，无论如何仍然有效.

I wonder if this is just a strange hang up from Informix or something in the driver, anyway it works.

在旁注中，有没有人注意到来自美国的人写上日期月份，年份年份有多奇怪?停下来想一想，您是否将数字2410表示为四百，十，两千"?最好的部分是试着问自己，美国独立日是哪一天?大多数美国人说:这很容易让您感到生气，这是7月4日"，一天(一年)，这是他们唯一能正确说出独立日期的日期.我将留给SO社区看看其中的讽刺意味

On a side note has anyone noticed how strange it is that people from the America write the date month, day year? Stop and think about it for a second, do you say the number 2410 as "Four hundred, ten, two thousand"? The best part about it is try asking yourself this, what day is American independence day? Most Americans say "That’s easy you limey person it is the 4th of July" hmmmm day month (year), the only date they say round the right way is the date they got their independence. I will leave it up to the SO community to see the irony in that

这篇关于MS SQL Server中的链接Informix表是否忽略条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！