January 25, 2018 at 3:25 am
I have a small problem. I like to catch a detailed Error Message. If a command raise more then one Error, i can catch only the last Error.
I need the previous message. Here's an example:
BACKUP DATABASE acme
PRINT @@ERROR
Meldung 911, Ebene 16, Status 11, Zeile 1
Database 'acme' does not exist. Make sure that the name is entered correctly.
Meldung 3013, Ebene 16, Status 1, Zeile 1
BACKUP DATABASE is terminating abnormally.
3013
The caught error is 3013, but I would need 911.
The same behavior is also in a try-catch block:
BEGIN TRY
BACKUP DATABASE acme
END TRY
BEGIN CATCH
print 'CATCH: '+ERROR_MESSAGE()
END CATCH
CATCH: BACKUP DATABASE is terminating abnormally.
But I would like to have the message
Database 'acme' does not exist. Make sure that the name is entered correctly.
Is that possible and can someone help me?
January 25, 2018 at 6:46 am
diba 20194 - Thursday, January 25, 2018 3:25 AMI have a small problem. I like to catch a detailed Error Message. If a command raise more then one Error, i can catch only the last Error.
I need the previous message. Here's an example:
BACKUP DATABASE acme
PRINT @@ERRORMeldung 911, Ebene 16, Status 11, Zeile 1
Database 'acme' does not exist. Make sure that the name is entered correctly.
Meldung 3013, Ebene 16, Status 1, Zeile 1
BACKUP DATABASE is terminating abnormally.
3013The caught error is 3013, but I would need 911.
The same behavior is also in a try-catch block:
BEGIN TRY
BACKUP DATABASE acme
END TRY
BEGIN CATCH
print 'CATCH: '+ERROR_MESSAGE()
END CATCH
CATCH: BACKUP DATABASE is terminating abnormally.But I would like to have the message
Database 'acme' does not exist. Make sure that the name is entered correctly.Is that possible and can someone help me?
Try using THROW - along the lines of:
BEGIN TRY
BACKUP DATABASE Acme....
END TRY
BEGIN CATCH
THROW;
END CATCH
Sue
January 25, 2018 at 7:12 am
Thanks Sue, but with throw i give the last error to the next catch block, but i can't see the full error stack or previous error message.
It would be enough for me to be able to access all print messages.
January 25, 2018 at 2:53 pm
diba 20194 - Thursday, January 25, 2018 7:12 AMThanks Sue, but with throw i give the last error to the next catch block, but i can't see the full error stack or previous error message.
It would be enough for me to be able to access all print messages.
I think I understand - you want a way to access all error messages while in a SQL procedure of some sort. I'm not sure you can do it the way you are trying - you could log the errors though and there may be a way to work that into whatever your procedure is doing.
One approach to logging the errors is in this article - check the section Using SQLEventLog:
Error and Transaction Handling in SQL Server
Sue
January 26, 2018 at 12:21 pm
I suggest that you find a different way to manage the monitoring task.
For instance, try to do a select against the database first and capture any exception, just prior to the backup.
January 27, 2018 at 5:44 am
Thanks RandomStream,
The Backup-Command was only a general example. There are several commands that throw several errors.
The question of mine is how to save and evaluate all errors in a file or in a variable.
In the agent you can choose to save all steps in a table.
How can I access this table and where is it?
How can you save the output of a command in a variable?
January 29, 2018 at 1:29 am
I have found a workaround
select
--sj.name as 'JobName',
--sh.run_date,
--sh.run_time,
--sh.step_name,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) ',
msdb.dbo.agent_datetime(sh.run_date, sh.run_time) as 'RunDateTime',
sh.message
From
msdb.dbo.sysjobs sj
INNER JOIN
msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
where
sj.enabled = 1 --Only Enabled Jobs
and
sj.name like '%Test%'
and
sh.step_id=1
and
msdb.dbo.agent_datetime(sh.run_date, sh.run_time)=(select max(msdb.dbo.agent_datetime(sh.run_date, sh.run_time)) from msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id where sj.enabled = 1 and sj.name like '%Test%' and sh.step_id=1)
order by
--JobName,
RunDateTime desc
run_time run_duration (DD:HH:MM:SS) RunDateTime message
-------- -------------------------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
09:17:11 00:00:00:00 2018-01-29 09:17:11.000 Executed as user: NT SERVICE\SQLSERVERAGENT. Database 'acme' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Other suggestions are welcome!
April 17, 2024 at 8:27 am
use dba
go
create or alter procedure sqlserver.PollForURLBackup (
-- @URL nvarchar(500),
@DBBackup nvarchar(500),
@TimeOutSeconds int
) as
set nocount on
drop table if exists #HeaderOnlyDetails
create table #HeaderOnlyDetails (
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint,
containment tinyint,
KeyAlgorithm nvarchar(32),
EncryptorThumbprint varbinary(20),
EncryptorType nvarchar(32)
--LastValidRestoreTime datetime,
--TimeZone nvarchar(32),
--CompressionAlgorithm nvarchar(32)
)
drop table if exists #ActualError
create table #ActualError (
LogDate datetime,
ProcessInfo nvarchar(300),
LogText nvarchar(2000)
)
declare
@URL nvarchar(500),
@ErrorMessage nvarchar(500),
@now datetime
select @URL = convert(nvarchar(500), value) from msdb.sys.extended_properties where name = 'URL'
set @url = concat(@url, @DBBackup)
--print @url
declare @sql nvarchar(500)
set @sql = 'restore headeronly from url = ''@url'''
set @sql = replace(@sql, '@url', @url)
-- TODO: put this in a loop until timeout exceeded or backup found
begin try
set @now = getdate()
insert into #HeaderOnlyDetails exec (@sql)
end try
begin catch
insert into #ActualError exec xp_ReadErrorLog 0, 1, N'Cannot open backup device', null, @now, NULL, 'DESC'
update #ActualError set LogText = replace(LogText, @URL, concat('https://<url>/', @DBBackup))
select @ErrorMessage = LogText from #ActualError
end catch
select @ErrorMessage as 'the actual error message'
go
exec sqlserver.PollForURLBackup 'aaa.bak', 3600
April 17, 2024 at 8:31 am
This returns:
the actual error message
--------------------------------------------------------------------------------------------------------------------------
Cannot open backup device 'https://<url>/aaa.bak'. Operating system error 2(The system cannot find the file specified.).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply