December 16, 2005 at 8:46 am
Hello,
I have a problem with a DTS. When i execute it directly, it works but when it is executed with SQLAgent i have the following error on line 66 (fil1.copy) :
Error Description: Invalid procedure call or argument
Can you help me ???? Thanks.
'**********************************************************************
' Script Visual Basic ActiveX
'************************************************************************
Public Function Main()
'+---------------------------------------+
'| Déclaration variables |
'+---------------------------------------+
Dim BlnErreur
'+---------------------------------------+
'| Initialisation variables |
'+---------------------------------------+
BlnErreur = False
'+---------------------------------------+
'| Appel des fonctions |
'+---------------------------------------+
BackupTest
If Not BlnErreur Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
Public Sub BackupTest()
Dim fso ' New FileSystemObject
Dim DateTmp
Dim Heure
Dim DateComplete
Dim fil1, fil2
Dim Str_def_variable
Dim Str_val_variable
str_sql = "SELECT Str_def_variable, Txt_val_variable, Int_val_variable, Txt_val_variable FROM [Grc_Sofinco].[dbo].[Variables_Tb] WHERE (Str_def_variable LIKE 'Path%') order by Num_sequence"
Set Rcs_Variables_Tb = ouvrirRecordset(str_sql)
Rcs_Variables_Tb.movefirst
While Not Rcs_Variables_Tb.EOF
Str_def_variable = Rcs_Variables_Tb("Str_def_variable")
Str_val_variable = Rcs_Variables_Tb("Txt_val_variable")
If Str_def_variable = "Path_ExportAlertes" Then
Path_ExportAlertes = Str_val_variable '1
End If
Rcs_Variables_Tb.MoveNext
Wend
Rcs_Variables_Tb.Close
DateTmp = Right(CStr(Date), 4) + Mid(CStr(Date), 4, 2) + Left(CStr(Date), 2)
Heure = Left(CStr(Time), 2) + Mid(CStr(Time), 4, 2)
DateComplete = DateTmp + Heure
Set fso = CreateObject("Scripting.FileSystemObject")
'Set fil1 = fso.GetFile(Path_ExportAlertes & "AlertesInvalides.txt")
Set fil1 = fso.GetFile("\\icmrecamo\Alertes\ExportAlertes\AlertesInvalides.txt")
fil1.Copy ("\\icmrecamo\Alertes\AlertesInvalides" & DateComplete & ".txt")
Set fil2 = fso.GetFile(Path_ExportAlertes & "AlertesNonAbouties.txt")
fil2.Copy (Path_ExportAlertes & "AlertesNonAbouties" & DateComplete & ".txt")
End Sub
'********************************************************************************************************************************
' fonction : ouvrirRecordset
' description : instancie un objet recordset
'********************************************************************************************************************************
Public Function ouvrirRecordset(strSQL)
Dim adOpenStatic
Dim adLockReadOnly
Dim adUseClient
adOpenStatic = 3
adLockReadOnly = 1
adUseClient = 3
Set ouvrirRecordset = CreateObject("adodb.recordset")
ouvrirRecordset.CursorLocation = adUseClient
ouvrirRecordset.Open strSQL, "Provider=SQLOLEDB.1;Password=grcstat;Persist Security Info=True;User ID=statgrc;database=Grc_Sofinco;Data Source=ICMRECAMO", adOpenStatic, adLockReadOnly
If ouvrirRecordset.activeconnection.errors.Count > 0 Then
Set ouvrirRecordset = Nothing
Exit Function
End If
End Function
December 18, 2005 at 6:48 pm
how did you excute on sqlAgent?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply