Archive for January, 2012

I was reading Joe Keohan’s Blog about copying files via PowerShell and that got me to thinking about a process I already had.  Each night we copy files from a shared folder and post them to our intranet site for internal use.  We currently are using a batch file to handle this but I thought it was time to rewrite it in PowerShell. 

I had two main processes that I needed to handle.  The first was to copy the files to the local folder for the intranet and the second was to send an email to someone with those files attached so he can open them on his mobile device. 

So the first step of my script was to define some variables to stand in for my file locations. 

$SourceDir = "\\SVR1\data\Reports\Trading\*.pdf"
$DestDir = "c:\inetpub\intranet\research\reports\"
$BlotterSource = "\\SVR1\data\Reports\Blotters\*.pdf"

Then I use the Test-Path cmdlet to determine if the reports exist at the source location.  Normally this cmdlet would return True if the files exist and False if they do not.  But I use it in an If statement to say if it evaluates to True then do something else.  The something else is to start the file copy. 


If (Test-path $SourceDir )
    Copy-Item $SourceDir -Destination $DestDir -Force

If (Test-path $BlotterSource)
    Copy-Item $BlotterSource -Destination $DestDir -Force

I do this twice, once for one location and then again for the second location. 

And once my script is finished copying the files I use the send-mailmessage cmdlet to email the files to the recipient.  Since I have multiple files I’d like to attach I use a Get-ChildItem cmdlet to get all the file names and pipe them into the send-mailmessage cmd. 

This is what the finished script looks like. 

$SourceDir = "\\SVR1\data\Reports\Trading\*.pdf"

$DestDir = "c:\inetpub\intranet\research\reports\"

$BlotterSource = "\\SVR1\data\Reports\Blotters\*.pdf"

If (Test-path $SourceDir )

{ Copy-Item $SourceDir -Destination $DestDir -Force }

If (Test-path $BlotterSource)

{ Copy-Item $BlotterSource -Destination $DestDir -Force }

Get-ChildItem $DestDir | Where {-NOT $_.PSIsContainer} | foreach {$_.fullname} | send-mailmessage -from "Reporting <reporting@anyone.com>" -to "Jonathan <jonathan@anyone.com>", "Rob <rob@anyone.com>" -Cc "Greg <greg@anyone.com"> -subject "Daily Research Reports" -smtpServer mailserver.anyone.com -Body "Here are the daily research files for your review"

Now that the script is done I just schedule it via Windows Task Scheduler and we’re all set.  Now the files will get copied and emailed quickly and efficiently.


Read Full Post »

After finishing my script from the previous post I thought there has to be a better way to write this and reduce the lines of code.  Not that 5 separate lines is a lot.  But, if the list of items to print grew larger say to 15 documents, and was in different subdirectories then it might get complicated.  So, I set about trying to find a way to use the ForEach-Object cmd.  After some trial and error I have come up with this. 

$Directory = "\\SVR1\DATA\Reports\Trading\"

Get-ChildItem -path $Directory -recurse -include *.pdf | ForEach-Object {Start-Process -FilePath $_.fullname -Verb Print -PassThru | %{sleep 10;$_} | kill }


This will loop through the “Trading” directory and all subdirectories and print out the pdf documents it finds.  The one thing I still want to refine is the ability to close out Acrobat.  Right now it closes Acrobat after printing each document only to reopen it again for the next document.  I will look into just closing it at the end one time.  But for now this is at least a decent working script I plan to get a lot of use out of. 

Read Full Post »

My office has a series of nightly reports from SQL Server that export as Adobe documents.  The users want them printed and waiting for them each morning on a specific printer.  We had a little utility that could do this (and more) but the problem with it was that the computer running the utility had to be logged in and the utility had to be launched.  Occasionally we would run into issues where the computer was rebooted and as a result the reports would not be printed out in the morning.

So, that got me to thinking.  This would be a great way to use PowerShell!  So here is what I did.

First I started experimenting with the Out-Printer cmdlet to see if I could use that.  However since a pdf document is not plain text I couldn’t use this command.

get-content c:\docstoprint\doc1.pdf | Out-Printer

All that would get me is several pages worth of useless garbage.

Then I found another command I could use.

Start-Process –FilePath “c:\docstoprint\doc1.pdf” –Verb Print

This worked exactly like I had hoped.  I was able to print the pdf document to my default printer.  With one side effect, Adobe was left open after the print job.  So I tried this option.

Start-Process –FilePath “c:\docstoprint\doc1.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill

The extra options told powershell to wait 10 seconds and then kill the process.  This worked perfectly.  So now all I needed to do was create a script to print out the list of documents and then set it up in Task Scheduler.

So here is my finished script.
Start-Process -FilePath “c:\docstoprint\doc1.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill

Start-Process -FilePath “c:\docstoprint\doc2.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill

Start-Process -FilePath “c:\docstoprint\doc3.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill

Start-Process -FilePath “c:\docstoprint\doc4.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill

