Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 5th, 2004, 03:33 PM
Registered User
 
Join Date: Feb 2004
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

 
Old February 5th, 2004, 03:45 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
 
Old February 5th, 2004, 04:23 PM
Registered User
 
Join Date: Feb 2004
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.

 
Old February 5th, 2004, 04:29 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
 
Old February 5th, 2004, 04:48 PM
Registered User
 
Join Date: Feb 2004
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


 
Old February 5th, 2004, 06:47 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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.
 
Old February 6th, 2004, 10:46 AM
Registered User
 
Join Date: Feb 2004
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

 
Old June 16th, 2004, 08:07 AM
Registered User
 
Join Date: Jun 2004
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.

 
Old March 17th, 2005, 12:20 AM
Registered User
 
Join Date: Mar 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.