SQLServerCentral / Discuss Content Posted by Haidong Ji / Article Discussions / Article Discussions by Author / Using VBScript to Automate Tasks / Latest Posts

RE: Using VBScript to Automate Tasks

m_rios75 — Sat, 10 Jul 2010 14:13:24 GMT

Great code.. so great and I am trying to use it! How would I run this against multiple locations (servers) without having to create a completely different job? Right now my "totally stolen" code looks like this:Hello,Sorry to bother you.. but I am trying to use some code you posted long ago in 2008. It worked great but I am trying to remove files from 2 different locations (servers). I was hoping you can help me..Right now my code looks like this..Option Expliciton error resume next Dim oFSO Dim sDirectoryPath Dim oFolder Dim oFileCollection Dim oFile Dim iDaysOld'Customize values to clear Reports'Question is here.. how do I loop around to grab a second location? iDaysOld = 90 Set oFSO = CreateObject("Scripting.FileSystemObject") sDirectoryPath = "\\LIBERTY\Tailsheets" set oFolder = oFSO.GetFolder(sDirectoryPath) set oFileCollection = oFolder.Files'Walk through each file in this folder collection. 'If it is older than 3 months (90) days, then delete it. For each oFile in oFileCollection If oFile.DateLastModified < (Date() - iDaysOld) Then oFile.Delete(True) End If Next'Clean up Set oFSO = Nothing Set oFolder = Nothing Set oFileCollection = Nothing Set oFile = NothingAny ideas?Thanks,Marvin

RE: Using VBScript to Automate Tasks

lrhyrne — Tue, 29 Jul 2008 14:48:49 GMT

It makes more sense to me to use a DATEDIFF vb function instead of assuming it will add days to the returned date. This also allows you to be explicit about which date part you want; days, minutes, weeks and so on... For you Unix / Linux nerds, you may notice I am using bzip2 (a win32 port) which compresses the files much better (and free) than any PKZip variant.I did a little script similar to yours (I was amazed about how similar it was) about six months ago, here's what is looks like:Option ExplicitConst strBackupExt = ".bak.bz2"Const strDir = "C:\Powerclean\Backups"Dim fsoDim vFolder as VariantDim vFile as VariantSet fso = CreateObject("Scripting.FileSystemObject")Set vFolder = fso.GetFolder(strDir)For each vFile in vFolder.Files If Right(vFile.Name, 8) = strBackupExt Then If DATEDIFF(d, vFile.DateLastModified, Now) > 30 Then vFile.Delete(True) End If End IfNext vFile

RE: Using VBScript to Automate Tasks

ImyaHuckleberry — Thu, 10 Apr 2008 15:28:04 GMT

Could someone send me the link please(or post it again)...this sounds exactly what I have been looking for (and banging my head against the wall trying to figure out others) and from all the comments I'm reading I am anxiously anticipating being able to simply add this to a SQL job step!!MANY thanks!!

RE: Using VBScript to Automate Tasks

Haidong Ji — Tue, 11 Mar 2008 15:33:45 GMT

Good stuff Truckin! Thanks for sharing.For the record, I am not religious on any particular technology or scripting languages. Whatever suits the needs and get the job done, and easy to read and maintain, more power to you, I am all for it. Be it JavaScript, VBScript, WMI, PowerShell, Perl, whatnot. I think they all have their places. I am totally digging Perl at the moment.I originally wrote this article maybe 5 years ago. It is actually surprising, and rewarding at the same time, that so many people find it useful.Happy scripting everybody!

RE: Using VBScript to Automate Tasks

Truckin — Tue, 11 Mar 2008 14:57:50 GMT

I don't wish to denigrate vbscript in any way, I probably should use it more, the results are certainly easier to read than some of the stuff I do. For the record, I had to schedule a job to delete old files and a quick look up in the operating system help and I did this:Forfiles -p D:\backup /m *.* /d -21 /c "cmd /C del @Path"I titled my scheduled job RemoveOldBackups so I still have some reasonable idea of what this line does. (I just used 21 days to match the example provided.)

RE: Using VBScript to Automate Tasks

Jeff Moden — Tue, 11 Mar 2008 08:58:40 GMT

Now... THAT I absolutely agree with! And for the very same reasons!

RE: Using VBScript to Automate Tasks

Matt Miller (#4) — Tue, 11 Mar 2008 08:36:33 GMT

[quote][b]Jeff Moden (3/11/2008)[/b][hr]I say again... Just like SQL allows itself to take any form... or Java... or... I've seen some absolutely gorgeous VBS both formatting wise and code wise. I've also seen my fair of VBS where the "S" stands for a 4 letter word ;)[/quote]Oh, I've seen some "pretty" VBScript, too (manually-formatted and all). But that doesn't improve its structure. If you want to compare it to SQL - it's like starting from a SQL data model where every column is a varchar(max)...I suppose I've seen it tortured more than is natural. I see it a bit like you do with CLR in SQL - should be restricted to very specific items (read - very FEW items).

