SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using VBScript to Automate Tasks


Using VBScript to Automate Tasks

Author
Message
Haidong Ji
Haidong Ji
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp



Calvin Lawson
Calvin Lawson
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 102
nice, consice code. Great way to delete old files; didn't realize it was that easy, you did my homework assignment for me. Smile

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

Signature is NULL
Haidong Ji
Haidong Ji
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 60
Thanks Calvin for your compliment. I appreciate it. I am glad you find the article helpful.



dougsx1
dougsx1
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 5
Very helpful, thanks for sharing



Archie Ogden
Archie Ogden
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 7

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



Archie Ogden
Archie Ogden
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 7

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

Thanks for the code.


antonio bonilla
antonio bonilla
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1

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


sql8081
sql8081
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 4

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


Narizz28
Narizz28
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 73

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!


Narizz28
Narizz28
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 73

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!


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search