SQL Agent + Active Scripting + COM+ + DTS Problems

Question

SQL Agent + Active Scripting + COM+ + DTS Problems

kpowers

Old Hand

Points: 309
More actions
March 4, 2004 at 1:46 pm

#65041

Hello All,
I've got a bug that is driving me crazy.  I have a job on a Windows 2000 Server (SQL Server 2000 is on this machine) and when it hits the active scripting step it fails. I get the following Error; "Executed as user: WPPISQL1\sqlservice. ActiveScripting specific error 257 occurred. The step failed." I've parsed the set and get no error. What it does is it executes some custom COM components that control some DTS package located on this machine. TO see where it fails lool below.
Dim adoRs
Dim adoConn
Dim adoCmd
Set adoRs = CreateObject("ADODB.Recordset")
Set adoConn = CreateObject("ADODB.Connection")
Set adoCmd = CreateObject("ADODB.Command")

Dim dtsRun
Dim dtsBUD
Dim dtsBUS
Dim dtsRD
Dim dtsRS
Function Main()
Set dtsRun = CreateObject("WPPI_HDWH_DATA.clsDataLoad") SCRIPT FAILS RIGHT HERE
adoConn.Provider = "SQLOLEDB"
adoConn.ConnectionString = "User ID=WPPI_HDWH;Password=password;" & "Data Source=WPPISQL1;" & "Initial Catalog=WPPI_HDWH"
             adoConn.Open
adoRs.ActiveConnection = adoConn
adoRs.OPen "Select prefix from WPPI_Main_Load_Globals order by prefix"
  Do Until adoRs.EOF
  x=dtsRun.LoadData(strip(adoRs.Fields("prefix").Value))
  if x = true then
   Set dtsBUD = CreateObject("WPPI_HDWH_DATA.clsBUDetail")
   dtsBUD.Load_BU_Detail(strip(adoRs.Fields("prefix").Value))
   Set dtsBUS = CreateObject("WPPI_HDWH_DATA.clsBUSummary")
   dtsBUS.Build_BU_Summary(strip(adoRs.Fields("prefix").Value))
   Set dtsRD = CreateObject("WPPI_HDWH_DATA.clsREVDetail")
   dtsRD.Load_REV_Detail(strip(adoRs.Fields("prefix").Value))
   Set dtsRS = CreateObject("WPPI_HDWH_DATA.clsREVSummary")
   dtsRS.Load_REV_Summary(strip(adoRs.Fields("prefix").Value))
   Set dtsBUD = Nothing
   Set dtsBUS = Nothing
   Set dtsRD = Nothing
   Set dtsRS = Nothing
  end if
  adoRs.MoveNext
             Loop
Set adoRs = nothing
Set adoConn = nothing
Set adoCmd = nothing
Set dtsRun = Nothing
Set dtsBUD = Nothing
Set dtsBUS = Nothing
Set dtsRD = Nothing
Set dtsRS = Nothing
Main = DTSTaskExecResult_Success
End Function
Private Function strip(str)
Do While InStr(str, vbCr)
     str = Replace(str, vbCr, " ")
Loop

Do While InStr(str, vbLf)
     str = Replace(str, vbLf, "")
Loop
strip = Trim(str)
End Function
The COM components are register correctly on the server. The users that the job runs under is set up correctly.
Any one have an idea?

Thanks,
Ken

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

test-131165 Newbie Points: 7 More actions · Answer 1

You have your COM registered directly, or via a Component Services Application. I recommend the latter and use a lan login to authenticate the application package. That we you elliminate any permission related issues with the script and remote access needs. Just a thought.