Active X Script fails in Job

Question

Active X Script fails in Job

Coldin Alaska

SSC Veteran

Points: 270
More actions
January 29, 2007 at 5:03 pm

#369086

I created a dts that has 2 parts: 1. truncate a table. 2. Run an active x script that reads the file names in a folder and imports them into a table. The dts works, but when it is called from a job then it fails. The only message I get is the job failed. Here is the active x script that fails when run as a job:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Const adLockOptimistic = 3
Const adUseClient = 3
Const adCmdStoredProc = 4
Set objConn = CreateObject("ADODB.Connection")
Set objRst = CreateObject("ADODB.Recordset")
Set objCmd = CreateObject("ADODB.Command")
objConn = "Provider=MSDataShape.1;Persist Security Info=False;Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=MyDB;Data Provider=SQLOLEDB.1"

objRst.CursorLocation = adUseClient
objRst.Open "Select * From tblDirFiles", objConn, adOpenStatic, adLockOptimistic
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("\\edy1\edy$\folder1\folder2")
Set Files = folder.Files
For Each File In Files
    objRst.AddNew
        objRst("NameOfFile") = File.NAME
        objRst("FileDate") = File.DateCreated
        objRst("FileSize") = File.Size
    objRst.Update
Next
objRst.Close
Set Files = folder.Files
Set File = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function

Through the process of gradually adding lines of code, I found out that it fails on the line:
Set folder = fso.GetFolder("\\edy1\edy$\folder1.\folder2")
Does anyone out there have any idea on why the job fails. Any help, ideas or suggestions will be appreciated. Thanks much.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

It might be a permissions thing... Check if the account associated with the Sql Server Agent has permissions on this folder.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Coldin Alaska SSC Veteran Points: 270 More actions · Answer 2

Thanks for your response. But according to my dba:

The account has Domain Admin priv, so that isn’t the problem.

Perhaps I should add, that the job runs fine on our production server, but fails on our 2 test servers, even though the dts runs fine on those servers. My dba thinks that there is something different between the production and test environments are causing this problem.

Does anyone have any more ideas?

Leeland SSCrazy Eights Points: 8959 More actions · Answer 3

When Ever I get into trouble with ActiveX I use variables to display what the files names/pathways...ect I am passing to make sure they are correct. use a msgbox (variable) right before you have the error to see what the actual path is.

Then if you can, log into the SQL server under the account that is running agent and attempt to get to the pathway. That way you should be able to rule out any acces issues with that account as well as verifying the correct pathway.

Coldin Alaska SSC Veteran Points: 270 More actions · Answer 4

Lee,

Thanks for your reply. I tried to use msgbox in the active x script, but apparently it is not recognized as an object. If it can be done could you send me a sample line of code on how to do it? Also, I can run this code as a function in Microsoft Access and debug the variables and it runs fine. However, when this script is called from a sql server job, then it fails on both my test servers which uses Windows 2000 as the operating system. The odd thing is that the script runs fine as a dts on both systems.

The job does run on my live server which uses windows 2003.

Leeland SSCrazy Eights Points: 8959 More actions · Answer 5

Here is an example of a ActiveX script I wrote that looks to see if there are any folders within a given folder and then can use a message box to pop up the size of the folder.

It isn't perfect but it works and hopefully is an example that helps...

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------

'Check For Previous File That Is Not Processed In Either The File_Temp_In or File_Process Directories

'-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Function Main()

IF ShowFolderSize = True THEN

Main = DTSTaskExecResult_Success

ELSE

Main = DTSTaskExecResult_Failure

END IF

End Function

'****************************************************************************************************************************************

'Checks the Folder Size of both the Temp and Process folders and based on the size it returns a Boolean True or False.

'True = Folder Size equal to Zero

'False = Folder Size greater than Zero

'****************************************************************************************************************************************

Function ShowFolderSize()

Dim FileSys

Dim Temp_Folder_Size

Dim Temp_FolderPath

Dim Temp_FolderSize

'------------------------------------

'Creating the File Object

'------------------------------------

Set FileSys = CreateObject("Scripting.FileSystemObject")

'------------------------------------------------------------------------

'Assigning the File Object to the Folder Property

'-------------------------------------------------------------------------

SET Temp_FolderSize = FileSys.GetFolder("\\ds06702\e$\nts\File_In_Temp")

'------------------------------------------------------------------------------------

'Checks the Folder size and assigns it to the variable

'------------------------------------------------------------------------------------

Temp_Folder_Size = (Temp_FolderSize.size)

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

'Checks the Temp folder size, if it is empty then the no file was successfully downloaded and should report a failure.

'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF Temp_Folder_Size = 0 Then

ShowFolderSize = False

ELSE

ShowFolderSize = True

END IF

'------------------------------------------------------------------------------------

'TESTING Message Box

'------------------------------------------------------------------------------------

' msgbox (Temp_Folder_Size)

End Function

Coldin Alaska SSC Veteran Points: 270 More actions · Answer 6

Thanks again for your response. This is my code altered to include msgbox:

Const adLockOptimistic = 3

Const adUseClient = 3

Const adCmdStoredProc = 4

Const FolderName = "\\edpy\exx$\myfolder\mysubfolder"

Set objConn = CreateObject("ADODB.Connection")

Set objRst = CreateObject("ADODB.Recordset")

Set objCmd = CreateObject("ADODB.Command")

objConn = "Provider=MSDataShape.1;Persist Security Info=False;Data Source=Myserver;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Provider=SQLOLEDB.1"

objRst.CursorLocation = adUseClient

objRst.Open "Select * From myTable", objConn, adOpenStatic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

Msgbox(FolderName)

Set folder = fso.GetFolder(FolderName)'Fails here

Set Files = folder.Files

For Each File In Files

objRst.AddNew

objRst("NameOfFile") = File.NAME

objRst("FileDate") = File.DateCreated

objRst("FileSize") = File.Size

objRst.Update

Set Files = folder.Files

Set File = Nothing

Set fso = Nothing

I get the msgbox prompt when I run this as an active x script in a data transformation service. The folder variable is correct. However, the job still fails.

Leeland SSCrazy Eights Points: 8959 More actions · Answer 7

All I can say is attempt to log into the sql server with the account is running the agent.

Once you log in, try to get to that pathway.

OR you can change the path in the activeX to something local to make sure it works vs. having something wrong with your code.

Also, what is the error message say when it errors out? a Syntax error or something different?

Coldin Alaska SSC Veteran Points: 270 More actions · Answer 8

My dba logged onto the server as the system manager and was able to get to the path, no problem. Also, she ran a dts containing my script with the msgbox and the correct path was displayed. So she says it is not a permissions problem.

I had already tried to change the path to a folder on my hard drive, which I have full permissions to and the Job still failed.

When I try to run the job no matter what path I use, all I get is a message saying job failed at the step that runs the active x script.

Leeland SSCrazy Eights Points: 8959 More actions · Answer 9

So the account that runs the SQL Agent service on the TEST servers is the same as is on the server that is failing?

Can you post the Job History from the job failure?

Who is the owner of the DTS in Production?

Who is the owner of the job in Production?

Who is the owner of the DTS in Test?

Who is the owner of the job in Test?

Changing the path to your harddrive will fail because the service accounts most likely do NOT have rights to your harddrive, I meant to the local drive on the SQL server...ect.

dJeePe Say Hey Kid Points: 712 More actions · Answer 10

sweetie,

1. If the package fails, you can find out more about the error if you double click the failed step when it pops up the failed dialog. Yes, I didn't know it could be double clicked either.

2. If the job fails, right click on the job and choose history. Check the box on top right to show details. Then look under it which have some more logging messages that might help. Also turn on logging on that job.

3. Read and understand this completely KB269074

This is happening to me and I haven't figured it out yet. In my case, even the package doesn't run on my test machine, it gives me "function not found" error which is completely meaningless because the code is identical. If someone else created the job then try recreating it yourself to see if it works. Creating and scheduling the job/package from scratch seems to work sometimes.

happy to help, and don't forget to post back here, if you resolve it. We'd like to know what happened.

3ntropy

Coldin Alaska SSC Veteran Points: 270 More actions · Answer 11

Hurray! My dba found a solution:

This DLL had to be re-registered on the servers:

regsvr32.exe “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\axscphst.dll”

Now the job works on the test servers.

Thanks for your attention to this matter.