Passing in current database name to DTS-based SQL task?
Hi all--I am writing a DTS package looking to back up all databases in sequence. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:\<path>.bak' with init
2. Upon failure, send the following message:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
select @srvname=srvname from master.dbo.sysservers
print @srvname
select @db_name=name from master.dbo.sysdatabases
print @db_name
set @msg='Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
Upon success, go to the next database to back up, execute through these steps for the next database. Here's my problem: The @db_name variable captures the proper value for the current SQL Server, but I want to turn both @srvname and @db_name into global variables. I would like to pass in @db_name as the current database being backed up (and failing), and @srvname as the current SQL Server instance being backed up.
The above statement "select @db_name=name from master.dbo.sysdatabases" definitely does not capture the current database name value. Any suggestions as to how to pass this in, if not as a global variable?
Thanks in advance,
Jonathan
