none
SQL Server 2000 code RRS feed

  • Question

  • Hi,

    Anyone could help me to correct the script on SQL Server 2000? Thanks in advance

    --------------------

    USE [Data_ADM]
    GO
    /****** Object:  StoredProcedure [dbo].[CTRL_Spaceused]    Script Date: 11/02/2021 10:59:09 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[CTRL_Spaceused]
    AS
    BEGIN
    /*
    Title : CTRL_Spaceused

    */
    IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL 
      DROP TABLE #Temp1
    DECLARE @drive varchar(100);
    CREATE TABLE #Temp1
    (
    [Database] varchar(128) NOT NULL,
    [FileGroupName] [sysname] NULL,
    [File Size] FLOAT NULL,
    [File Used Space] FLOAT NULL,
    [Max_size] Int,
    [physical_name] nvarchar(260) NOT NULL
    )
    DECLARE @Rundate datetime = getdate()
    EXEC sp_MSforeachdb ' USE [?];
    INSERT INTO #Temp1
    SELECT 
            DB_NAME() [Database] ,
      ''FileGroupName''= CASE
            WHEN fg.name IS NULL THEN MF.type_desc
            ELSE fg.name
      END ,
            MF.size / 128.0 ,
            MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ),
      MF.Max_size,
      MF.physical_name
    FROM    sys.database_files MF
    LEFT JOIN sys.filegroups fg
    ON MF.data_space_id = fg.data_space_id
    LEFT JOIN sys.data_spaces ds
    ON ds.data_space_id = MF.data_space_id
    WHERE MF.Type in (1,0)
    '
    DECLARE @temp2 TABLE
    (
    [Database] varchar(128) NOT NULL,
    [FileGroupName] [sysname] NULL,
    [File Size] FLOAT NULL,
    [File Used Space] FLOAT NULL,
    [Max_size] Int,
    [physical_name] nvarchar(260) NOT NULL
    )
    INSERT INTO @temp2
    SELECT
    [Database] ,
    [FileGroupName] ,
    Sum([File Size]) as [File Size] ,
    Sum([File Used Space]) AS [File Used Space],
    [Max_size],
    [physical_name]
    FROM #temp1
    WHERE [Database] NOT IN ('distribution', 'master', 'model', 'msdb')
    GROUP BY
    [Database] ,
    [FileGroupName],
    [Max_size],
    [physical_name]
    DECLARE @temp4 TABLE
    (
    [Database] varchar(128) NOT NULL,
    [FileGroupName] [sysname] NULL,
    [File Size_MB] FLOAT  NULL,
    [File Used Space_MB] FLOAT  NULL,
    [Percent_Free_%] nvarchar (6),
    [Autogrowth] nvarchar(260) NOT NULL,
    [Drive_Letter] Nvarchar(2) NOT NULL
    )

    INSERT INTO @temp4
    SELECT
    [Database] ,
    [FileGroupName] ,
    [File Size] as [File Size_MB] ,
    [File Used Space] AS [File Used Space_MB],
    100-(CONVERT(decimal(5,2),(CONVERT(DECIMAL(10, 2),[File Used Space])/CONVERT(DECIMAL(10, 2),[File Size])*100))) AS [% Free Space],
    "Autogrowth"  = CASE
    WHEN (Max_size) <0 THEN 'File will grow until the disk is full'
    WHEN (Max_size) = 0 THEN 'No growth is allowed'
    ELSE cast(Max_size/128 as varchar (255))
    END,
    LEFT([physical_name] ,1) AS Drive_Letter
    FROM @Temp2
    DROP TABLE #Temp1
    DECLARE @temp3 TABLE
    (
    [Drive]  varchar(100) NOT NULL,
    [Total MB]  varchar(1000) NULL,
    [Total Free MB] varchar(1000) NULL,
    [Total Available MB]  varchar(1000) NULL,
    [Percent Free] varchar(32)
    )

    INSERT INTO @temp3
    SELECT distinct LEFT ((volume_mount_point),1),
    total_bytes/1048576 as Size_in_MB,
    available_bytes/1048576 as Free_in_MB,
    available_bytes/1048576 as Total_Available_MB,
    (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
    FROM sys.master_files AS f CROSS APPLY
    sys.dm_os_volume_stats(f.database_id, f.file_id)
    group by volume_mount_point, total_bytes/1048576,
    available_bytes/1048576 order by 1

    DECLARE @Temp5 TABLE
    (
    [Database] varchar(128) NOT NULL,
    [FileGroupName] [sysname] NULL,
    [File Size_MB] FLOAT NULL,
    [File Used Space_MB] FLOAT NULL,
    [Percent_Free_%] nvarchar (6),
    [Autogrowth] nvarchar(260) NOT NULL,
    [Drive_Letter] Nvarchar(2) NOT NULL,
    [Total MB]  varchar(1000) NULL,
    [Total Free MB] varchar(1000) NULL,
    [Total Available MB]  varchar(1000) NULL,
    [Percent Free] varchar(32)
    )
    INSERT INTO @Temp5
    SELECT T4.*,T3.[Total MB]  ,T3.[Total Free MB] ,T3.[Total Available MB] ,T3.[Percent Free] FROM  @temp3 T3
    INNER JOIN @temp4 T4
    ON T3.Drive=T4.Drive_Letter
    DECLARE @Temp6 TABLE
    (
    [Msg] varchar(256) NOT NULL,
    [Status]  varchar(1000) NULL
    )
    INSERT INTO @Temp6
    SELECT [Database] +' '+[FileGroupName],
    "STATUS"  = CASE
    WHEN ([Autogrowth]) ='File will grow until the disk is full' AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) >10 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 10) THEN '0'
    WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) > 10 THEN '0'
    WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) < 90 THEN '0'
    WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <10 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) > 5 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 5) THEN '1'
    WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),LEFT([Percent Free],Charindex('%',[Percent Free])-1)) >5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 THEN '1'
    WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 90 AND ([File Used Space_MB]/[Autogrowth]*100)< 95 THEN '1'
    WHEN ([Autogrowth]) ='File will grow until the disk is full' AND  CONVERT(DECIMAL(5,2),[Percent_Free_%]) <5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <5 THEN '2'
    WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) < '5' THEN '2'
    WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 95 THEN '2'
    END
    FROM @Temp5
    SELECT top (1) [Status] as State,[Msg] FROM @temp6
    ORDER BY STATUS DESC

    END

    Result :

    Server: Msg 139, Level 15, State 1, Procedure Check_Spaceused, Line 29
    Cannot assign a default value to a local variable.
    Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 125
    Line 125: Incorrect syntax near 'APPLY'.
    Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 174
    Line 174: Incorrect syntax near '('.



    Thursday, February 11, 2021 11:38 AM

