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 Monday, March 10, 2008 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
Heh.... Just like SQL allows itself to take any form... or Java... or...

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467139
Posted Tuesday, March 11, 2008 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
Jeff Moden (3/10/2008)
Heh.... Just like SQL allows itself to take any form... or Java... or...


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.



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #467362
Posted Tuesday, March 11, 2008 7:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
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 ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467415
Posted Tuesday, March 11, 2008 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
Jeff Moden (3/11/2008)
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 ;)


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


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #467457
Posted Tuesday, March 11, 2008 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
Now... THAT I absolutely agree with! And for the very same reasons!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467484
Posted Tuesday, March 11, 2008 2:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:02 AM
Points: 115, Visits: 113
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.)
Post #467773
Posted Tuesday, March 11, 2008 3:33 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
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!



Post #467792
Posted Thursday, April 10, 2008 3:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 1:11 PM
Points: 3, Visits: 20
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!!
Post #483330
Posted Tuesday, July 29, 2008 2:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 30, 2013 5:15 AM
Points: 35, Visits: 77
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 Explicit


Const strBackupExt = ".bak.bz2"
Const strDir = "C:\Powerclean\Backups"


Dim fso
Dim vFolder as Variant
Dim vFile as Variant


Set 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 If

Next vFile
Post #543060
Posted Saturday, July 10, 2010 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 12, 2010 9:05 AM
Points: 1, Visits: 3
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 Explicit
on 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 = Nothing

Any ideas?

Thanks,
Marvin
Post #950377
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse