How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")

Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.

works:
[build @.RestoreCmd]
exec (@.RestoreCmd)

doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder

Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]

Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder

Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.

Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.

Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.
Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.c om...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.
|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056
.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to email completion messages from RESTORE commands?

I need to build an automated email that gives the completion messages
when a database is restored (i.e. "Executed as user: sa. Executing
RESTORE DATABASE DB1 FROM
DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
01000] (Message 0) Processed 3816 pages for database 'DB1', file
'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
(Message 4035)")
Currently, the Job History box contains it, but I'd rather get it via
email. The base restore statement works, but it gives me a "Cannot
perform a backup or restore operation within a transaction." when I
try to run it as below.
works:
[build @.RestoreCmd]
exec (@.RestoreCmd)
doesn't:
[build @.RestoreCmd]
create table #Error_Finder (listing nvarchar (4000))
declare @.Errors smallint
insert #Error_Finder exec (@.RestoreCmd)
EXEC xp_sendmail @.recipients = 'dba',
@.query = 'SELECT * from #Error_Finder',
@.subject = 'SQL Server Restores'
drop table #Error_Finder
Any suggestions? My next thought is to start selecting against system
tables in msdb. It looks because the Insert can fail, it's a
transaction.Michale
In advanced tab of the job step window click 'edit' there you can define
where to go in success or on failure. Create two steps like 'send OK', and
'send Failed' that will be notified you about restore.
"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0411100645.35713c7e@.posting.google.com...
> I need to build an automated email that gives the completion messages
> when a database is restored (i.e. "Executed as user: sa. Executing
> RESTORE DATABASE DB1 FROM
> DISK='h:\backups\DB1\DB1_db_200411082056.BAK', RECOVERY [SQLSTATE
> 01000] (Message 0) Processed 3816 pages for database 'DB1', file
> 'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1
> pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000]
> (Message 4035)")
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
>
> Any suggestions? My next thought is to start selecting against system
> tables in msdb. It looks because the Insert can fail, it's a
> transaction.|||[posted and mailed, please reply in news]
Michael Bourgon (bourgon@.gmail.com) writes:
> Currently, the Job History box contains it, but I'd rather get it via
> email. The base restore statement works, but it gives me a "Cannot
> perform a backup or restore operation within a transaction." when I
> try to run it as below.
> works:
> [build @.RestoreCmd]
> exec (@.RestoreCmd)
> doesn't:
> [build @.RestoreCmd]
> create table #Error_Finder (listing nvarchar (4000))
> declare @.Errors smallint
> insert #Error_Finder exec (@.RestoreCmd)
> EXEC xp_sendmail @.recipients = 'dba',
> @.query = 'SELECT * from #Error_Finder',
> @.subject = 'SQL Server Restores'
> drop table #Error_Finder
Even if there is no user-defined transaction, an INSERT, UPDATE or
DELETE statement is its own transaction in SQL Server. This means that
INSERT EXEC() defines a transaction.
Furthermore, even if RESTORE had not cared about the transaction, it
would not have worked anyway, because INSERT EXEC() can only catch
result set, and what RESTORE produces is an informational message,
which is passed to the client. There is no way to catch this message
in the server.
Uri's suggestion of using the GUI to set up a e-mail alert, sounds like
a much easier way to go.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

How to do this

Hi All,
I have a table Called MailList with 2 columns namely MailId and Message.
There can be many messages related to one email id in the table. I need to
consolidate all the messages into a single message and send the mail to that
id.
(It may or may not exceed the limit of 8000 chars).
For Example,
MailId Message
a@.x.com XXXXXXXXXXXXXXXXXXXXXX
a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
c@.z.com BB
a@.x.com CCC
I need to concatenate the messages of the first, second and fifth record
into one single message and send the mail to a@.x.com. Similarly for the othe
r
two.
How can I do this. Please help me at the earlist.
Sevugan.CYou have to do this in a loop. Wheter you do it in a temporary table or
within a cursor belongs to you coding style.
HTH, Jens Suessmeyer.|||use northwind
-- your problem looks like this --
select supplierid, productname
from products
-- your problem looks like this --
-- solutions--
create proc concatenate
(
@.supplierid Varchar(10)
)
as
select IDENTITY(int, 1,1) AS ID_Num,
Productname into #tempx from products where
supplierid=@.supplierid
declare @.ctr int
declare @.concatenated varchar(5000)
declare @.maxrecord int
select @.maxrecord=max(id_num) from #tempx
select @.concatenated=''
select @.ctr=0
while @.ctr <> @.maxrecord
begin
Select @.ctr=@.ctr+1
Select @.concatenated =@.concatenated+Productname
from #tempx where id_num=@.ctr
end
select @.concatenated
--test solution --
exec concatenate 7
try to improve from there...
thanks,
joeydj
"Sevugan" wrote:

> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to th
at
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the ot
her
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||Which is the PK? You want message to be XXX...YYYY...CCC or
CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
helpful for us.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to
> that
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the
> other
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||It is a temporary table. The Message is the primary key.
I want the message to be xxx...yyy...ccc...
--
Sevugan.C
"Perayu" wrote:

> Which is the PK? You want message to be XXX...YYYY...CCC or
> CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
> helpful for us.
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
>
>|||Hi
My problem here is I need to store more than 8000 characters which is not
permitted in VARCHAR variable.
If the no.of records in the table are more than 8000 chars how can I store
that?
--
Sevugan.C
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> use northwind
> -- your problem looks like this --
> select supplierid, productname
> from products
> -- your problem looks like this --
> -- solutions--
> create proc concatenate
> (
> @.supplierid Varchar(10)
> )
> as
> select IDENTITY(int, 1,1) AS ID_Num,
> Productname into #tempx from products where
> supplierid=@.supplierid
> declare @.ctr int
> declare @.concatenated varchar(5000)
> declare @.maxrecord int
> select @.maxrecord=max(id_num) from #tempx
> select @.concatenated=''
> select @.ctr=0
>
> while @.ctr <> @.maxrecord
> begin
> Select @.ctr=@.ctr+1
> Select @.concatenated =@.concatenated+Productname
> from #tempx where id_num=@.ctr
> end
> select @.concatenated
> --test solution --
> exec concatenate 7
>
> try to improve from there...
> --
> thanks,
> --
> joeydj
>
> "Sevugan" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>From your vague narrative, you need a column that gives you the message
order.
Finally, you need to learn RDBMS and *basic* tiered architecutre
concepts. This is a display issue and shjould be done in the front
end, never in the database.|||Why can't you use Text column?
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
> Hi
> My problem here is I need to store more than 8000 characters which is not
> permitted in VARCHAR variable.
> If the no.of records in the table are more than 8000 chars how can I store
> that?
> --
> Sevugan.C
>
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||If message is the PK, so you have no control which one will be the first
one.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:B76CCD95-E2F9-4315-BA77-D2B9C614CE76@.microsoft.com...
> It is a temporary table. The Message is the primary key.
> I want the message to be xxx...yyy...ccc...
> --
> Sevugan.C
>
> "Perayu" wrote:
>|||How would I pick the value from that text column to make use of that in the
xp_sendmail procedure call.
Exec Master..xp_sendmail @.message = <how to bring the value of the text
column here>
Sevugan.C
"Perayu" wrote:

> Why can't you use Text column?
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
>
>