Showing posts with label cdosys. Show all posts
Showing posts with label cdosys. Show all posts

Friday, February 24, 2012

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stu
Stu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Conde?a
"Stu" wrote:

> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail used
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>
|||Paulo,
thank you for that, that has worked.
stu
"Paulo Conde?a" wrote:
[vbcol=seagreen]
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Conde?a
> "Stu" wrote:
|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Conde?a
"Stu" wrote:
[vbcol=seagreen]
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Conde?a" wrote:

Sunday, February 19, 2012

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stuStu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Condeça
"Stu" wrote:
> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail used
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>|||Paulo,
thank you for that, that has worked.
stu
"Paulo Condeça" wrote:
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Condeça
> "Stu" wrote:
> > Hi there,
> >
> > I have CDOSYS email working and correclty firing off emails for configured
> > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > maintenance plan job successfully backups all user databases (we have
> > aproximately 16). what i'm after is the same information that sql mail used
> > to send:
> >
> > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > run on 19/11/2006 at 19:00:01
> > DURATION: 0 hours, 15 minutes, 1 seconds
> > STATUS: Succeeded
> > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > (Schedule 1). The last step to run was step 1 (Step 1).
> >
> >
> > the closest i can get to this is to use the following script as the next
> > step after the backups have completed:
> >
> > DECLARE @.msg nvarchar(4000)
> > Set @.msg = REPLACE("Error: [A-ERR]
> > Severity: [A-SEV]
> > Date: [STRTDT]
> > Time: [STRTTM]
> > Database: [A-DBN]
> > Message: [A-MSG] ", "'", "") --'
> >
> > EXEC sp_send_cdosysmail 'fromaddress',
> > 'toaddress',
> > 'subject line',
> > @.msg
> >
> > but all this produces is the following:
> >
> > Error:
> > Severity:
> > Date: 20061120
> > Time: 111358
> > Database:
> > Message:
> >
> > i dont really want to create separate jobs and alerts for each individual
> > database so i was wondering if anyone had any ideas at all?!
> >
> > thanks in advance
> >
> > stu
> >|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Condeça
"Stu" wrote:
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Condeça" wrote:
> > Stu,
> >
> > What you can do is the following :
> > In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
> > and associate the failure or the sucess of the maitenance plan step to the
> > respective step ( sucess or failure ).
> >
> > Best Regards,
> >
> > Paulo Condeça
> >
> > "Stu" wrote:
> >
> > > Hi there,
> > >
> > > I have CDOSYS email working and correclty firing off emails for configured
> > > alerts but what i'm trying to do is get CDOSYS to send an email when a
> > > maintenance plan job successfully backups all user databases (we have
> > > aproximately 16). what i'm after is the same information that sql mail used
> > > to send:
> > >
> > > JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> > > run on 19/11/2006 at 19:00:01
> > > DURATION: 0 hours, 15 minutes, 1 seconds
> > > STATUS: Succeeded
> > > MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> > > (Schedule 1). The last step to run was step 1 (Step 1).
> > >
> > >
> > > the closest i can get to this is to use the following script as the next
> > > step after the backups have completed:
> > >
> > > DECLARE @.msg nvarchar(4000)
> > > Set @.msg = REPLACE("Error: [A-ERR]
> > > Severity: [A-SEV]
> > > Date: [STRTDT]
> > > Time: [STRTTM]
> > > Database: [A-DBN]
> > > Message: [A-MSG] ", "'", "") --'
> > >
> > > EXEC sp_send_cdosysmail 'fromaddress',
> > > 'toaddress',
> > > 'subject line',
> > > @.msg
> > >
> > > but all this produces is the following:
> > >
> > > Error:
> > > Severity:
> > > Date: 20061120
> > > Time: 111358
> > > Database:
> > > Message:
> > >
> > > i dont really want to create separate jobs and alerts for each individual
> > > database so i was wondering if anyone had any ideas at all?!
> > >
> > > thanks in advance
> > >
> > > stu
> > >

CDOSYS/Maintenance plan help please!

Hi there,
I have CDOSYS email working and correclty firing off emails for configured
alerts but what i'm trying to do is get CDOSYS to send an email when a
maintenance plan job successfully backups all user databases (we have
aproximately 16). what i'm after is the same information that sql mail used
to send:
JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
run on 19/11/2006 at 19:00:01
DURATION: 0 hours, 15 minutes, 1 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Schedule 4
(Schedule 1). The last step to run was step 1 (Step 1).
the closest i can get to this is to use the following script as the next
step after the backups have completed:
DECLARE @.msg nvarchar(4000)
Set @.msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
EXEC sp_send_cdosysmail 'fromaddress',
'toaddress',
'subject line',
@.msg
but all this produces is the following:
Error:
Severity:
Date: 20061120
Time: 111358
Database:
Message:
i dont really want to create separate jobs and alerts for each individual
database so i was wondering if anyone had any ideas at all?!
thanks in advance
stuStu,
What you can do is the following :
In the Job of the Maintenance Place, add extra steps ( Success & Failure ),
and associate the failure or the sucess of the maitenance plan step to the
respective step ( sucess or failure ).
Best Regards,
Paulo Conde?a
"Stu" wrote:

> Hi there,
> I have CDOSYS email working and correclty firing off emails for configured
> alerts but what i'm trying to do is get CDOSYS to send an email when a
> maintenance plan job successfully backups all user databases (we have
> aproximately 16). what i'm after is the same information that sql mail us
ed
> to send:
> JOB RUN: 'DB Backup Job for DB Maintenance Plan 'User Backups'' was
> run on 19/11/2006 at 19:00:01
> DURATION: 0 hours, 15 minutes, 1 seconds
> STATUS: Succeeded
> MESSAGES: The job succeeded. The Job was invoked by Schedule 4
> (Schedule 1). The last step to run was step 1 (Step 1).
>
> the closest i can get to this is to use the following script as the next
> step after the backups have completed:
> DECLARE @.msg nvarchar(4000)
> Set @.msg = REPLACE("Error: [A-ERR]
> Severity: [A-SEV]
> Date: [STRTDT]
> Time: [STRTTM]
> Database: [A-DBN]
> Message: [A-MSG] ", "'", "") --'
> EXEC sp_send_cdosysmail 'fromaddress',
> 'toaddress',
> 'subject line',
> @.msg
> but all this produces is the following:
> Error:
> Severity:
> Date: 20061120
> Time: 111358
> Database:
> Message:
> i dont really want to create separate jobs and alerts for each individual
> database so i was wondering if anyone had any ideas at all?!
> thanks in advance
> stu
>|||Paulo,
thank you for that, that has worked.
stu
"Paulo Conde?a" wrote:
[vbcol=seagreen]
> Stu,
> What you can do is the following :
> In the Job of the Maintenance Place, add extra steps ( Success & Failure )
,
> and associate the failure or the sucess of the maitenance plan step to the
> respective step ( sucess or failure ).
> Best Regards,
> Paulo Conde?a
> "Stu" wrote:
>|||Stu,
You know where to post, when you have any doubt.
Best Regards,
Paulo Conde?a
"Stu" wrote:
[vbcol=seagreen]
> Paulo,
> thank you for that, that has worked.
> stu
>
> "Paulo Conde?a" wrote:
>

CDOSYS object not working

I am now trying to send email using CDOSYS from a stored procedure on a
Windows 2003 server and Sql Server 2000.
I got this code from the net and trying to figure out why it won't work.
It seems to work ok until I do the "send".
I am getting an error:
Source: CDO.Message.1
Description: The transport failed to connect to the server.
Is this the SMTP server or the code that is using the microsoft addresses
(ie. http://schemas.microsoft.com/cdo/co...ation/sendusing)?
My code is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%
Declare @.From varchar(100) , @.To varchar(100) , @.Subject varchar(100), @.Body
varchar(4000)
/ ****************************************
**
This stored procedure takes the parameters and sends an e-mail. All the mail
configurations are hard-coded in the stored procedure. Comments are added to
the stored procedure where necessary. References to the CDOSYS objects are
at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp
****************************************
***/
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)
Select @.From = 'tfs@.ftsolutions.com',@.To = 'tfs@.ftsolutions.com', @.Subject =
'Backup Notification', @.Body='The Body of the message'
--***** Create the CDO.Message Object *****
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
print '@.hr after Creating CDO.Message'
print @.hr
--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
print '@.hr after the sendUsing line'
print @.hr
-- This is to configure the Server Name or IP address.
print 'Before the Smtp Server Setup'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value',
'smtp.earth.com'
print '@.hr After setting up the SMTP server'
print @.hr
-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
print '@.hr after Update'
print @.hr
-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
print @.hr
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject
print @.hr
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
print @.hr
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL
print @.hr
-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%
All the "print @.hr" lines are all showing 0, except the last one that is
showing: -2147220973.
Thanks,
TomHave you tried using xp_smtp_sendmail instead? I believe it's easier
to use than CDOSYS.
I found these links on Google Groups:
http://www.sqldev.net/xp/sp_smtp_sendmail.htm
http://www.aspfaq.com/2403
HTH
Barry|||"Barry" <barry.oconnor@.manx.net> wrote in message
news:1139940520.616802.88260@.g47g2000cwa.googlegroups.com...
> Have you tried using xp_smtp_sendmail instead? I believe it's easier
> to use than CDOSYS.
I did get it to work if I used the IP address instead of the name address.
I was going to look at sp_smtp_sendmail when Tibor mentioned it. But I did
get CDOSYS working. Is sendmail better?
Thanks,
Tom
> I found these links on Google Groups:
> http://www.sqldev.net/xp/sp_smtp_sendmail.htm
> http://www.aspfaq.com/2403
> HTH
> Barry
>|||Personally, I've never used it. I have read about it and seems quite
good.
I searched Google and I think general opinion is that it's easier to
use and has better scalability.
I guess it's a question of personal preference.
Barry|||I was reading about it also and found that the drawback was that you need an
email client to use it. CDOSYS doesn't need this.|||xp_smtp_sendmail doesn't need an email client. The old x_sendmail does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ezp9AvZMGHA.1536@.TK2MSFTNGP11.phx.gbl...
>I was reading about it also and found that the drawback was that you need a
n email client to use
>it. CDOSYS doesn't need this.
>

CDOSYS Mail Using SQL Server

The procedure works without problems for the basic 'textbody' and 'htmlbody' type messages. The problem is I would like to get the CreateMHTMLBody method working. This works beautifully as a VBScript which I'm attempting to convert to SQL.

CREATE PROCEDURE usp_send_cdosysmail
@.From_Addr VARCHAR(500) ,
@.To_Addr VARCHAR(500) ,
@.Subject VARCHAR(500),
@.Body VARCHAR(4000) ,
@.SMTPserver VARCHAR(25) = 'localhost',
@.BodyType VARCHAR(10) = 'textbody'
AS

DECLARE @.imsg INT
DECLARE @.hr INT
DECLARE @.source VARCHAR(255)
DECLARE @.description VARCHAR(500)
DECLARE @.output VARCHAR(1000)

EXEC @.hr = sp_oacreate 'cdo.message', @.imsg out
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @.SMTPserver
EXEC @.hr = sp_oamethod @.imsg, 'configuration.fields.UPDATE', NULL

EXEC @.hr = sp_oasetproperty @.imsg, 'to', @.To_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'from', @.From_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'subject', @.Subject

IF @.BodyType <> 'URL'
BEGIN
EXEC @.hr = sp_oasetproperty @.imsg, @.BodyType, @.Body
END
ELSE
BEGIN
-- Generate The Body From The Input URL
EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''
END

EXEC @.hr = sp_oamethod @.imsg, 'send', NULL

When I test the "EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''" step it gives me a blank email.
The return code (@.hr) for that step is -2147211483 (x80042725).
There is no error message. Search as I might, I haven't been able to find a reference to that error.I found the answer:
EXEC @.hr = sp_OAMethod @.imsg, 'CreateMHTMLBody', NULL, @.body, 0, '',''
works!!!!

CDOSYS email body truncated

I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- Save the configurations to the message object.
exec @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
exec @.hr = sp_OASetProperty @.iMsg, 'To', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).

> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Cdosys

I am trying to send job status information using smtp via cdosys. I have it emailing but I want to pass the servername and jobname in the email. Can anyone assist?

***oops, here is the SP and the command ran from the job steps;;;

************************************************** *****

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@.From varchar(100) ,
@.To varchar(100) ,
@.Subject varchar(100)=" ",
@.Body varchar(4000) =" "
/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpserver'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
************************************************** ****

declare @.Body varchar(4000)
select @.Body = 'Job Succeeded'
exec sp_send_cdosysmail 'me@.you.com','you@.me.com','servername Jobname',@.Body

ThanksAnyone? Bueller?