Start-Process -FilePath “c:\docstoprint\doc5.pdf” –Verb Print -PassThru | %{sleep 10;$_} | kill
The last step is to actually schedule this in Windows Task Scheduler.

So, first launch Task Scheduler, right click on the Task Scheduler Library and select “Create Basic Task”



Give your task a meaningful name, I chose “Daily Automated Report Printout”.  And put a little description of the task so someone else will understand the purpose.  Then click Next.

The next step is to decide when you want the task to start.  In my case I need it every weekday at 6am.  So, I chose “Weekly” and then clicked next to select the time and days.   After selecting the days and time click Next.

Now you need to tell Task Scheduler what action you want to perform.  In this case you want to “Start a Program”


Click Next and then you need to tell Task Scheduler what program to start.



The program is powershell.exe and it should be located in C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Then in the “Add arguments” section you need to put in the path to the powershell script.  In my case it was c:\users\greg\documents\powershell\DocsToPrint.ps1

Click Next to finish the wizard.  You will be presented with a Summary of your new task, make sure everything is correct, be sure to click the “Open the Properties dialog” checkbox and then click Finish.   

In the Properties for the task you have one last step to configure. 

In the Security Options section on the General Tab, you need to change the option to “Run whether user is logged on or not”


Then when you click OK you will be asked to put in account credentials for running this task.  That way when the job is started it will have privileges to run the job and print out to the default printer.

Read Full Post »

This is a script I wrote to use when I need to get the quarter begin and end dates for SQL.

declare @date datetime

declare @begdate datetime

declare @enddate datetime

set @date = getdate()

--get first day of CURRENT qtr 

set @begdate = dateadd(q, datediff(q, 0, @date), 0)

--get last day of CURRENT qtr 

set @enddate = dateadd(q, datediff(q, 0, dateadd(q, 1, @date)), -1)

select @date as today, @begdate as QtrBegin, @enddate as QtrEnd

Read Full Post »

Once you have kicked off your report subscription you might want to see the status. Again, if you don’t want to log onto the SQL Server Reporting Services web interface you can do this with a simple SQL Query.

/*Active (Running) subscriptions*/



rs.scheduleid AS jobid,



FROM reportserver..catalog c

INNER JOIN reportserver..subscriptions sub
ON (c.itemid = sub.report_oid)

INNER JOIN reportserver..activesubscriptions asub
ON (sub.subscriptionid = asub.subscriptionid)

INNER JOIN reportserver..reportschedule rs
ON (sub.subscriptionid = rs.subscriptionid)

This query will return the Report Name, the JobID, description and the LastStatus.

Read Full Post »

The previous post showed a sql script that is useful for finding all your Reporting Services jobs. Now that you have that information, lets say you’d like to kick off a scheduled report that either already ran, or you’d like to run again but you don’t want to have to step through all the settings for that subscription in the Reporting Services web interface. Instead you can use the Job ID that you got in the previous result set and use it to start the report job. I use this script every month to manually kick off a scheduled report that generates client invoices.

/*  Report Server Rerun subscription  */

--Execute Monthly Web Statement Subscripion

EXEC msdb..Sp_start_job @job_name = ‘A251BE76-0453-4A8F-8C98-69439B881B23’

--Client Holdings

EXEC msdb..Sp_start_job @job_name = ’57AD46EF-A46F-41A7-83F3-9430B51CEDE8′

--Standard subscription

EXEC msdb..Sp_start_job @job_name = ‘DC1C9E50-D18E-4A69-9452-2274D21232D7’

Read Full Post »

This script is a useful one that I have at my office.  I use it often to find all the SQL Server Reporting Services scheduled reports that exist on a SQL Reporting Server instance.  It list the job name, job description, the scheduled date and time and the status of the last time it was run.

/*Information about the subscriptions on your Report Server*/

SELECT c.name,

       rs.scheduleid as JobID,






FROM   reportserver..catalog c WITH(nolock)

       INNER JOIN reportserver..subscriptions sub WITH(nolock)

         ON ( c.itemid = sub.report_oid )

       INNER JOIN reportserver..reportschedule rs WITH(nolock)

         ON ( c.itemid = rs.reportid

              AND sub.subscriptionid = rs.subscriptionid )

       INNER JOIN reportserver..schedule sch WITH(nolock)

         ON ( rs.scheduleid = sch.scheduleid )

       INNER JOIN msdb..sysjobs sj WITH(nolock)

         ON ( CAST(rs.scheduleid AS SYSNAME) = sj.name )

       --sysname equivalent to nvarchar(128)

       INNER JOIN msdb..sysjobschedules sjs WITH(nolock)

         ON ( sj.job_id = sjs.job_id )

       INNER JOIN msdb..sysschedules ss WITH(nolock)

         ON ( sjs.schedule_id = ss.schedule_id )

-- WHERE c.Name like ‘ISS %’
-- A good way to filter by report name.


Read Full Post »

Older Posts »