Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using VBScript to Automate Tasks


Using VBScript to Automate Tasks

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18070
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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 Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18070
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 Wink


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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Truckin
Truckin
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 127
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.)
Haidong Ji
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
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!



ImyaHuckleberry
ImyaHuckleberry
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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!!
Leifton
Leifton
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 78
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
m_rios75
m_rios75
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
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