主页 / user-20877

RoKa's questions

Martin Hope
RoKa
Asked: 2013-03-27 06:32:44 +0800 CST
  • 4

我在客户端的 SQL Server 2005 框上发生了一个奇怪的错误。我们为他们提供了一个数据视图，其中填充了一个带有 CTE 的查询，该 CTE 具有多个连接/子查询，对包含 XML blurb 的 NTEXT 字段执行一些文本值解释

我知道我们可以使用其他字段类型和/或方法，但这是有充分理由的，我希望我不必在这里解释。

问题是由于某种原因，此查询在 SQL Server 上创建了一个致命异常。

附上导致错误的实际T-SQL的业务端的浓缩版

declare @@dtDateStart datetime, @@dtDateEnd datetime
set @@dtDateStart = getdate()
Set @@dtDateEnd = dateadd(year, 1, @@dtDateStart)
;with  qItems
    (date_created, SYSTEM_ID, LegalEntity) as
(   select i.date_created,
        i.externalKey as SYSTEM_ID,
        case when i.HasLegalEntity > 0 then
            substring(i.message, charindex('<LegalEntity>',i.message)+13, charindex('</LegalEntity>',i.message)-charindex('<LegalEntity>',i.message)-13)
         else '' end as LegalEntity
    from (select *, charindex('<LegalEntity>', message) as HasLegalEntity from dbo.INT_Error_Queue) as i
    where i.date_created between @@dtDateStart and @@dtDateEnd)
select 
    QItems.date_created, 
    QItems.SYSTEM_ID, 
    Qitems.LegalEntity
from QItems
WHERE
(isnull(Qitems.LegalEntity, '') not in (select key_value from Special_app_Settings where key_name = 'EntityName'))

一开始怀疑是不是和where子句有关，因为[key_value]on[Special_app_Settings]是type nvarchar(4000)，[i].[message]on[INT_Error_Queue]是type [ntext]，不知道会不会报错。如果我删除 where 子句，则不会发生错误。

特此从 SQL Server 日志转储：

03/13/2013 10:30:24,Server,Unknown,A user request from the session with SPID 101 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
03/13/2013 10:30:24,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
03/13/2013 10:30:24,spid101,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
...
03/13/2013 10:30:20,spid101,Unknown,* BEGIN STACK DUMP:
03/13/2013 10:30:20,spid101,Unknown,*
03/13/2013 10:30:20,spid101,Unknown,* *******************************************************************************
03/13/2013 10:30:20,spid101,Unknown,SqlDumpExceptionHandler: Process 101 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
03/13/2013 10:30:20,spid101,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0039.txt
03/13/2013 10:30:20,spid101,Unknown,Using 'dbghelp.dll' version '4.0.5'

有谁知道为什么会发生这种情况？

sql-server-2005