Script when database backup fails

Hello,

I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.

I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @.message = @.message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.message = @.message + @.dbname + ' backup up on ' + @.Status + Char(13)
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

print @.message

EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of sqlserver!',
@.type = N'text/html',
@.message = @.message

Thanks

How are you performing the backup? If you are using say SQLAgent jobs then why don't you just include an additional step at the end after the backup step which fires only if the backup fails. This can email the details and you don't even have to use extended SPs etc. I believe that SQLAgent has ways to send emails. Otherwise, you will have to check for status of backup also in the msdb tables since you cannot create triggers on system tables.

Script task Error

I have a script task that is supposed to perform some task but it fails and throws this exception

"Unable to cast COM object of type System._OComObject to class System.Data.Odbc.odbcConnection". Instances of types that represent com components cannot be cast to the types that represent COM components; they can be caste to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the inteface".

Here is the code that throws this

Dim sqlString As String

Dim conn As Odbc.OdbcConnection

Dim da As Odbc.OdbcDataAdapter

Dim ds As Data.DataSet = New Data.DataSet()

sqlString = " SELECT count(*)FROM tmpDAY INNER JOIN tblData ON tmpDAY.CusID = tblData.datFKCusID WHERE(tmpDAY.Serial = tblData.datSerial)"

Dim connName As String = Dts.Connections(0).Name

Try

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

da = New Odbc.OdbcDataAdapter(sqlString, conn)

Catch ex As Exception

MsgBox(ex.Message.ToString())

End Try

Any suggestion will be greatly appreciated?

you could try replacing your CType with a normal ODBC connection constructed from a string
i.e. replace
conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with

Dim connString As String = Dts.Connections(0).ConnectionString

conn = New Odbc.OdbcConnection(connString)|||

replace:

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with:

conn = New Odbc.OdbcConnection(connectionString)

conn.Open()

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed becau
se
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

scptxfr.exe - generated script fails

Generated script fails because fucntions are added to this script after
tables in which this functions can be used for calculated column.
How can I resolve this?
Is there any usefull tool that can generate script with proper order of
dependence objects?
|||Download DataStudio from http://www.agileinfollc.com, it has very strong
scripting capability.
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124378725.452117.32190@.g47g2000cwa.googlegro ups.com...
> Is there any usefull tool that can generate script with proper order of
> dependence objects?
>
|||Can it do exactly that I need. It is 7 MB very big for me. I would like
to know weather this soft can do my work before downloading. Does
avaluatin versiong it?
|||DataStudio has evaluation version (http://www.agileinfollc.com/product.asp),
you can install and try it before you buy.
John King
http://www.agileinfollc.com
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124459262.366497.216500@.g47g2000cwa.googlegr oups.com...
> Can it do exactly that I need. It is 7 MB very big for me. I would like
> to know weather this soft can do my work before downloading. Does
> avaluatin versiong it?
>
|||I downloaded evaluation version. This software DOES NOT make working
SQL script for database creation. ((
The problem is still opened.
|||Can I temprally disable error rising if I'am creating a table that has
in its default value nonexisting function?