All geek questions in one place

"The database may not be activated or may be in a transition state"

I have this highly nested SQL code that works well in my SQL Server Express Express.
[code below]

However, when I transfer it to our preliminary test server (sql server 2000), it does not work unless I use fully resolved table references in sentences from each statement. What I can not do, because the database name depends on the installation in the field.

He gives me this error message:

Msg 913, Level 16, State 8, Line 14 Could not find the database identifier 102. The database is not yet activated or may be in the process of transition.

I found a thread in this [sql newsgroups] forum; which indicates an error that occurred as a result of installing an unloaded SQL server.

Microsoft Support Link: This will work for ~ 10 minutes or until Microsoft changes the location of its site.

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.

, , sql. ( . , .) sql; , , . , GROUP BY. (, .)

, , ?

   [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]

+3
3

, , . sql DECLARE @foo TABLE , INSERT INTO. .

SQL Server 2000; -. , .

.

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]

+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]

, - , . , , , , , temp.

- , , , .

+1

, (NOLOCK) , , . , .

, CTE . .

0

Source: https://habr.com/ru/post/1714982/

More articles:


All Articles