Error Handling t-sql SQL Server 2005

  • OK..here is what I need help with. I need to loop through a cursor (yuck!) and handle any exception on a RBAR basis. HEre is a simple scenario. Loop through all the database names in the cursor. Everytime you come to the northwind or pubs DB pretend it is an error. Upon this error, email someone that the error occured, and continue through the loop until all DBs have been gone through. I apologize in advance for any syntactical errors below but I am not fluent in the .NET syntax as of yet. 😉

    WHILE @@FETCH_STATUS = 0

    begin

    begin try

    IF dbname = 'northwind' or dbname = 'pubs'

    raiseerror

    end try

    begin catch

    Email DBA that db exists ( I use cdosysmail for this)

    resume and go to the next record

    end catch

    end

    --fetch teh next row..

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

  • I think this may be simpler:

    while 1=1

    begin

    fetch ...

    if @@fetch_status <> 0 break

    if dbname in ('northwind','pubs')

    begin

    .. send email or whatever ...

    continue

    end

    ... rest of stuff in cursor loop ...

    end

  • I agree but the rest of the scenario is this.....

    I can make the code run fine in the Management studio. However, when I run it as a job, the error is caught but the job halts on the error. I am hoping that the try...catch method may resolve this. I have confirmed that the faliure is due to the calling of another proc from within the proc.

    HEre is the actual code that I am doing. It pertains to automating a LiteSpeed backups. I need to do some Olympic events during this time based on a variety of conditions. I am trying to get the basic portion to work. PLease forgive the back and forth of this code becuase I am now using the 'Poke and Hope' method for a resolution. 😛

    *************

    alter procedure BU_DB

    /*

    PAss in the DB name and root folder at a later time.

    */

    as

    declare

    @folder varchar(50),

    @path Varchar(150),

    @fullpath varchar(200),

    @date_temp varchar(20),

    @time_temp varchar(20),

    @name varchar(40),

    @dbname varchar(50),

    @BU_FileName varchar(150),

    @bu_type char(1),

    @err int

    /* These are used to send the message in case of a failure or success */

    declare

    @static_msg varchar(3000),@data_info_msg varchar(8000),@closing_msg varchar(3000), @recipients varchar(350),

    @subject varchar(70),@from varchar(20)

    Select @from =

    Select @recipients=

    Select @static_msg = 'This email is from our test environment and should be ignored by all except for the SQL DBA.'

    Select @Subject='Database Backup Failure Test Message'

    --Initialize the variable

    Select @err=0

    /*

    This cursor will be used to get all the db_names in the

    instance and act accordingly.

    */

    declare DB_Cursor cursor

    for

    select name from master..sysdatabases where name not in ('tempdb', 'model')

    for read only

    /*

    Get the current timestamp from the server.

    This is what we will use to timestamp the backup.

    */

    select @date_temp = convert(varchar(20), getdate(),112)

    Select @time_temp = convert(varchar(20), getdate(),108)

    --create the proper name for the parent folder

    select @name = @date_temp + left(@time_temp,2)+ substring(@time_temp,4,2)+ right(@time_temp,2)

    /*

    We will create the correct path. In the naming convention of the

    folder, we will wwant ot remove the seconds form the name. We want the

    seconds on the file name itself.

    Note: The root folder should be passed in using a variable. This will

    allow us to re-use the same code on all server instances. It can remain

    a constant variable if necessary.

    */

    --select @path ='D:\MSSQLBU\BACKUP\'+left(@name,len(@name)-2)+'\'

    --select @path ='\\dkdodo\dave\'+left(@name,len(@name)-2)+'\'

    select @path ='\\Csdc01\dave\'+left(@name,8) + '\'--left(@name,len(@name)-2)+'\'

    --select @path ='e:\BackupFiles\Test_Servers\'+left(@name,len(@name)-2)+'\'

    /*

    Here is where we will open the cursor to loop through the

    database names and append them to the path.

    */

    Open db_cursor

    /* Get the first row */

    FETCH NEXT FROM db_cursor INTO @dbname

    /*

    Begin looping through the DBs and create the directories as needed.

    It will be the path plus the db name.

    */

    WHILE @@FETCH_STATUS = 0

    begin

    --Print @dbname

    Select @fullpath = @path+@dbname+'\'

    --print @fullpath

    /*

    Check for the needed folder in the specified directory. This folder

    should reflect the current day. We will use a table variable and an

    extended stored proc to get the needed info.

    */

    --Table variable to store the XP results to be queried

    declare @t1 table (fileExists bit,isDirectory bit,parentDirectoryExists bit)

    --Populate the table variable

    insert into @t1 exec xp_fileexist @fullpath

    if not exists(select * from @t1 where isDirectory >0)

    begin

    --Create the needed folder

    declare @command varchar(1000)

    set @command = 'mkdir ' + @fullpath

    exec @err = master..xp_cmdshell @command , no_output

    if @err != 0

    begin

    select @err=86 --resource not found

    Select @data_info_msg = 'The network resource '

    Select @data_info_msg = @data_info_msg+ ' Please verify the server is up and that the SLQ id has the needed permissions.'

    goto err_handler

    end

    end

    /*

    Once we have established that the directory exists then we will need

    to name and kick off the appropriate backup. The backup type will

    be based on the time of day.

    A fullbackup will be taken during the midnight hour.

    */

    Select @bu_type = case when datepart(hh,getdate()) <=0 then 'F' else 'T' end

    select @BU_FileName = @path+@dbname +'\'+@dbname + '_'+@name + '_'+@bu_type+'.safe'

    --print @bu_type

    print @BU_FileName

    if @bu_type = 'F'

    Begin

    --Full backup

    exec @err=master.dbo.xp_backup_database

    @database = @dbname,

    @GUID = N'CB58942F-140A-4D75-B7D5-4C0D80BE1E05',

    @filename = @BU_FileName,

    @backupname = @dbname,

    @desc = N'Backup set',

    @encryptionkey = N'test', @compressionlevel = 11,

    @comment = '',

    @with = N'SKIP', @with = N'STATS = 10'

    End

    else

    begin

    if @dbname !='master' and @dbname!='msdb'

    begin

    -- Transaction BU

    exec @err=master.dbo.xp_backup_log

    @database = @dbname,

    @GUID = N'C12712A5-6952-436A-9629-B07995218E78',

    @filename = @BU_FileName,

    @backupname = @dbname,

    @desc = N'Backup set',

    @encryptionkey = N'test', @cryptlevel = 8, @compressionlevel = 11,

    @comment = '', @with = N'SKIP', @with = N'STATS = 10'

    end

    end

    /*

    The below error is thrown when an atempt is made to BU the trasnaction

    log without having taken a full BU first.

    */

    -- if @err = 62309

    -- begin

    -- Select @data_info_msg = 'The database backup for failed at ' + convert(varchar(20), getdate(),120)

    -- Select @data_info_msg = @data_info_msg+ 'with the following error: '

    -- Select @data_info_msg = @data_info_msg+'BACKUP LOG cannot be performed because there is no current database backup.'

    -- exec @err= master..sp_send_cdosysmail @from,@recipients,@subject,@data_info_msg, @bodytype='HTMLBody'

    -- end

    -- else if @err !=0

    -- begin

    -- goto err_handler

    -- end

    get_next:

    --Get the next record

    FETCH NEXT FROM db_cursor INTO @dbname

    End

    clean_up:

    CLOSE db_cursor

    DEALLOCATE db_cursor

    --Bail out

    return

    err_handler:

    if @err=86 --path not found

    Begin

    Select @data_info_msg = @Static_msg + char(10) + char(13)+@data_info_msg

    End

    -- else if @err = 62309

    -- Begin

    -- Select @data_info_msg = 'The database backup for failed at ' + convert(varchar(20), getdate(),120)

    -- Select @data_info_msg = @data_info_msg+ 'with the following error: '

    -- Select @data_info_msg = @data_info_msg+'BACKUP LOG cannot be performed because there is no current database backup.'

    -- exec master..sp_send_cdosysmail @from,@recipients,@subject,@data_info_msg, @bodytype='HTMLBody'

    -- Select @err = 0

    -- goto get_next

    -- end

    else if @err=0

    begin

    Select @from = 'SQLDBA@cybershift.com'

    Select @recipients='dpaskiet@cybershift.com'

    Select @static_msg = ' '

    Select @Subject=@@servername + 'Database Backups Were Successful - Test'

    end

    else

    begin

    Select @data_info_msg = 'The database backup for failed at ' + convert(varchar(20), getdate(),120)

    Select @data_info_msg = @data_info_msg+ 'This is a catch all error. I dont know what happened here.'

    Select @data_info_msg = @data_info_msg+'BACKUP LOG cannot be performed because there is no current database backup.'

    exec @err=master..sp_send_cdosysmail @from,@recipients,@subject,@data_info_msg, @bodytype='HTMLBody'

    Select @err = 0

    goto get_next

    end

    exec @err = master..sp_send_cdosysmail @from,@recipients,@subject,@data_info_msg, @bodytype='HTMLBody'

    goto clean_up

    --

    go

    set nocount off

    Kindest Regards,
    David

    ** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply