Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 5th, 2004, 03:33 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bjrichard
Default Error when code run as a job

This code runs in the DTS designer, but not as a job.
 Set objFolder = myOlNameSpace.Folders("Public Folders")

Any Thoughts

  #2 (permalink)  
Old February 5th, 2004, 03:45 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi there,

If you run a package from the Designer it runs in *your* security context. So, basically, it can do the things you are allowed to do.

If you run it as a job however, it runs as the account that SQL Server Agent is started with. You'll find the account in the Services dialog of Windows, or on the Properties dialog for the SQL Server Agent in the Enterprise Manager.

Change the account to a (domain) account with (just) enough permissions, and your package will run.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #3 (permalink)  
Old February 5th, 2004, 04:23 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bjrichard
Default

Quote:
quote:Originally posted by bjrichard
 This code runs in the DTS designer, but not as a job.
 Set objFolder = myOlNameSpace.Folders("Public Folders")

Any Thoughts
The account is in the Domain Group. Any other thoughts.
 Here is the error.


Thanks
Executed as user: HRA\sqlagent. ...ing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: 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 51 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: 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 51 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution . The step failed.

  #4 (permalink)  
Old February 5th, 2004, 04:29 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hmmmmm, tricky.

Does the domain account have sufficient access rights to the public folders? What happens if you log on on your machine with that account and then try to run the package?

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #5 (permalink)  
Old February 5th, 2004, 04:48 PM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bjrichard
Default

I logged onto the sevrer using the account.
Open outlook to public folders.
Also ran the job in the DTS designer OK.
Ran as a job an it fails.

I think it has something to do with the folder object.

I'm going to send the other code.
Maybe that will give you a clue.

Thanks

Dim myOlApp,itms
Dim myOlNameSpace
Dim objFolder,FolderPath,fldr,GetFolder,strTemplatePat h,itm
Dim ItemCount, UnReadItemCount
Dim strSheet,appExcel,wkb,wks,rng
FolderPath = "Public Folders\All Public Folders\HRAmerica Contacts\Dental Care Alliance"
Dim strRange,strASCII, lngASCII ,objCDO,objSession,objAMSession1
   On Error Resume Next
strFolderPath = Replace(FolderPath,"/","\")
aFolders =Split(FolderPath, "\")



Set xlapp = CreateObject("Excel.Application")
Set myOlApp = CreateObject("Outlook.Application")
Set myOlNameSpace = myOlApp.GetNamespace("MAPI")



strTemplatePath = "\\HRANTS02\E$\ExCelExports\"
strSheet = "ContactsII.xls"
strSheet = strTemplatePath & strSheet

   'Adjust the following number to be 1 less than the row number of the
   'first body row
 i = 3

   'Initialize column letters with 64, so the first letter used will be A
lngASCII = 64

Set appExcel = GetObject(, "Excel.Application")

appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
appExcel.Application.Visible = False



  Set objFolder = myOlNameSpace.Folders("Public Folders") *****fails

For i = 1 to Ubound(aFolders)

    Set objFolder = objFolder.Folders(aFolders(i))


    Next
    Set GetFolder = objFolder

    Set itms = objfolder.Items
    ItemCount = itms.Count
    UnReadItemCount = objFolder.UnReadItemCount

 If objFolder Is Nothing Then

  End If

If objFolder.DefaultItemType <> 2 Then

   End If

lngCount = objFolder.Items.Count

   If lngCount = 0 Then


   Else
end if

For Each itm In objFolder.Items

      If itm.Class = 40 Then
         'Process item only if it is a contact item
         i = i + 1
         lngASCII = lngASCII + 1
         strASCII = Chr(lngASCII)
         strRange = strASCII & CStr(i)
         Set rng = wks.Range(strRange)
         If itm.Title <> "" Then rng.Value = itm.Title

         lngASCII = lngASCII + 1
         strASCII = Chr(lngASCII)
         strRange = strASCII & CStr(i)
         Set rng = wks.Range(strRange)
         If itm.FirstName <> "" Then rng.Value = itm.FirstName

         lngASCII = lngASCII + 1
         strASCII = Chr(lngASCII)
         strRange = strASCII & CStr(i)
         Set rng = wks.Range(strRange)
         If itm.MiddleName <> "" Then rng.Value = itm.MiddleName

         lngASCII = lngASCII + 1
         strASCII = Chr(lngASCII)
         strRange = strASCII & CStr(i)
         Set rng = wks.Range(strRange)
         If itm.LastName <> "" Then rng.Value = itm.LastName



         lngASCII = lngASCII + 1
         strASCII = Chr(lngASCII)
         strRange = strASCII & CStr(i)
         Set rng = wks.Range(strRange)
         If itm.Email1Address <> "" Then
            rng.Value = itm.Email1Address
         End If

         On Error Resume Next
         'The next line illustrates the syntax for referencing
         'a custom Outlook field

         If itm.Email1Address <> "" Then
            rng.Value = itm.Email1Address
         End If

         lngASCII = 64
      End If
   Next




xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close True


 xlApp.Quit


Set xlApp = Nothing
Set appExcel = nothing
Set myOlApp = Nothing







    Main = DTSTaskExecResult_Success

If Err.Number <> O Then

Main = DTSTaskExecResult_Failure


  #6 (permalink)  
Old February 5th, 2004, 06:47 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hmmm, this Public Folders thing is not really my cup of tea. I used this a few times as well, and I always had unexpected troubles.

Could it be that GetNamespace requires a true, logged on session? That is, that it won't correctly start the session for the "current user" when the package runs as a job?

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #7 (permalink)  
Old February 6th, 2004, 10:46 AM
Registered User
 
Join Date: Feb 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bjrichard
Default

I will look into adding some code to log onto OUTLOOK from
the job.

Thanks

  #8 (permalink)  
Old June 16th, 2004, 08:07 AM
Registered User
 
Join Date: Jun 2004
Location: Birmingham, , United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default


Did you ever solve this problem as I am having the same difficulties with my current project.

  #9 (permalink)  
Old March 17th, 2005, 12:20 AM
Registered User
 
Join Date: Mar 2005
Location: Canberra, ACT, Australia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have the same problem, anyone has already resolve this problem?

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Package runs, Job doesn't run polofson SQL Server DTS 0 July 28th, 2006 10:41 AM
Getting a Run-Time Error with this code.. Smythe Access VBA 2 May 28th, 2006 07:06 PM
Problem with the views execution when run as a job sreepada SQL Server 2000 0 March 18th, 2006 08:39 AM
Job fails & need to use domain\user acct to run kiwikencox SQL Server 2000 1 October 17th, 2005 09:30 PM
Cron Job Error - Need Help jkirkpatrick Linux 2 August 5th, 2005 05:51 AM



All times are GMT -4. The time now is 02:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.