Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    86

    Unanswered: FIle system sproc has weird syntax error issue

    I'm trying to create a stored proc that will monitor the file system threshold (remaining space for each drive on a sql server)

    I can't get this sproc to create w/o a syntax error, except if I make it check only one drive.
    I get "Incorrect syntax near the keyword 'select' " on ine 33
    select @MB_Free = MB_Free from #FreeSpace where Drive = 'E'
    If I remove all code from that point on, I get "Incorrect syntax near 'end'
    -- that's the 'end' after the "D" drive check
    If I remove all but the check on the C drive it gets created w/o an error.
    What is going on here?



    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[sp_dba_freespace] Script Date: 05/04/2009 10:47:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE proc [dbo].[sp_dba_freespace]
    as
    begin
    declare @MB_Free int

    create table #FreeSpace(
    Drive char(1),
    MB_Free int)

    insert into #FreeSpace exec xp_fixeddrives

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'
    -- Free Space on C drive Less than Threshold
    if @MB_Free < 1024
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive C: is below 1gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on C Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'D'
    -- Free Space on D drive Less than Threshold
    if @MB_Free < 1024
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive D: is below 1gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on D Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'E'
    -- Free Space on E drive Less than Threshold
    if @MB_Free < 4096
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive E: is below 4gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on E Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'F'
    -- Free Space on F drive Less than Threshold
    if @MB_Free < 2048
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive F: is below 2gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on F Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'G'
    -- Free Space on G drive Less than Threshold
    if @MB_Free < 4096
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive G: is below 4gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on G Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'H'
    -- Free Space on H drive Less than Threshold
    if @MB_Free < 4096
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive H: is below 4gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on H Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'I'

    -- Free Space on F drive Less than Threshold
    if @MB_Free < 4096
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive I: is below 4gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on I Drive',
    @profile_name ='TREKSmail'
    end

    select @MB_Free = MB_Free from #FreeSpace where Drive = 'J'
    -- Free Space on F drive Less than Threshold
    if @MB_Free < 8192
    exec msdb.dbo.sp_send_dbmail
    @recipients =N'xxx@yahoo.com,
    @body='Free space on drive J: is below 8gb',
    @subject ='ALERT: SERVER YKDAVSA - Free space threshold reached on J Drive',
    @profile_name ='TREKSmail'
    end
    Last edited by stuarta; 05-04-09 at 15:11.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've got a heck of a lot more END than BEGIN tags. In SQL those have to be matched, an IF doesn't generate an implicit BEGIN like it does in BASIC.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2007
    Posts
    86
    Yeah I know .. I tried 'if begin end' on each but they syntaxed out . I just realized I had a begin w/o an end to start with on the sproc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •