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