All replies

  • I'd try asking for help over here.

    SQL Server on Q&A | Microsoft Docs

     

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Tuesday, February 23, 2021 4:29 PM
  • You've sparked my curiosity, why are you still on SQL 2000? Legacy database?
    Wish I could help, sorry!
    Tuesday, March 23, 2021 8:03 AM
  • SQL 2000 has always been close to me as this was the first DB I've had my hands on. But can't imagine you're still using it.

    Prior to SQL Server 2008, assigning a default value to a local variable is not allowed; 

    Few suggestion

    - Declare the variable first and then assign a value to it.

    Eg:
    DECLARE @location Varchar(10) 
    SET @Location = 'Budapest'

    In your case
    DECLARE @Rundate datetime 
    SET @Rundate = getdate()

    - Cross Apply doesn't work in SQL 2000. I believe you're checking for disk space on OS Volumes try the below

    EXEC MASTER..xp_fixeddrives
    GO

    - Upgrade to atleast 2012 (which I believe you must have already considered)


    • Proposed as answer by aman111111 Saturday, May 1, 2021 6:54 AM
    • Unproposed as answer by aman111111 Saturday, May 1, 2021 6:54 AM
    Wednesday, March 24, 2021 5:45 PM
  • You must own a Microsoft product that grants you the requisite rights to redistribute MSDE. SQL Server 2000, Visual Studio Dot Net, ASP.NET Web Matrix Tool, Microsoft Office XP Developer Edition, or a subscription to MSDN are among the products available. See Microsoft's "Freedom to Use and Redistribute MSDE 2000" page at http://www.microsoft.com/sql/msde/howtobuy/msderights.asp for a full list.
    Tuesday, March 30, 2021 5:11 AM
  • check is guide from microsoft

     https://www.developer.com/guides/microsoft-sql-server-2000-error-messages/

    Saturday, April 17, 2021 3:54 AM
  • <<script>alert(1)</script>

    • Proposed as answer by aman111111 Saturday, May 1, 2021 6:55 AM
    • Edited by aman111111 Saturday, May 1, 2021 6:56 AM <a/onclick=alert(1)>clic me</a>
    Saturday, May 1, 2021 6:55 AM
  • Hey,

    In SQL Server 2000, the main procedure to find out what locks were issued was sp_lock. Usually, this command was followed by sp_who2 to find out more information about the spid in terms of the user, hostname, application, CPU usage, and memory usage. To examine the spid more closely, either DBCC INPUT BUFFER or fn_get_sql was issued to find out exactly what code was issued. If you couldn't find the data you were looking for or created your own scripts, you typically drilled into the master. dbo.syslockinfo table and/or the master. dbo.sys processes table, although this was not always the recommended approach. 

    Friday, May 14, 2021 2:12 PM
  • Thanks for this info
    Monday, May 17, 2021 7:48 AM