Using VBScript to Automate Tasks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Haidong Ji » Using VBScript to Automate Tasks

40 posts, Page 4 of 4

«««1 2 3 4

Using VBScript to Automate Tasks

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Monday, March 10, 2008 9:53 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #467139

Matt Miller (#4)

Posted Tuesday, March 11, 2008 7:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

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

Jeff Moden

Posted Tuesday, March 11, 2008 7:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

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

Post #467415

Matt Miller (#4)

Posted Tuesday, March 11, 2008 8:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

Jeff Moden (3/11/2008)

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

Jeff Moden

Posted Tuesday, March 11, 2008 8:58 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

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

--Jeff Moden

Post #467484

Truckin

Posted Tuesday, March 11, 2008 2:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 7:12 AM
Points: 109, Visits: 105

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

Haidong Ji

Posted Tuesday, March 11, 2008 3:33 PM

Valued 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

ImyaHuckleberry

Posted Thursday, April 10, 2008 3:28 PM

Forum 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

lrhyrne

Posted Tuesday, July 29, 2008 2:48 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 18, Visits: 41

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

m_rios75

Posted Saturday, July 10, 2010 2:13 PM

Forum 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 »

40 posts, Page 4 of 4

«««1 2 3 4

Permissions