RE: Using VBScript to Automate Tasks

Jeff Moden — Tue, 11 Mar 2008 07:57:40 GMT

I say again... Just like SQL allows itself to take any form... or Java... or... I've seen some absolutely gorgeous VBS both formatting wise and code wise. I've also seen my fair of VBS where the "S" stands for a 4 letter word ;)

RE: Using VBScript to Automate Tasks

Matt Miller (#4) — Tue, 11 Mar 2008 07:18:58 GMT

[quote][b]Jeff Moden (3/10/2008)[/b][hr]Heh.... Just like SQL allows itself to take any form... or Java... or...[/quote]Touche. I worded that poorly. It's not so much that you can write poor code in VBScript (you can do that anywhere as you pointed out) - it's just that it's almost impossible to build something WELL in it.My perception is that that VBScript doesn't really allow you to build stuff the right way, because it's starts from the untyped, don't really need to declare or set anything. It's Humpty Dumpty: it starts out with no structure, and you can't seem to force any decent amount of structure into it.

RE: Using VBScript to Automate Tasks

Jeff Moden — Mon, 10 Mar 2008 21:53:46 GMT

Heh.... Just like SQL allows itself to take any form... or Java... or...

RE: Using VBScript to Automate Tasks

Matt Miller (#4) — Mon, 10 Mar 2008 19:30:06 GMT

Nice article...but - I can't help but ask - why use VBScript when you can use an actual full-featured language? You know - something with actual types, error-handling, etc...?I mean - in the old days where putting together a full fledged VB6 was a PITA, okay, I could see it. But simple console apps are just as simple to put together, and are oh so very much cleaner to put together....All right fine - I like VBScript too....But it's almost invariably garbage code because it allows itself to have no form whatsoever....

RE: Using VBScript to Automate Tasks

Jeff Moden — Mon, 10 Mar 2008 18:59:53 GMT

Thanks Timothy... That's why I normally don't use VBS scripts... I can do almost everything with T-SQL. :)

RE: Using VBScript to Automate Tasks

timothyawiseman — Mon, 10 Mar 2008 18:00:41 GMT

Thanks Jeff. None of those are issues in my situations, but they make perfect sense for reasons someone might need to use this.

RE: Using VBScript to Automate Tasks

sqlhack — Mon, 10 Mar 2008 16:04:26 GMT

Haidong,Thanks for posting this article. I've never done any scripting or programming other than SQL. I had a task in my assigned "To Do" list at work that required automating some backup and log deletes. I've been putting it off because I didn't know how to do it other than write some SQL with xpcmdshell and I still wasn't sure exactly how to accomplish it. This article popped up in my email and now it's done! Thank you for knocking something of my tasklist and helping me get over the fear of vbscript!

RE: Using VBScript to Automate Tasks

Haidong Ji — Mon, 10 Mar 2008 11:24:52 GMT

It does the same thing, as far as I can tell.

RE: Using VBScript to Automate Tasks

Andrew Collins — Mon, 10 Mar 2008 11:10:32 GMT

How does the script for deleting old files differ from the Deleting old files in the Maintenance plan?thanks

RE: Using VBScript to Automate Tasks

Jeff Moden — Sun, 09 Mar 2008 08:44:22 GMT

Heh... I can explain that... since it's a "script", it may not be subject to the normal "code release" steps within a company, it's callable from DTS or SSIS, it's available on every Windows machine, and it's free...

RE: Using VBScript to Automate Tasks

timothyawiseman — Sun, 09 Mar 2008 01:43:05 GMT

Haidong Ji, This is an excellent article and I appreciate the information. I do have one question though, I have worked with a variety of technologies, but I have not yet used VBS. Why would you want to implement it this way instead of making an actual application or writing it in sql with use of xp command shell?

RE: Using VBScript to Automate Tasks

Haidong Ji — Fri, 07 Mar 2008 21:26:37 GMT

Great point!Thanks a lot Jeff for the compliment :)

RE: Using VBScript to Automate Tasks

Jeff Moden — Fri, 07 Mar 2008 21:00:17 GMT

Outstanding primer on VBS and the FSO... well done.I don't know if I missed it in the article, but for anyone that hasn't used it before, you should have said something to the effect that you can write the code using any text editor, mention that the files can be saved like any text file, and which extensions the operating system expects for VBS files (.vbs or .scr??? Not sure on .scr because I always use .VBS). It would also be handy to empasize a bit that the scripts can be called from any .bat file.Again, outstanding job and good documented examples.

