An Error Handling Template for 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Leo Peysakhovich » An Error Handling Template for 2005

An Error Handling Template for 2005

Rate Topic

Display Mode

Topic Options

Author

Message

Leo Peysakhovich

Posted Wednesday, February 15, 2006 9:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 26, 2013 6:18 AM
Points: 129, Visits: 205

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/anerrorhandlingtemplatefor2005.asp

Post #258704

Wesley Brown

Posted Monday, March 13, 2006 11:12 PM

SSChasing Mays

Group: Moderators
Last Login: Tuesday, May 07, 2013 3:54 PM
Points: 608, Visits: 379

Nice example of the try catch and a standard error handler! I think this is the single best thing that they could have done for the T-SQL language.

Wes

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org

Post #265436

cutespn

Posted Tuesday, March 14, 2006 1:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 25, 2010 12:00 PM
Points: 125, Visits: 20

Good article!!

Regards,
Sudheer

Post #265447

Ryan-209402

Posted Tuesday, March 14, 2006 6:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 8:34 AM
Points: 121, Visits: 100

To the novice DBA this definitely looks like something that would benefit our developers and to help a DBA get their feet wet with try/catch phrases. However, as a novice, I was unable to determine from the article just how something like this is actually implemented. I realize that this article is probably targeted toward more advanced DBAs/Developers but a short paragraph at the end of the article to help junior DBAs (and non programmers) understand how to get started with this would be excellent. Thanks!

Post #265517

safigi

Posted Tuesday, March 14, 2006 8:33 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, December 11, 2011 11:54 PM
Points: 14, Visits: 3

You should change dbcc inputbuffer something like that:

SELECT t.[text]

FROM sys.dm_exec_requests As r
cross apply sys.dm_exec_sql_text(Sql_handle) As t
WHERE session_id = @@spid

More better if you search in text the line of error using ERROR_LINE () function

Cheers,Safi

Post #265588

John Grant-372231

Posted Tuesday, October 03, 2006 3:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 12, 2011 10:19 AM
Points: 1, Visits: 33

I found a lot of mistakes and had to fix them in order to get this thing to work.

On on line you are referencing an IDENTITY against a table that does not exists. The table in question is HIST_ERROR_LOG, I had to change it to ERROR_LOG. I found some other things that needed to be fixed, i.e. missing commas, ' instead of ". Aside from all that, this error handling template works great.

Thank you very much.

Post #313078

earamayo

Posted Thursday, May 31, 2007 1:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 01, 2007 10:23 AM
Points: 1, Visits: 1

I am new to SQL Server. Can someone tell me why (sometimes) when declaring variables the author uses @$prog instead of just @prog. What is the significance of the $ symbol in this context?

Thank you

Post #370410

David Paskiet

Posted Wednesday, November 21, 2007 1:41 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:02 AM
Points: 313, Visits: 183

I am liking the new format although I am new to the .NET programming standard. I have many years experience in VB6 and lesser SQl versions than 2oo5. I have a question though that I cant seem to answer. How do you get the procedure to go back to the point of filaure and contiunue? Here is my scenario.

I open a cursor and loop through all DB names. For each DB, I create the needed t-sql statement to backup the DB either full or transaction log depending on the time of day. We are always adding new clients to our DB server and they autmatically get added to teh next backup whe the job runs. If the error occurs, I need to have it go to the error handler, send and email using cdosysmail and return to the while loop and continue through the process. I had it sort of working but only in the SQL window. ONce it was scheduled as a job, it caused the job to fail.

Any help would be appreciated.

I have pasted the ugly version of the code below. It was much cleaner before I tried to resolve this issue.

**************************
--EXEC sp_updatestats

go
set nocount on
go
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 = 'SQLDBA@cybershift.com'
Select @recipients='dpaskiet@cybershift.com'
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 @err=0
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'
goto get_next
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. **

Post #424738

« Prev Topic | Next Topic »

Permissions