Maintenance Plan email report failure

Hello,
I have a simple daily maintenance plan for backups.(check integrity, back up, clean up history and then report) However, sending the email report always fails with the following error:

"Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.Incorrect syntax near 'sys'."

How can the wizard output Incorrect syntax?

I have set up and tested Database mail and set the default public profile.
I have enabled the mail profile in SQL server agent properties and then restarted SQL server agent.
I have set up operator to receive the mail.
The frustrating thing is that if I set up and schedule a really simple test maintenance plan, the email report works! But it always fails on the real one.
How can I get more detail in the error message and actually see the SQL it is trying to execute when it send the mail?

Thanks,
Adam

More detail.

Having done a few tests it seems that this mail report error only occurs after the check integrity job fails. eg.

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error:

"Incorrect PFS free space information for page (1:278) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).

CHECKDB found 0 allocation errors and 1 consistency errors in database 'RHJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (RHJ).".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This failure does not affect the backup and maintenance cleanup tasks that run afterwards, only the email report that it tries to send at the end.

mailing from MS SQL

Hi, is it possible to send an email from MS SQL? I need to have a trigger which will activate the sending an email. Thanksdo not do it from a trigger. What happens if there is a bulk import of 10,000 records. You want 10,000 emails? Otherwise please google or refer to SQL Server Books Online for xp_sendmail or xp_smtpmail.|||A bulk import of 10,000 records is still only going to execute the trigger once. A cursor or loop-based import would stress out your e-mail system, though.
It's just a bad idea for the scope of a trigger's actions to extend outside the database. Don't do it.
An alternative is to have your trigger load messages into a queue table for regularly processing by a scheduled job (which could run as frequently as once per minute).|||I was assuming that we would execute xp_sendmail for every record in inserted smart guy.|||I know you were, and I know you know the difference. Just wanted to make sure the poster did not get the wrong impression.

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:
>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:
> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> > Hi
> >
> > I recently had a big problem with an important job, after sending an email
> > using the msdb..sp_send_dbmail stored procedure the job succeeded!
> > BUT
> > the emails never went out! because the email server was problematic at
> > roughly the same time. This is the error that was logged in the sql 2005
> > email job log.:
> > No connection could be made because the target machine avtively refused
> > it.
> > mail server failure ... bla bla bla
> >
> > When i reran the job a couple of hours later the mails went out because
> > the
> > mail server was up and running.
> >
> > My question - is there not a way to tell the mail profile to retry sending
> > the email a couple of times with say 1 minute intervals between retries -
> > same as in a sql 2005 job'
> >
> > thanks
> >
> > Ian
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:
> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
> >Hi
> >
> >I recently had a big problem with an important job, after sending an email
> >using the msdb..sp_send_dbmail stored procedure the job succeeded!
> >BUT
> >the emails never went out! because the email server was problematic at
> >roughly the same time. This is the error that was logged in the sql 2005
> >email job log.:
> >No connection could be made because the target machine avtively refused it.
> >mail server failure ... bla bla bla
> >
> >When i reran the job a couple of hours later the mails went out because the
> >mail server was up and running.
> >
> >My question - is there not a way to tell the mail profile to retry sending
> >the email a couple of times with say 1 minute intervals between retries -
> >same as in a sql 2005 job'
> >
> >thanks
> >
> >Ian
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job?
thanks
Ian
Hi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job?
> thanks
> Ian
|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job?
>thanks
>Ian
|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded then
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>
|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Profile sql 2005 : How to retry until successfully sent?

Hi
I recently had a big problem with an important job, after sending an email
using the msdb..sp_send_dbmail stored procedure the job succeeded!
BUT
the emails never went out! because the email server was problematic at
roughly the same time. This is the error that was logged in the sql 2005
email job log.:
No connection could be made because the target machine avtively refused it.
mail server failure ... bla bla bla
When i reran the job a couple of hours later the mails went out because the
mail server was up and running.
My question - is there not a way to tell the mail profile to retry sending
the email a couple of times with say 1 minute intervals between retries -
same as in a sql 2005 job'
thanks
IanHi
As I understood, you may want to PING the server
to check whether or not the server is running up and if it succeded then
send the email
Some ideas, check it out
SET NOCOUNT ON
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
> Hi
> I recently had a big problem with an important job, after sending an email
> using the msdb..sp_send_dbmail stored procedure the job succeeded!
> BUT
> the emails never went out! because the email server was problematic at
> roughly the same time. This is the error that was logged in the sql 2005
> email job log.:
> No connection could be made because the target machine avtively refused
> it.
> mail server failure ... bla bla bla
> When i reran the job a couple of hours later the mails went out because
> the
> mail server was up and running.
> My question - is there not a way to tell the mail profile to retry sending
> the email a couple of times with say 1 minute intervals between retries -
> same as in a sql 2005 job'
> thanks
> Ian|||You can set the number of retires and the retry delay
interval at the server configuration level. In T-SQL, you
can use sysmail_configure_sp. If you are using the Database
Mail Configuration wizard in Management Studio, select the
option to View or Change System Parameters.
-Sue
On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
Coetzer@.discussions.microsoft.com> wrote:

>Hi
>I recently had a big problem with an important job, after sending an email
>using the msdb..sp_send_dbmail stored procedure the job succeeded!
>BUT
>the emails never went out! because the email server was problematic at
>roughly the same time. This is the error that was logged in the sql 2005
>email job log.:
>No connection could be made because the target machine avtively refused it.
>mail server failure ... bla bla bla
>When i reran the job a couple of hours later the mails went out because the
>mail server was up and running.
>My question - is there not a way to tell the mail profile to retry sending
>the email a couple of times with say 1 minute intervals between retries -
>same as in a sql 2005 job'
>thanks
>Ian|||Hi
This would not have solved the problem because the server was up and running
- but the mail service was hanging / not working i think.
bye
"Uri Dimant" wrote:

> Hi
> As I understood, you may want to PING the server
> to check whether or not the server is running up and if it succeded the
n
> send the email
> Some ideas, check it out
> SET NOCOUNT ON
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> DROP TABLE #t_ip
> RETURN
> END
> DROP TABLE #t_ip
>
>
> "I.W Coetzer" <I.W Coetzer@.discussions.microsoft.com> wrote in message
> news:27F55D35-F6A1-437D-888D-B897A30DA9A0@.microsoft.com...
>
>|||brilliant! thank you very much - now to wait and see what happens tomorrow
morning ...
bye
"Sue Hoegemeier" wrote:

> You can set the number of retires and the retry delay
> interval at the server configuration level. In T-SQL, you
> can use sysmail_configure_sp. If you are using the Database
> Mail Configuration wizard in Management Studio, select the
> option to View or Change System Parameters.
> -Sue
> On Mon, 16 Apr 2007 04:14:04 -0700, I.W Coetzer <I.W
> Coetzer@.discussions.microsoft.com> wrote:
>
>

Mail Delivery - Corupt attachments

When I configure Mail Delivery in RS sending mails with links works fine, but when I want that also report is attached in mail I get corupted files.
There is no difference if it's PDF, Excel or TIFF.
Delivery to File Share is working fine and all files are readable.
I use LotusNotes SMTP service to deliver mail, but to minimize possibilitie that this has influence I have configured local virtual SMTP which re-route mail to LN SMTP.
I'm using Reporting Services Ent. SP1 on Win 2003 Server with SQL 2000 SP3.
All services uses strartup account NETWORK/SERVICE and connection between RS and SQL database uses SQL account.
Second test machine is on WinXP with SQL 2000 SP3 Developer and RS Developer with domain startup accounts.
On both machines there is the same problem.There is an issue with RS delivering email through lotus. We have issued a
QFE. I believe you will need to contact Product Support to resolve this
issue.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mario Spicar" <Mario Spicar@.discussions.microsoft.com> wrote in message
news:50BBA072-BDC0-4653-AC05-A3CDA535BD7F@.microsoft.com...
> When I configure Mail Delivery in RS sending mails with links works fine,
but when I want that also report is attached in mail I get corupted files.
> There is no difference if it's PDF, Excel or TIFF.
> Delivery to File Share is working fine and all files are readable.
> I use LotusNotes SMTP service to deliver mail, but to minimize
possibilitie that this has influence I have configured local virtual SMTP
which re-route mail to LN SMTP.
> I'm using Reporting Services Ent. SP1 on Win 2003 Server with SQL 2000
SP3.
> All services uses strartup account NETWORK/SERVICE and connection between
RS and SQL database uses SQL account.
> Second test machine is on WinXP with SQL 2000 SP3 Developer and RS
Developer with domain startup accounts.
> On both machines there is the same problem.

Mail

How can I implement mail sending facility in Sql Server 2000(in DTS)?Hey - You need to enable a MAPI Profile in SQL Mail (In Enterprise Mgr - Support Servcies/SQL Mail).

Once that's done, the Send Mail Task will work.