Hey, Scripting Guy! Quick-Hits Friday: The Scripting Guys Respond to a Bunch of Questions (12/18/09)

Hey, Scripting Guy! Quick-Hits Friday: The Scripting Guys Respond to a Bunch of Questions (12/18/09)

  • Comments 1
  • Likes
Bookmark and Share

In this post:


Help Me Make This Code Work!

Hey, Scripting Guy! Question

Hey, Scripting Guy! When I follow your examples on accessing SQL Server objects using Windows PowerShell, I do not seem to get the same results as you. An example is the Why Should I Use Windows PowerShell with SQL Server 2008 post that talks about using Windows PowerShell to access SQL Server, which much like your scripts is something I enjoy reading and learning from.

The article begins with this rather cryptic code to load the SQL management namespace.

$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "<<my server name>>"
$dbs = $sqlServer.databases

It then states the following:

From the list of properties from the Database object, let's select the most common ones—Name, CompatibilityLevel, AutoShrink, RecoveryModel, Size, and SpaceAvailable.

And it continues with the code shown here.

$dbs | SELECT Name, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

When I run the code, I am able to retrieve values for Name, CompatibilityLevel, Size, and SpaceAvailable. However, nothing is returned for the AutoShrink and RecoveryModel attributes.

I decided to do a little troubleshooting by using the Get-Members cmdlet on the databases object collection, but I cannot see it in the list. This is probably why I am receiving the empty result values from my previous query.

Do you have idea what I might be doing wrong? The SMO library seems to be loaded because I can get the other properties that are listed in the databases collection (Name, Size, CompatibilityLevel), and I am able to see the methods and properties from the SQL Server object.

-- JH


Hey, Scripting Guy! AnswerHello JH,

Microsoft Scripting Guy Ed Wilson here. I asked my good friend Buck Woody, who wrote the original article, and here is his response.

One of the cool things about being the Microsoft Scripting Guy is being able to work with many extremely smart people. Sometimes just watching the way things transpire is nifty. I decided to let you peek at the exchange that transpired during the day. Because I was on the Cc line, I had a front row seat.

Buck: There could be a problem with his SMO library. Can you please send the results from running the line of code seen here?




Image of results of running the line of code


Buck: It could be the version or edition of SQL Server that you are running. Can you tell me which version and edition you are running?



Image of version and edition of SQL Server JH is running

The version and edition should be the same for each server that I am trying to query. 

Buck: I used Windows PowerShell 1.0 when I wrote those Hey, Scripting Guy! posts. If you are running Windows PowerShell 2.0, there might be some kind of strange compatibility issue there.



Image of version of Windows PowerShell JH is running


Buck: Hmmm, are you running on a local system or a remote one? 

JH: I am running Windows PowerShell from my local machine. However, I am referencing servers remotely on the domain. 

Buck: OK, I have set up everything on my test network the same as you have described. I have to think it is a permissions issue at this point. It is also possible that the SMO library on your system is corrupt. If you are obtaining the same results against a local Instance, you will need to re-install SMO.


How Can I Copy a Folder from One Computer to Many Other Computers? 

Hey, Scripting Guy! Question

Hey, Scripting Guy! In Windows PowerShell, I need to copy a folder from my computer and put it on 30 to 40 computers. I tried the script seen here, but the script was not written for Windows PowerShell and therefore did not work. Do you have any suggestions, or a Windows PowerShell script that might get me on the right path?


Const ForReading = 1
Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")

Do Until objFile.AtEndOfStream
    strComputer = objFile.ReadLine
    strRemoteFile = "\\" & strComputer & "\C$\Scripts\Test.doc"
    objFSO.CopyFile "C:\Scripts\Test.doc", strRemoteFile, OverwriteExisting

-- RD


Hey, Scripting Guy! AnswerHello RD,

The following script should help you.


# CopyFileToRemoteComputersFromText.ps1
# ed wilson, msft 6/19/2009

$path = "C:\bp\testDoc.txt"
$computerListPath = "C:\fso\computers.txt"
$computerList = Get-Content -path $computerListPath

Foreach ($destination in $computerList)
  Copy-Item -path $path -destination "


Addressing Some Unexpected VBScript While...Wend Behavior 

Hey, Scripting Guy! Question

