Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Using VBScript to Automate Tasks Expand / Collapse
Author
Message
Posted Friday, October 31, 2003 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp


Post #17809
Posted Wednesday, November 5, 2003 12:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, 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. :)

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



Signature is NULL
Post #85567
Posted Wednesday, November 5, 2003 7:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Thanks Calvin for your compliment. I appreciate it. I am glad you find the article helpful.




Post #85568
Posted Thursday, April 7, 2005 10:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 24, 2008 9:03 AM
Points: 4, Visits: 5
Very helpful, thanks for sharing


Post #173101
Posted Friday, April 8, 2005 3:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 9, 2013 1:40 PM
Points: 245, Visits: 6

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


 

Post #173163
Posted Friday, April 8, 2005 3:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 9, 2013 1:40 PM
Points: 245, Visits: 6

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

Thanks for the code.

Post #173166
Posted Friday, April 8, 2005 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 21, 2006 5:34 PM
Points: 1, 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

Post #173394
Posted Friday, April 8, 2005 10:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 9:00 AM
Points: 65, 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

 

Post #173539
Posted Saturday, April 9, 2005 10:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 17, 2011 8:25 AM
Points: 286, 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!

Post #173549
Posted Saturday, April 9, 2005 11:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 17, 2011 8:25 AM
Points: 286, 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!

Post #173553
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse