vb script to copy and delete file system items

Took a few minutes this morning to help a buddy of mine script the archiving of files accross his network. There are tons of variations of this but here is what I came up with.

strFolder = "\\unc\path" 'or local path
dtstart = cdate(FormatDateTime(DateAdd("m",-1,Now),2) + " " + FormatDateTime(DateAdd("m",-1,Now),3))
copydir = "\\unc\path" 'or local path

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)

For Each objFile in objFolder.Files
dtmFileDate = objFile.DateLastModified

If dtmFileDate > dtStart Then
'Wscript.Echo objFile.Name
'objFile.copy(copydir + "\" + objFile.name)
'objFile.Delete True
End If
Next

wscript.echo "Done"

Let's quickly break this done line at a time.

Line 1
strFolder = "\\unc\path" 'or local path

Declaring a local variable to hold the name of the folder we want to copy from.

Line 2
dtstart = cdate(FormatDateTime(DateAdd("m",-1,Now),2) + " " + FormatDateTime(DateAdd("m",-1,Now),3))

Declaring and setting a value for a date that we will use to compare to the dates of files. Lokk at the dateadd "m" -1. this is adding a minus 1 to the current date. The second portion of this is for the time portion of the date. You can change the interval by increasing the -1 to however many months back you want to go.

Line 3
copydir = "\\unc\path" 'or local path

Declaring a local variable to hold the name of the folder we want to copy to.

Line 5
Set objFSO = CreateObject("Scripting.FileSystemObject")

Initializing a new variable for object creation.

Line 6
Set objFolder = objFSO.GetFolder(strFolder)

setting the folder attribute of the File System Object.

Line 8
For Each objFile in objFolder.Files

Start our "For" loop.

Line 9
dtmFileDate = objFile.DateLastModified

Declare and set a variable each time in this loop that will coorespond to the last modified date of the file now being processed.

Line 11
If dtmFileDate > dtStart Then

Begin our comparison. If the last modified date is newer than the start date (Set in Line 2) then go to the next line of code in this loop.

Line 12
'Wscript.Echo objFile.Name

This line has been commented out. It will not run because of the single tick ' character. Uncomment this line by removing the ' and it will print thwe current file name to the screen. This is helpful in debugging.

Line 13
objFile.copy(copydir + "\" + objFile.name)

Do the work. Copy the file in the loop to the destination.

Line 14
objFile.Delete True

Delete the file from the original location.

Line 15
End If

This is the end of the if statement block.

Line 16
Next

Loop to the next record.

Line 18
wscript.echo "Done"

This will send a message to the display stating the the job is completed. This can be commented but is also helpful in debugging.

This script can be referenced and schedule in a windows acheduled task or a SQL Server agent job and run on a schedule in an automated fashion to help alleviate the burdon of administration. There are also many error catching and handling features and steps that can be added to this script to help when the process runs into hiccups.

Error handling and logging is a seperate arena and will be tackled on the next post. Until the Happy Coding and Good Luck!

TSQL Having clause

In the spirit of all of the aggregates for reporting I have been doingh lately the next item in the TSQL language that it made sense to discuss is the "having" clause. I had used previously but it was for a different purpose. having is a good way of using the count() function to show duplicates in a dataset. Here is an example:

-- table var
declare @duplicate table
(
col1 int,
col2 varchar(10)
)

-- inserts
insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(1,'one')

insert into @duplicate
(col1,col2)
values
(2,'two')

insert into @duplicate
(col1,col2)
values
(2,'two')

insert into @duplicate
(col1,col2)
values
(3,'three')


select col2
from @duplicate
group by col2
having COUNT(col2) > 1 -- could be count(*)

go

You should have gotten 2 rows back one for the word "one" and one for the word "two". If you change the having clause in the query to be > 2 then you'll only get one row back. This wil be the word "one" for (hopefully) obviously reasons based on the inserts.

However, the having clause can be put to a much more useful purpose when producing a query that contains aggregates. let us say that you were asked to generate a report that returned the sums of a column? No big deal right?

Select sum(column), group_by_column
from table
group by group_by_column

What if you were asked to allow an input variable to the query that allowed you to filter on the sum of the "sum(column)"?

Enter the having clause. Think of it as a where clause for aggregates. Here is a simplified example:

declare @t table
(
col1 int,
col2 int
)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(1,1)

insert into @t
(col1,col2)
values
(2,1)

insert into @t
(col1,col2)
values
(2,1)

-- throws aggregate error
select col1,SUM(col2)
from @t
where SUM(col2) > 2
group by col1


This code used an aggregate in the where clause and should have cause the following error:

Msg 147, Level 15, State 1, Line 36
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Now use the having clause instead

-- having!
select col1,SUM(col2)
from @t
where 1 = 1 -- used just to show where the "where" clause would go if needed.
group by col1
having SUM(col2) > 2

Coming full circle you could declare a variable and apply this variable to your having clause such as having sum(col2) > @variable.

Put the having clause to work for you when needing to identify duplicates or filter on aggregates.

Happy coding!