Hey, Scripting Guy! I’m working on an exercise that uses the VBScript command While…Wend, and I came across an interesting occurrence. The original script, seen here, works just fine:

a = 5

While a < 10

    Wscript.Echo a

    a = a + 1


I decided to change the script, and it still works fine:

a = 3
while a < 5
wscript.echo a
a = a+.1

I made a further modification to the script and increased the value of the While as illustrated here:

a = 3
while a < 6
wscript.echo a
a = a+.1

As you can see in the following image, the results are not what one would expect:

Image of unexpected results of running script


Any thoughts about what could cause this?

-- BE


Hey, Scripting Guy! AnswerHello BE,

According to Eric Lippert, one of the original developers of VBScript, this behavior is actually expected. Here is his reply:

“Floating-point arithmetic is not exact. Remember, floats are binary, and there is no binary representation for 1/10 any more than there is a decimal representation for 1/3.”

That means that every time you add 1/10, you are actually adding the quantity nearest to 1/10 that can be represented in 52-bit decimal. That quantity is slightly smaller than 1/10; keep doing it and eventually you will accumulate enough error that it becomes noticeable.


Can I Display My Hard Drive's Temperature?

Hey, Scripting Guy! Question

Hey, Scripting Guy! I am trying to use Windows PowerShell to get to the hard drives' s.m.a.r.t technology; in particular the hard drive temperature. Will I have to use C++ or Ruby instead, or are there WMI methods that may be of some use?

On another note I believe that Windows PowerShell 2.0 will allow me to duplicate the DOS start command; where can I initiate several processes at one time? Here is an example:

Start delprof
\\mysyem csaa
Start delprof /p /c:\\server1
Start delprof /p /c:\\server1
Echo done

-- GW


Hey, Scripting Guy! AnswerHello GW,

There is nothing exposed by WMI to allow you to read the hard drive’s temperature. It is possible that your drive vendor may have created a WMI MOF file that will expose this information.

I am not sure exactly what you are referring to when you talk about starting several processes at once, but Windows PowerShell 2.0 does have the concept of jobs that allows you to run multiple commands in the background. You can also do this from a cmd prompt:

C:\Windows\system32>start powershell  -noexit -command "& {get-process}"

C:\Windows\system32>start powershell  -noexit -command "& {get-service}"

Or from within Windows PowerShell 2.0 you can do this:

start powershell -arg '-noexit -command "& {gps}"'

GW, you are going to love this. I have spent the last 45 minutes writing a script for you. Thank you for the idea! It is a pretty cool script, if I do say so myself.


# ----------------------------Begin Script ----------------------------------------------------
    Starts two new powershell consoles and displays service and process info
    This script contains a function Start-NewPowerShellConsole that will run a
    PowerShell command in a new PowerShell console.
   Start-NewPowerShellConsole.ps1 -command gps
   This command starts a new PowerShell console and displays process information
   Start-NewPowerShellConsole.ps1 -command gps,gsv
   This command starts a new PowerShell console and displays process information in
    one powershell console, and service information in a second powershell console.
    NAME: Start-NewPowerShellConsole.ps1
    AUTHOR: Ed Wilson
    LASTEDIT: 12/2/2009
    KEYWORDS: PowerShell Best Practices, Microsoft Press 2009
#Requires -Version 2.0
) #end param

Function Start-NewPowerShellConsole ($command)
  Foreach($c in $command)
     Write-Host -foregroundcolor cyan "Starting new PowerShell for command $c"
     Start-Process powershell -arg "-noexit -command ""& {$c}""  "
} #end function Start-NewPowerShellConsole

# *** Entry Point To Script ***

Start-NewPowerShellConsole -command $command

# ------------------ END OF SCRIPT -------------------------------------------------


Well, this concludes another edition of Quick-Hits Friday. It also concludes another exciting week on the Script Center. Join us next week as we delve into the mysteries of…well, we will let that remain a mystery for now.

If you want to know exactly what we will be discussing on Monday, follow us on Twitter or Facebook. If you have any questions, send e-mail to us at scripter@microsoft.com or post your questions on the Official Scripting Guys Forum. See you on Monday. Until then, have an awesome weekend.


Ed Wilson and Craig Liebendorfer, Scripting Guys 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • How can I completly remove SQL 2005 with a script?