RE: Using VBScript to Automate Tasks

SanjayAttray — Fri, 07 Mar 2008 08:57:20 GMT

Delete files in a folder that are certain days old script is worth keeping in personal briefcase. Thanks for a good article.

RE: Using VBScript to Automate Tasks

Haidong Ji — Fri, 07 Mar 2008 08:19:17 GMT

You can put it in a Sql Server Agent job. Create a new job, go to steps, and pick ActiveX script from the dropdown.

RE: Using VBScript to Automate Tasks

Jerry Hung — Fri, 07 Mar 2008 08:17:30 GMT

Pretty cool neat VBSI am playing with them alreadyI suppose SSIS/Batch can work equally well for the same thing

RE: Using VBScript to Automate Tasks

Charlie Whitcraft-242308 — Fri, 07 Mar 2008 08:12:13 GMT

I need this funcionality, but I have not done VBScript with SQL Server before.Where do you put this code? Do I bring up SQL Mgmt Studio and then what?Can you point me to a resource?Thanks. CW

RE: Using VBScript to Automate Tasks

Warren Ball — Fri, 22 Sep 2006 05:23:00 GMT

The following questions are for script#2.

1) When the FTP script is run, what does the '-s' represent, send a single file?

2) Is there a way to make the DOS screen freeze when the FTP script is being run? I've tried 'pause', 'prompt', 'echo', etc. but nothing seems to work.

3) How can a logfile be created when the FTP script is run? I've used the Chr to put the ascii '>>' characters in but a logfile is not created.

Thanx! Warren

RE: Using VBScript to Automate Tasks

Haidong Ji — Mon, 10 Apr 2006 19:53:00 GMT

Very cool pl! Thanks for sharing.

RE: Using VBScript to Automate Tasks

pl-272086 — Mon, 10 Apr 2006 06:06:00 GMT

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 folderoWshShell.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.

RE: Using VBScript to Automate Tasks

Haidong Ji — Sat, 15 Oct 2005 21:37:00 GMT

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.

RE: Using VBScript to Automate Tasks

Ben Dizenhouse — Mon, 11 Jul 2005 08:53:00 GMT

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

RE: Using VBScript to Automate Tasks

Michael Shorkey — Thu, 02 Jun 2005 15:02:00 GMT

Is this in a .BAT or .SCR file?

RE: Using VBScript to Automate Tasks

Narizz28 — Sat, 09 Apr 2005 11:00:00 GMT

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!

RE: Using VBScript to Automate Tasks

Narizz28 — Sat, 09 Apr 2005 10:48:00 GMT

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!

RE: Using VBScript to Automate Tasks

sql8081 — Fri, 08 Apr 2005 22:40:00 GMT

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 SourceSafesTargetPath = "C:\SourceSafe\GetLatest" 'File System pathsUserName = "steve" 'for SourceSafesPassword = "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 = sPathEnd Function

RE: Using VBScript to Automate Tasks

antonio bonilla — Fri, 08 Apr 2005 10:34:00 GMT

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

RE: Using VBScript to Automate Tasks

Archie Ogden — Fri, 08 Apr 2005 03:25:00 GMT

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

Thanks for the code.

RE: Using VBScript to Automate Tasks

Archie Ogden — Fri, 08 Apr 2005 03:22:00 GMT

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 passwordcd c:\xfrrem IF EXISTS THEN DELETE THE PREVIOUS FILESdir c:\xfr\%1.%2 >nul && del c:\xfr\%1.%2dir c:\ftpcmd\%1.ftp >nul && del c:\ftpcmd\%1.ftpecho open %3 >c:\ftpcmd\%1.ftpecho %4>>c:\ftpcmd\%1.ftpecho %5>>c:\ftpcmd\%1.ftpecho get %1.%2 >>c:\ftpcmd\%1.ftpecho bye>>c:\ftpcmd\%1.ftpftp -s:c:\ftpcmd\%1.ftp >c:\scripts_logs\%1FTP.logcd c:\scriptsfindstr /c:"226 Transfer complete" C:\scripts_logs\%1FTP.logif %ERRORLEVEL% GTR 0 goto FAIL exit 0:FAILrem - send a mail message or whatever you wantexit 1

RE: Using VBScript to Automate Tasks

dougsx1 — Thu, 07 Apr 2005 22:47:00 GMT

Very helpful, thanks for sharing

RE: Using VBScript to Automate Tasks

Haidong Ji — Wed, 05 Nov 2003 19:29:00 GMT

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

RE: Using VBScript to Automate Tasks

Calvin Lawson — Wed, 05 Nov 2003 00:34:00 GMT

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.

Using VBScript to Automate Tasks

Haidong Ji — Fri, 31 Oct 2003 00:00:00 GMT

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