SQL Server / Oracle链接 - Julian日期错误

I have a client who has a SQL Server 2000 database linked to an Oracle 8i database. They have dozens of views in the SQL Server 2000 database which reference the Oracle database, often with simple syntax such as:

我有一个客户端,其SQL Server 2000数据库链接到Oracle 8i数据库。它们在SQL Server 2000数据库中有许多引用Oracle数据库的视图,通常使用简单的语法,例如:

SELECT *
FROM  SERVER..DB.TABLE

These views (and the sprocs which reference them) have worked for YEARS without issue. Suddenly, this morning, some (but not all) of them are failing with the following error:

这些视图(以及引用它们的sprocs)在没有问题的情况下工作了几年。突然,今天早上,他们中的一些(但不是全部)都失败了以下错误:

Server: Msg 7330, Level 16, State 2, Procedure SALES_ORDER_HEADERS, Line 7
Could not fetch a row from OLE DB provider 'MSDAORA'. 
[OLE/DB provider returned message: ORA-01854: julian date must be between 1 and 5373484]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e07].

When we select data using the four-part syntax above (the same syntax as the view has defined), the queries succeed without issue and the . However, we cannot create a view on that syntax, we cannot edit the existing views (in the designer), and we cannot figure out why directly querying the linked table works, but using it via a query fails.

当我们使用上面的四部分语法选择数据时(与视图定义的语法相同),查询成功没有问题和。但是,我们无法创建该语法的视图,我们无法编辑现有视图(在设计器中),我们无法弄清楚为什么直接查询链接表有效,但通过查询使用它失败。

My company didn't build the system, nor do we generally maintain it -- the client has an internal dev team which works on it, but they asked for our troubleshooting help, and we're just as stumped as they are, so I figured I'd ask here.

我的公司没有构建系统,我们也没有维护它 - 客户端有一个内部开发团队可以使用它,但他们要求我们的故障排除帮助,我们就像他们一样难倒,所以我我想在这里问。

Anyone have any clue why we'd see this behavior -- and more importantly, how to correct it? (Short of upgrading; not an option at this point.) As a temporary workaround, we've discovered that using OPENQUERY does function, but it is of course dog slow. We'd like a solution which doesn't involve OPENQUERY, if possible.

任何人都有任何线索,为什么我们会看到这种行为 - 更重要的是,如何纠正它? (没有升级;此时不是一个选项。)作为临时解决方法,我们发现使用OPENQUERY确实起作用,但它当然是狗慢。如果可能的话,我们想要一个不涉及OPENQUERY的解决方案。

In response to the comments: There is no ORDER BY defined in the views. There may be in the sprocs, but I'm not certain. (I just saw this database for the first time about 20 minutes before posting the question. :) )

响应注释:视图中没有定义ORDER BY。可能在sprocs中,但我不确定。 (我在发布问题之前大约20分钟才第一次看到这个数据库。:))

2 个解决方案

#1

I think a change on data have happened that not happened on years. I found something similar years ago, I was developing a critical application module, testing, testing, testing for a lot of use-cases and all fine, and the day on production a non-tested pattern was introduced! Incredible but true!

我认为数据的变化发生在多年来没有发生过。几年前我发现了类似的东西,我正在开发一个关键的应用程序模块,测试,测试,测试很多用例,并且一切正常,并且在生产当天引入了非测试模式!令人难以置信但真实!

Because that change on data something wrong is giving to some kind of TO_CHAR(xx, 'J') call. For example, zero or negative data.

因为对数据的更改有些不对,所以给某种类型的TO_CHAR(xx,'J')调用。例如,零或负数据。

Because a lot of calls are falling may be is a base table/column common to all.

因为许多调用都在下降,可能是所有人共同的基表/列。

Actions:

Activate trace for the session in Oracle (you can do it for an active session and for an error number).

在Oracle中激活会话的跟踪(您可以为活动会话和错误号执行此操作)。

Look for "common denominator" tables/columns and then for strange values.

查找“公分母”表/列,然后查找奇怪的值。

#2

Is there "ORDER BY" in your views, this could be the cause.

您的观点中是否有“ORDER BY”,这可能是原因。

#1