Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2011
    Posts
    29

    Question Unanswered: How to move a Batch file using T-SQL in SQL Server 2000?

    Hi All,

    From the ActiveX script below, I understood that the batch file is being moved to a different location. But, there is some text file in between which I don't understand exactly and also the .log file. Also, I don't understand the CmdLine queries in between. Can someone help me out with a Stored Proc for the same ActiveX Script.

    Code:
    Dim FileName
    Dim SourceFileName
    Dim SourceFilePath
    Dim SourceServerName
    Dim DestinationFileName
    Dim UserID
    
    Function  Main()
    
        FileName = "D:\Customer\LogFileScripts\Trans2.bat"
      
        SourceFilePath = DTSGlobalVariables("sNodePath") & DTSGlobalVariables("sNode1")
        
        SetSourceFileName()
        
        DestinationFileName = DTSGlobalVariables("dPath")  &  DTSGlobalVariables("dMonLog1")
        SourceServerName = DTSGlobalVariables("sNode")
        UserID = DTSGlobalVariables("uID")
       ' PassPhrase = "CustArchive"
    
    
        DeleteFile(FileName)
        DeleteFile(DestinationFileName)
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.CreateTextFile(FileName)
        Set oFile = fso.GetFile(FileName)
        Set CmdFile = oFile.OpenAsTextStream(2)
    
    
    
        if (IsNull(DTSGlobalVariables("sNode1"))) Then
            CmdLine = CmdLine & "copy /Y " & DTSGlobalVariables("dPath")  & "FormatFiles\defaultlog.txt" & " " & DestinationFileName & vbCr & vbLf
            CmdLine = CmdLine & "COPY " & DTSGlobalVariables("dMonLog1") & " /B + ,, /Y"
        Else
            CmdLine = "for /f ""tokens=1"" %%a in (" & vbCr & vbLf
            CmdLine = CmdLine & " 'vsh -l " & DTSGlobalVariables("uID") & "  -auth publickey -i ""d:\program files\Identity"" "
            CmdLine = CmdLine & SourceServerName & " ls " & SourceFilePath & SourceFileName &"'"& vbCr & vbLf
            CmdLine = CmdLine & " ) do (" & vbCr & vbLf 
            CmdLine = CmdLine & "   if %%a == " &  SourceFilePath & SourceFileName
            CmdLine = CmdLine & " (set testBool=true)" & vbCr & vbLf & ")" & vbCr & vbLf
            CmdLine = CmdLine & "if %testBool% == true" & " (" & vbCr & vbLf
            CmdLine = CmdLine & "   vcp.exe -auth publickey -i ""d:\program files\Identity"" "
            CmdLine = CmdLine & " " & UserID & "@" & SourceServerName & ":" & SourceFilePath & SourceFileName & " " & DestinationFileName
            CmdLine = CmdLine & vbCr & vbLf & ")"  & " else " & "(" & vbCr & vbLf
            CmdLine = CmdLine & "        copy /Y " & DTSGlobalVariables("dPath")  & "FormatFiles\defaultlog.txt" & " " & DestinationFileName & vbCr & vbLf
            CmdLine = CmdLine & "        copy " & DTSGlobalVariables("dMonLog1") & " /B + ,, /Y" & vbCr & vbLf & ")"
        End If
    CmdFile.WriteLine "set testBool=false" & vbCr & vbLf & vbCr & vbLf & "D:" & vbCr & vbLf & "cd \ARCH" & vbCr & vbLf
    
    
    
        CmdFile.WriteLine CmdLine
        CmdFile.Close
        Set CmdFile = nothing
        Set oFile = nothing
        Main = DTSTaskExecResult_Success
    
    End Function
    
    
    
    Function  DeleteFile(FileName)
    
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.CreateTextFile(FileName)
        Set oFile = fso.GetFile(FileName)
        oFile.delete true
        Set oFile = nothing
    
    End Function
    
    
    Function SetSourceFileName()
    
        SourceFileName = DTSGlobalVariables("sMonLog")  &  "."   &  DTSGlobalVariables("glCurYear") & "-" & DTSGlobalVariables("glCurMonth") & "-" & DTSGlobalVariables("glCurDay") & ".log"
    
    End Function

    ------
    Thanks
    Mr.Bean
    Last edited by Mr.Bean; 12-29-11 at 13:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •