Active X Script fails in Job
-
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.
-
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?
-
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.
-
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.
-
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
-
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
Next
objRst.Close
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.
-
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?
-
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.
-
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.
-
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
-
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply