Job Fails

[togatown]

I want to launch a mail routine I created asynchronously using a job.

When I execute spMailerRecipients from the Query Analyzer thus:
exec sp_MailerRecipients @Type='Newsletter'
but when try to launch it from a job it fails?

CREATE Procedure sp_MailerRecipients
@type varchar(13) --run time

--debug only
--declare @type varchar(13)
--Valid Types Newsletter, Specials, Notifications
AS
--debug only
Set @type = 'Newsletter'

Declare @sqlStr as varchar(500)
Declare @TableCreate as varchar(500)
Declare @EDeliver varchar(14)
Declare @Edeliver2 varchar(15)
Declare @EdeliverSort varchar(22)
Declare @EdeliverCreate varchar(26)

Set @EDeliver = ''
Set @EDeliver2 = ''
Set @EDeliverSort = ''
Set @EdeliverCreate = ''

If @type = 'Newsletter'
Begin
Set @EDeliver = 'EmailDelivery,'
Set @EDeliver2 = 'EmailDelivery2,'
Set @EDeliverSort = ' AND EmailDelivery = 1'
Set @EdeliverCreate = LEFT(@Edeliver, LEN(@Edeliver)-1) + ' int, '
End

Set @sqlStr =
'Select * From
(SELECT Customer.ID, FirstName, LastName, EmailAddress, CustomerExt.' + @type + ', ' + @EDeliver + '
''1'' as UserType FROM Customer INNER JOIN CustomerExt ON Customer.ID = CustomerExt.ID
UNION ALL
SELECT CustomerExt.ID, FirstName2, LastName2, EmailAddress2, ' + @type + '2, ' + @EDeliver2 + '
''2'' as UserType FROM CustomerExt)
as dt
Where ' + @type + ' = 1 AND EmailAddress <> ''''' + @EDeliverSort


Set @TableCreate =
'CREATE Table ##Recipients
(ID int, FirstName varchar(30),
LastName varchar(50),
EmailAddress varchar(255), '
+ @type + ' int, ' + @EDeliverCreate +
'UserType int)'

EXEC (@TableCreate)
set @sqlstr = 'Insert into ##Recipients ' + @sqlstr
EXEC (@sqlstr)

Declare @ID int, @FirstName varchar(30), @LastName varchar(50), @Email varchar(255), @Table int
--Debug Only
--Declare @TestMe2 varchar(200)

DECLARE c CURSOR FOR
Select [ID], FirstName, LastName, EmailAddress, UserType From ##Recipients
OPEN c
FETCH NEXT FROM c INTO @ID, @FirstName, @LastName, @Email, @Table

--Debug Only
--Set @TestMe2 = @FirstName + ' ' + @LastName + ' ' + @Email --Debug Only


WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @TestMe2 --Debug Only
EXEC kiosk.sp_MailerSend @ID, @FirstName, @LastName, @Email, @Table, @type
FETCH NEXT FROM c INTO @ID, @FirstName, @LastName, @Email, @Table
--Debug Only
--Set @TestMe2 = @FirstName + ' ' + @LastName + ' ' + @Email --Debug Only
END

CLOSE c
DEALLOCATE c

Drop table ##Recipients

Go


CREATE Procedure sp_MailerSend

@ID int,
@FirstName varchar(30),
@LastName varchar(50),
@RecipientAddress varchar(255), --@Email Passed
@Table int,
@Type varchar(13)

AS

Declare @SenderName varchar(12), @SenderAddress varchar(31),
@RecipientName varchar(80), @MailServer varchar(18), @Subject varchar(200),
@Disclaimer nvarchar(3000), @BodySource varchar(100), @Parameters nvarchar(30)
Declare @resultcode int, @FileSystem int , @FileHandle int,
@DisclaimerFile varchar(75), @FileOutput as varchar(3000)

Set @MailServer = 'mail.bellsouth.net'
Set @DisclaimerFile = 'D:\inetpub\sites\thevineyardfl_no-ip_com\

http://www\newsletter\disclaimer.htm'

Set @BodySource = 'D:\inetpub\sites\thevineyardfl_no-ip_com\

http://www\newsletter\'

+ @Type + '.htm'
Set @RecipientAddress = 'mathew@decourcy.org'

Set @SenderName = 'The Vineyard'
Set @SenderAddress = 'thevineyardfl@thevineyardfl.com'

Set @RecipientName = @FirstName + ' ' + @LastName


Set @Subject = @Type + ' - The Vineyard at Cascades of Isleworth'

Set @Parameters = 'ID=' + Cast(@ID as nvarchar(5)) + '&Table=' + Cast(@Table as nvarchar(5)) + '&Type=' + @Type

EXECUTE @resultcode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
EXECUTE @resultcode = sp_OAMethod @FileSystem, 'OpenTextFile', @FileHandle OUTPUT, @DisclaimerFile
EXECUTE @resultcode = sp_OAMethod @FileHandle , 'ReadALL' , @FileOutput OUTPUT
EXECUTE sp_OADestroy @FileHandle
EXECUTE sp_OADestroy @FileSystem
Select @FileOutput as ReturnVal
Set @Disclaimer = Replace(@FileOutput, 'zzzparameterszzz', @Parameters) + '</body></html>'

SET nocount on

declare @oMail int --Object reference

EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @MailServer
EXEC @resultcode = sp_OASetProperty @oMail, 'CharSet', '2'
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddExtraHeader', NULL, 'X-MimeOLE: Produced The Vineyard at Ilseworth'

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'ContentType', 'text/html'
EXEC @resultcode = sp_OAMethod @oMail, 'GetBodyTextFromFile', NULL, @BodySource, False, False
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Disclaimer


EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off

GO

 

[Catadmin]

What error messages are you getting? And does it only error out when scheduled or does it error out when you run it in QA or start the job manually? (I did't quite understand that part of your comment).



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[SQLBill]

How are you 'launching' it as a job?

Have you right clicked on the failed job and selected View Job History? Do so and check the box in the upper right corner that expands the steps. Then see what the error message is.

-SQLBill

Posting advice: FAQ481-4875

 

[togatown]

Sorry guys I was really tired/frustrated when I wrote this. Wasn't thinking clearly, it was a credential thing...

Toga

 

[SQLBill]

That's okay...it happens. Glad you solved the problem.

Now I have to ask you a favor. Someone else might have this problem in the future and find your thread via a search. Reading the thread they might not know what you mean by "...it was a credential thing.".

Would you please follow this up with a short explanation of what your solution was? And maybe include how you determined it was a credential issue and not something else.

-SQLBill

Posting advice: FAQ481-4875