"Invalid procedure call or argument" with a DTS

Question

"Invalid procedure call or argument" with a DTS

Moi94

SSC Veteran

Points: 269
More actions
December 16, 2005 at 8:48 am

#70035

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Check the user credentials of the login that SQL Agent is started under. Chances are it doesn't have necessary access permissions to a UNC share.

Moi94 SSC Veteran Points: 269 More actions · Answer 2

The SQL Agent is started with the local login SYSTEM. Our system administrator said that this login has the appropriate access to the disk...

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Then you're going to have to make your code more robust & report errors:

Set fil1 = fso.GetFile("\\icmrecamo\Alertes\ExportAlertes\AlertesInvalides.txt")

fil1.Copy ("\\icmrecamo\Alertes\AlertesInvalides" & DateComplete & ".txt")

What happens if the 1st statement fails, and the variable fil1 contains a Nothing reference ? That's exactly the situation that would give you an invalid procedure call ..

Set fil1 = fso.GetFile(\\icmrecamo\Alertes\ExportAlertes\AlertesInvalides.txt)

If fil1 Is Nothing Then

' Do wahtever error handling here, log to Event Log, or package log etc

Else

fil1.Copy ("\\icmrecamo\Alertes\AlertesInvalides" & DateComplete & ".txt")

End If

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

Also note, File.Copy() is a procedure.

That means you either use Call and parentheses, or you omit call and parentheses:

Call fil1.Copy("\\icmrecamo\Alertes\AlertesInvalides" & DateComplete & ".txt")

or

fil1.Copy "\\icmrecamo\Alertes\AlertesInvalides" & DateComplete & ".txt"