sql - “数据库可能尚未激活或可能正在转换”错误

Question

我有这个高度嵌套的 sql 语句，它在我的 sql server 2008 express 中运行良好。
[下面的代码块]

但是，当我将它移到我们的初步测试服务器 (sql server 2000) 时，它不起作用，除非我在每个语句的 from 子句中使用完全解析的表引用。我不能这样做，因为数据库名称因现场安装而异。

它给我这个错误信息:

消息 913，级别 16，状态 8，第 14 行找不到数据库 ID 102。数据库可能尚未激活或可能正在转换中。

我在这个 [sql newsgroups] 中找到了一个话题论坛;这表明未修补的 sql server 设置导致错误。

Microsoft 支持链接: This will work for ~10 minutes or until microsoft changes its website document locations.

来自链接:

You may receive a 913 error message when you run a query that meets the following conditions:
  

   -The query includes a JOIN clause that uses ANSI SQL-92 JOIN syntax.-The JOIN condition references a user-defined function.-The query includes a derived table.
  

The article indicates that you could resolve this by patching the sql server installation. (Which would be too easy. And, impossible, since we can't force client updates.) Or by simplifying the sql statement; which in my case probably means reducing the number of derived tables. Specifically it looks like the last one with the GROUP BY clause is the problem. (Perhaps, a case of, a derived table too far.)

So, how do I go about simplifying this query without breaking it in the process?

Thanks

USE [mydatabase]

SELECT [Desc],
    [Series],
    [Manufacturer],
    [Distributer],
    MAX(LastOrdr) AS LastOrdr,
    [Minimum],
    SUM(Qty) AS Qty
FROM (SELECT [pptype].[Desc],
            COALESCE(cStock.Serial,' ') AS Serial,
            COALESCE([misccode].Descript,' ') AS Series,
            COALESCE((SELECT vendors.vn_Name FROM [dbo].vendors WHERE vendors.Vn_id = [pptype].Mfg),' ') AS Manufacturer,
            COALESCE((SELECT vendors.vn_Name FROM [dbo].vendors WHERE vendors.Vn_id = [pptype].Distrib),' ') AS Distributer,
            [ppType].Minimum,
            COALESCE(cQty.Qty,0) AS Qty,
            COALESCE(cStock.Recvd,0) AS LastOrdr,
            [pptype].Trkser
        FROM (SELECT [Typeid], [Serial], [Series], MAX([Recvd]) AS Recvd FROM [dbo].[ppstock] WHERE [Invoice] != 'SETUP' GROUP BY [Typeid], [Serial], [Series]) cStock
            LEFT OUTER JOIN [dbo].[pptype] ON
                cStock.[Typeid] = [pptype].Typeid
            LEFT OUTER JOIN (SELECT [Typeid], [Serial], SUM([Qty]) AS Qty FROM [dbo].[pplocatn] GROUP BY [Typeid], [Serial]) cQty ON 
                cStock.[Typeid] = cQty.[Typeid] AND cStock.Serial = CASE
                    WHEN [dbo].EMPTY(cStock.Serial) = 1 THEN 'Do not match.' ELSE cQty.[Serial] END
            LEFT OUTER JOIN [dbo].[misccode] ON
                cStock.[Series] = [misccode].[Code] AND [misccode].[type] = 'SERIES'
    WHERE [dbo].EMPTY([pptype].Inactive) = 1 and
        (COALESCE(cQty.Qty,0) < [pptype].Minimum)) cData
GROUP BY [Desc],[Series],[Manufacturer],[Distributer],[Minimum]

Answer 1

您能否将其中一个内部派生表分解为临时表？即:

SELECT [Typeid], [Serial], [Series], MAX([Recvd]) AS Recvd
INTO #InnerTable1
FROM [dbo].[ppstock] WHERE 
[Invoice] != 'SETUP' GROUP BY [Typeid], [Serial], [Series]) cStock
                            LEFT OUTER JOIN [dbo].[pptype] ON
                                    cStock.[Typeid] = [pptype].Typeid
                            LEFT OUTER JOIN (SELECT [Typeid], [Serial], SUM([Qty]) AS Qty FROM [dbo].[pplocatn] GROUP BY [Typeid], [Serial]) cQty ON 
                                    cStock.[Typeid] = cQty.[Typeid] AND cStock.Serial = CASE
                                            WHEN [dbo].EMPTY(cStock.Serial) = 1 THEN 'Do not match.' ELSE cQty.[Serial] END
                            LEFT OUTER JOIN [dbo].[misccode] ON
                                    cStock.[Series] = [misccode].[Code] AND [misccode].[type] = 'SERIES'
            WHERE [dbo].EMPTY([pptype].Inactive) = 1 and
                    (COALESCE(cQty.Qty,0) < [pptype].Minimum


SELECT [Desc],
        [Series],
        [Manufacturer],
        [Distributer],
        MAX(LastOrdr) AS LastOrdr,
        [Minimum],
        SUM(Qty) AS Qty
FROM (SELECT [pptype].[Desc],
                        COALESCE(cStock.Serial,' ') AS Serial,
                        COALESCE([misccode].Descript,' ') AS Series,
                        COALESCE((SELECT vendors.vn_Name FROM [dbo].vendors WHERE vendors.Vn_id = [pptype].Mfg),' ') AS Manufacturer,
                        COALESCE((SELECT vendors.vn_Name FROM [dbo].vendors WHERE vendors.Vn_id = [pptype].Distrib),' ') AS Distributer,
                        [ppType].Minimum,
                        COALESCE(cQty.Qty,0) AS Qty,
                        COALESCE(cStock.Recvd,0) AS LastOrdr,
                        [pptype].Trkser
                FROM #InnerTable1) cData
GROUP BY [Desc],[Series],[Manufacturer],[Distributer],[Minimum]

这应该可行，因为内部表是您外部查询的基础。如果涉及连接，并且您是根据内部数据从其他表中进行选择，那么我会担心这两个查询之间发生数据更改的可能性，但由于您是从最内层连接中选择所有数据，使用临时表应该没有任何问题。

如果由于某种原因这不起作用，请发回，我会尝试对其进行更多调整以使其起作用。