MS SQL Server DTS - DTS job fails if scheduled on VB email step.

Author

DTS job fails if scheduled on VB email step.

mutley1

2006-01-24, 7:23 am

Hi All,

I have a package that sends a mail using VB as opposed to Sendmail (I'm no
expert on VB so apologies in advance). Basically it will retrieve the users
that match criteria, insert images and information and send the mail. This
works fine from DTS designer, but when run as a scheduled job, or using
windows scheduler and a batch file, it fails with the error below. Beginning
of VB code below. I have included line numbers as per the VB editor sees it.

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Error Code: 4096
Error Source= Microsoft Outlook
Error Description: Could not complete the operation because the service
provider does not support it.

Error on Line 18

Error code: 800403FE
\Error Help File:sqldts80.hlp
Error Help Context ID:4500

I have seen numerous posts where no resolution was specifically found (for
my issue), but here's the answers to a few questions that I know I'll be
asked:

1. Outlook is installed on the server.
2. I am running the package when actually logged onto the server.
3. I am logged in as the SQL Agent domain account on the server.
4. The said domain account DOES have access to the outlook profile.
5. It works every time from DTS designer - only fails when scheduled via SQL
agent or Windows Scheduler.
6. Windows Server 2000 (SP3) and SQL 2000 (SP3), Outlook 2000.

CODE:
0'******************
********************
***************
1' Visual Basic ActiveX Script
2'******************
********************
***************
3
4Function Main()
5
6'create new Outlook MailItem
7 Set objApp = CreateObject("Outlook.Application")
8Set mapiNameSpace = objApp.GetNameSpace("MAPI")
9 Set l_Msg = objApp. CreateItem(olMailIte
m)
10
11' initialize CDO session
12 'Set oSession = CreateObject("MAPI.Session")
13 'oSession.Logon "xxxxxxx", "xxxxxxx", False, False
14
15'add graphic as attachment to Outlook message
16' change path to graphic as needed
17 Set colAttach = l_Msg.Attachments
18 Set l_Attach = colAttach.Add("C:\Program Files\picture.jpg")
19 l_Msg.Close olSave
20 strEntryID = l_Msg.EntryID
21 Set l_Msg = Nothing
22' *** POSITION CRITICAL *** you must dereference the
23' attachment objects before changing their properties
24' via CDO
25 Set colAttach = Nothing
26 Set l_Attach = Nothing

I believe that there is some kind of permissions issue when it tries to
insert the JPG into the mail as a scheduled job. As I say, it works when I am
logged in as the SQL Agent and Outlook is completely closed. I see an outlook
email window pop up briely on the screen (split second) when it creates the
mail. However, whenever it is scheduled via SQL or batch file, it fails.

Matt.

Allan Mitchell

2006-01-24, 8:24 pm

Hello mutley1,

Ok So you've tried virtually everything I would have.

What account owns the SQL Server Agent Job? member of sysadmin? This matters
as to what the execution account will be.

Allan

> Hi All,
>
> I have a package that sends a mail using VB as opposed to sendmail
> (I'm no expert on VB so apologies in advance). Basically it will
> retrieve the users that match criteria, insert images and information
> and send the mail. This works fine from DTS designer, but when run as
> a scheduled job, or using windows scheduler and a batch file, it fails
> with the error below. Beginning of VB code below. I have included line
> numbers as per the VB editor sees it.
>
> Error Source: Microsoft Data Transformation Services (DTS) Package
> Error Description:Error Code: 4096
> Error Source= Microsoft Outlook
> Error Description: Could not complete the operation because the
> service
> provider does not support it.
> Error on Line 18
>
> Error code: 800403FE
> \Error Help File:sqldts80.hlp
> Error Help Context ID:4500
> I have seen numerous posts where no resolution was specifically found
> (for my issue), but here's the answers to a few questions that I know
> I'll be asked:
>
> 1. Outlook is installed on the server.
> 2. I am running the package when actually logged onto the server.
> 3. I am logged in as the SQL Agent domain account on the server.
> 4. The said domain account DOES have access to the outlook profile.
> 5. It works every time from DTS designer - only fails when scheduled
> via SQL
> agent or Windows Scheduler.
> 6. Windows Server 2000 (SP3) and SQL 2000 (SP3), Outlook 2000.
> CODE:
> 0'******************
********************
***************
> 1' Visual Basic ActiveX Script
> 2'******************
********************
***************
> 3
> 4Function Main()
> 5
> 6'create new Outlook MailItem
> 7 Set objApp = CreateObject("Outlook.Application")
> 8Set mapiNameSpace = objApp.GetNameSpace("MAPI")
> 9 Set l_Msg = objApp. CreateItem(olMailIte
m)
> 10
> 11' initialize CDO session
> 12 'Set oSession = CreateObject("MAPI.Session")
> 13 'oSession.Logon "xxxxxxx", "xxxxxxx", False, False
> 14
> 15'add graphic as attachment to Outlook message
> 16' change path to graphic as needed
> 17 Set colAttach = l_Msg.Attachments
> 18 Set l_Attach = colAttach.Add("C:\Program Files\picture.jpg")
> 19 l_Msg.Close olSave
> 20 strEntryID = l_Msg.EntryID
> 21 Set l_Msg = Nothing
> 22' *** POSITION CRITICAL *** you must dereference the
> 23' attachment objects before changing their properties
> 24' via CDO
> 25 Set colAttach = Nothing
> 26 Set l_Attach = Nothing
> I believe that there is some kind of permissions issue when it tries
> to insert the JPG into the mail as a scheduled job. As I say, it works
> when I am logged in as the SQL Agent and Outlook is completely closed.
> I see an outlook email window pop up briely on the screen (split
> second) when it creates the mail. However, whenever it is scheduled
> via SQL or batch file, it fails.
>
> Matt.
>

Mutley1

2006-01-25, 3:27 am

Hi Allen,

Yes, it's a domain account with sysadmin in SQL, administrator on the box,
access to the profile it's trying to send with....basically a demi God in the
environment!

The one other thing I have done is dcomcnfg.exe and added the account into
the default perms. We have not had the criteria met yet since i did that to
cause a mail to be sent (live system so can't force through), but it's
bugging me big time now. I knw the permissions are all correct. Short of
being made President, I don't believe this account can get any higher!!!

Sponsored Links