Powershell won’t save when running as a scheduled job

Or, How SQLBits put me in touch with Laerte and solved a problem

I have a scheduled Powershell job which I use to create an Excel file colour coded for backup checks. (I will blog about it another time) It works brilliantly on my desktop and saves the file to a UNC path and emails the team the location. It works brilliantly when run in Powershell on the server. When I schedule it to run though it doesn’t do so well. The job completes without errors but no file is saved.

If you examine the processes running at the time you can see the excel process is running  so I knew it was doing something but couldn’t work out why it was failing.

It was one of those jobs that gets put to the bottom of the list because the service worked ok I just needed to have it running on the server rather than a desktop for resilience, recovery and security purposes. Every now and then I would try and work out what was going on but new work and new problems would always arrive and it has been like that for 6 or maybe even 9 months.

As you know I attended SQLBits this weekend and I went into a session with Laerte Junior. Laerte is a SQL Server MVP and can be found at simple-talk as well as his own blog http://shellyourexperience.com/ or on twitter @LaerteSQLDBA Oh and He loves Star Wars 🙂

Laerte_Junior[1]After a fascinating session I asked him if I could show him my problem. He very graciously said yes and after looking at the code and listening to me explain the problem he suggested this very simple solution which he said had taken him a great deal of searching to find. It’s a bug with COM objects and requires the creation of folders as shown below. I cam into work today, tried it and it worked. HOORAY another thing off my list and big thanks to Laerte

#Region Bug_Jobs_ComObjects
#(32Bit, always)
# Create Folder
#New-Item –name C:\Windows\System32\config\systemprofile\Desktop  –itemtype directory
# Create folder
#New-Item –name C:\Windows\SysWOW64\config\systemprofile\Desktop  –itemtype directory
#EndRegion Bug_Jobs_ComObjects


This worked for me however I had already implemented another fix for a possible gotcha so I will tell you of that one too

Sometimes Powershell cannot save to UNC paths because of  IE enhanced security.

Either log in as user and add server to intranet site zones or disable the warning in registry as follows

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap] "UNCAsIntranet"=dword:00000000


Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

12 Things I learnt at SQLBits XI

  • The Helpers are awesome


  • Often Sessions fill up very quickly – Get there early


  • You can learn as much outside of the sessions as you can in them

  • There are amazing prizes

A LEGO R2D2 !!!

  • Bring your sense of humour

  • The SQL community contains the most gracious and generous, willing to help people

  • You can connect with your user group and get a mini SQL Bits every month

Find your User Group Here http://sqlsouthwest.co.uk/national_ug.htm

  • If there is no user group in your area people will help you to start one

RT @fatherjack Interesting chat about a potential new user group in the uk. Anyone around Newcastle area looking for some free training?

  • Every session is videoed and will be available online. For free.

  • You will learn and have fun

Too awesome for words! “@justjonlevett: Lego Server! @fusionio #sqlbitspic.twitter.com/bhxPaTIq4K

More blogs about SQL Bits XI and Photos

SQL Bits Facebook https://www.facebook.com/SQLBits

Roger Van Unen Gallery https://plus.google.com/photos/109984741094039234638/albums/5874913179986208577

JR’s Gallery https://skydrive.live.com/?cid=7b73b60f4c7d77c9&id=7B73B60F4C7D77C9%212222

Steve Jones Blog http://voiceofthedba.wordpress.com/2013/05/06/fun-at-sql-bits/

Chris Webbs Blog http://cwebbbi.wordpress.com/2013/05/05/sqlbits-xi-summary/

Find more from the Facebook Page or #sqlbits

Finally a BIG Thank you to all these people http://sqlbits.com/about/WhosWho.aspx

and the fantastic helpers without whom SQL Bits would never happen

Till Next year