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

Question

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

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

它给我这个错误消息：

Msg 913，Level 16，State 8，Line 14 找不到数据库 ID 102。数据库可能尚未激活或可能正在转换。

我在此找到了一个线程 [sql 新闻组] 论坛； 这表明由于未修补的 sql server 设置而导致错误。

Microsoft 支持链接：这将工作大约 10 分钟，或者直到微软更改其网站文档位置。

从链接：

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.

文章表明您可以通过修补 sql 来解决此问题服务器安装。 （这太容易了。而且，不可能，因为我们不能强制客户端更新。）或者通过简化 sql 语句； 就我而言，这可能意味着减少派生表的数量。 具体来说，看起来最后一个带有 GROUP BY 子句就是问题所在。 （也许，派生表太远了。）

那么，我该如何简化这个查询而不破坏它？

谢谢

使用[我的数据库]

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

您可以将内部派生表之一分解为临时表吗？ IE：

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]

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

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

Answer 2

所以，这就是我最终所做的。 我拆分了内部 sql 语句，并使用 DECLARE @foo TABLE 语法创建一个临时表，并用 INSERT INTO 填充该临时表。 然后我使用临时表进行最终选择。

这个过程在我的 SQL Server 2000 测试环境中可以编译； 虽然我还没有开始我的 alpha 测试。 如果最终没有成功，我会回复。

谢谢。

DECLARE @cData TABLE (
    [Desc] VARCHAR(25),
    [Series] VARCHAR(40),
    [Manufacturer] VARCHAR(30),
    [Distributer] VARCHAR(30),
    [LastOrdr] CHAR(8),
    [Minimum] SMALLINT,
    [Qty] INT
)

INSERT INTO @cData (
    [Desc],
    [Series],
    [Manufacturer],
    [Distributer],
    [LastOrdr],
    [Minimum],
    [Qty]
)
    SELECT [pptype].[Desc],
            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,
            COALESCE(cStock.Recvd,0) AS LastOrdr,
            [ppType].Minimum,
            COALESCE(cQty.Qty,0) AS Qty
        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)
        ORDER BY [Desc],[Series],[Manufacturer],[Distributer],[Minimum]


SELECT [Desc],
    [Series],
    [Manufacturer],
    [Distributer],
    MAX(LastOrdr) AS LastOrdr,
    [Minimum],
    SUM(Qty) AS Qty
FROM @cData
GROUP BY [Desc],[Series],[Manufacturer],[Distributer],[Minimum]
ORDER BY [Desc],[Series],[Manufacturer],[Distributer],[Minimum]

Answer 3

您可能知道，但我建议在表调用上使用 (NOLOCK) 提示，因为您的查询涉及许多表，因此可能会出现表锁定。 让我知道这是否有帮助。

另外我认为你可以 CTE 来回答你的问题。 试一下。