Using VBScript to Automate Tasks

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp

  • nice, consice code. Great way to delete old files; didn't realize it was that easy, you did my homework assignment for me. 🙂

    I've created a sproc that ftp's a file, but it's a bastardized version of your code. Good stuff.

    Signature is NULL

  • Thanks Calvin for your compliment. I appreciate it. I am glad you find the article helpful.

  • Very helpful, thanks for sharing

  • Not great for FTP as no error checking done - same could be achieved with a .cmd file using less code with error checking. E.G

    rem %1 is file to be xferred %2 is suffix eg xfr %3 host name %4 is username %5 is password

    cd c:\xfr

    rem IF EXISTS THEN DELETE THE PREVIOUS FILES

    dir c:\xfr\%1.%2 >nul && del c:\xfr\%1.%2

    dir c:\ftpcmd\%1.ftp >nul && del c:\ftpcmd\%1.ftp

    echo open %3 >c:\ftpcmd\%1.ftp

    echo %4>>c:\ftpcmd\%1.ftp

    echo %5>>c:\ftpcmd\%1.ftp

    echo get %1.%2 >>c:\ftpcmd\%1.ftp

    echo bye>>c:\ftpcmd\%1.ftp

    ftp -s:c:\ftpcmd\%1.ftp >c:\scripts_logs\%1FTP.log

    cd c:\scripts

    findstr /c:"226 Transfer complete" C:\scripts_logs\%1FTP.log

    if %ERRORLEVEL% GTR 0 goto FAIL

    exit 0

    :FAIL

    rem - send a mail message or whatever you want

    exit 1

     

  • Forgot to mention - liked the script for deleting files - i shall use that

    Thanks for the code.

  • Well... I continue feeling that implementing a shell/awk in the unix/linux is better and more powerfull, but sometimes anybody require work in WindowsX, your code is great, easy and elegant.

    Tks

    Pepe

  • Great article.

    My collection of VBScipt and batch files has grown over the years as I automate the many tasks that I am responsible for. I've tried designing full-blown applications to handle redundant tasks, but find that when company procedures or standards change frequently, it's easier to edit a VBS or BAT file than recompile an app. (of course I'm mostly a SQL developer, not an app developer)

    The following code from a VBS file that will "Get Latest" version of scripts from a SourceSafe Project folder. (Of course, it might be a little easier to use a batch file for this, but this can be slightly modified to prompt the user for input.)

    Option Explicit

    'Gets the latest version of files in a SourceSafe project folder.

    Dim sSourcePath, sTargetPath, sUsername, sPassword

    'EDIT THESE VARIABLES:

    'You can optionally use the "InputBox" function to prompt for these.

    sSourcePath = "$/Database/Product/Release800" 'from SourceSafe

    sTargetPath = "C:\SourceSafe\GetLatest" 'File System path

    sUserName = "steve" 'for SourceSafe

    sPassword = "password"

    Call GetLatest( sSourcePath, sTargetPath, sUserName, sPassword )

    MsgBox( "'Get Latest' completed!", vbOKOnly+vbInformation, "Get Latest" )

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

    Sub GetLatest( sSourcePathSS, sTargetPathFs, sUser, sPwd )

     Dim sCmd, sSSExePathFs, sSSIniPathFs, sSSLogin, sComspec

     Dim oShell 'Command shell

     Set oShell = CreateObject("WScript.Shell")

     Dim fso 'File System Object

     set fso = CreateObject("Scripting.FileSystemObject")

     sTargetPathFs = CreateDir( sTargetPathFs )

     

     'Create login switch:

     sSSLogin = " -Y" & sUser & "," & sPwd & " "

     'Variables that will likely not change often:

     sComspec = "%COMSPEC% /c "

     sSSIniPathFs = "\\server2\ProductABC\Test\VSS"

     sSSExePathFs = "\\server2\ProductABC\Test\VSS\WIN32\"

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

     ' rem Command Line access to SourceSafe:

     ' set ssdir=%ggssinifile%

     ' ss cp "%ggssfold%" -Y%ggssuser%,%ggsspass%

     ' ss get * -gtul%ggTarget% -Y%ggssuser%,%ggsspass% "-o&SSGetLatest.out"

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

     

     'Use command line to set SourceSafe INI file:

     sCmd = sComspec & "set SSDIR=" & sSSIniPathFs

     Call oShell.Run( sCmd, 1, true )

     'Use command line to set the desired SourceSafe Project folder:

     sCmd = sComspec & sSSExePathFs & "ss cp """ & sSourcePathSS & """" & sSSLogin

     Call oShell.Run( sCmd, 1, true )

     'Build and run the 'Get Latest' command:

     sCmd = sComspec & sSSExePathFs & "ss get * -gtul"

     sCmd = sCmd & fso.GetFolder( sTargetPathFs ).ShortPath

     sCmd = sCmd & sSSLogin & " ""-o&SSGetLatest.out"""

     Call oShell.Run( sCmd, 1, true )

     Set oShell = Nothing

     Set fso = Nothing

    End Sub

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

    Function CreateDir( sPath )

    'Creates a folder if one doesn't exist.

     Dim fso 'File System Object

     set fso = CreateObject("Scripting.FileSystemObject")

     if Not fso.FolderExists( sPath ) then

      'Be sure parent exists, before creating child:

      Call CreateDir( fso.GetParentFolderName( sPath ) )

      fso.CreateFolder( sPath )

     end if

     

     set fso = Nothing

     CreateDir = sPath

    End Function

     

  • In the FTP script, don't forget to delete the FTP answer file when done with it (clean up the temp files) and unistantiate the objects to prevent possible memory leaks.

    Happy Scripting!

  • I almost forgot, in response to Archie's comment above about no error checking, you could use the Exec method instead of run. That way you could parse the std.out string for error verbiage and take appropriate action (Email, Net Send, etc...). An excellent site for command refernce for VBScript, including WSH and ADO is DevGuru.com.

    Happy Scripting!

  • Is this in a .BAT or .SCR file?

  • I just want to say thank you for posting this article.  It was real easy to read, understand and modify for my own use.  There are a lot of coders out there that can write really good code, but it takes another skill to write code as you have done that is easy to read and adapt.

     

    Thanks,

    Ben

  • Wow, I never thought that my little article generated so many great feedbacks! I learned so much from you all!!!

    Sorry for the late reply to all you guys. I've been busy with other things and have neglected checking back the forum from time to time.

    Thanks again.

  • Forfiles is a little known command that is great for iterating over a number of files that meet a condition, it's available in both 2000, XP and 2003 but there are different versions out there depending on servicepack version.  

    For example, I use it in a backup script to remove local backups when they have been moved to network drive:

    ' Delete cab files older than 3 days in local folder

    oWshShell.Run "FORFILES /p ""C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"" /m *.cab /d -3 /c ""cmd /c del @file""" , 0, True

    For all command line options run FORFILES /? on the command prompt.

  • Very cool pl! Thanks for sharing.

Viewing 15 posts - 1 through 15 (of 39 total)

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