'Database may not be activated yet or may be in transition' error

Question

I have this heavily nested sql statement which works well in my sql server 2008 express.
[code block below]

However, when I move it over to our preliminary test server (sql server 2000) it doesn't work unless I use fully resolved table references in the from clauses of each statement. Which I can't do as the database name varies by installation in the field.

It gives me this error message:

Msg 913, Level 16, State 8, Line 14 Could not find database ID 102. Database may not be activated yet or may be in transition.

I've found a thread in this [sql newsgroups] forum; which suggests an error resulting from an unpatched sql server setup.

Microsoft support link: This will work for ~10 minutes or until microsoft changes its website document locations.

From the link:

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

You may be aware, but I would suggest using (NOLOCK) hint on table calls, because your query involves many tables there is possible chances for a table lock. Let me know if this helps.

Also I think you can CTE for your query. Give a try.

Answer 2

Can you just break one of the inner derived tables into a temp table? 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]

This should work, since the inner table is all your basing your outer query on. If there were joins involved, and you were selecting from other tables based on inner data, then I'd be concerned about the possibility of data changes that happen between these two queries, but since you're selecting all your data from the innermost join, there shouldn't be any problems using a temp table.

If this won't work for some reason, please post back and I'll try to tweak it some more so it does.

Answer 3

So, this is what I ended up doing. I split out the inner sql statement and used the DECLARE @foo TABLE syntax to create a temporary table which I filled with the INSERT INTO. I then used the temporary table for the final select.

This procedure does compile in my SQL server 2000 test environment; although I haven't yet started my alpha testing. If it doesn't end up working out I will post back.

Thanks.

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]