Category Archives: 3051

Finding the Microsoft File Transfer Manager

This is really just a reminder blog post for me. Way too often I find that I have closed the Microsoft File Transfer Manager for one reason or another, and I want to start it up again to resume some download from my MSDN Subscription. Like today, I need to grab the latest version of SQL Server 2008 R2, which includes built-in Split, RegEx and Fuzzy matching features for T-SQL (something I’ve wanted for a long time, and that I’ll blog more about later, once I’ve had a chance to try it out). It’s a large download, and not something I want going when I’m on a 3G connection, but happy to use a WiFi connection for. And there’s just the odd time when I’ve forgotten I’m downloading something and it’s sitting there, partly downloaded…

So I find myself looking for the application to run. When it installs by default because you click on a download, it doesn’t put a shortcut in the Start Menu anywhere. So I’ve been known to even go and restart a download (just telling it to Cancel instead of agreeing to it), just to start the process. Then I can jump into PowerShell and run this command:

get-process -name transfermgr | select path

…which tells me that the path of the transfermgr process is at c:\windows\downloaded program files\TransferMgr.exe

So now I can just run it from there. The idea of this post is to remind me where it is, so that I don’t have to hunt for it every time.

A review – Microsoft Windows PowerShell Step By Step (Ed Wilson)

Another book review, and another giveaway for the Adelaide SQL Server User Group. This time, it’s Microsoft Windows PowerShell Step By Step.

Last month I had picked up the Windows PowerShell Scripting Guide, wondering if it was going to be a good recommendation for people who were interesting in getting into PowerShell. Even though I thought the book was very good (particularly if you want to use PowerShell to access the innards of a Windows installation), it didn’t seem like the right book for recommending for PowerShell beginners.

This book is though, and I’ll definitely recommend it for people wanting to get into PowerShell.

It’s worth pointing out that it’s a thinner (and cheaper) book than the other one. It certainly doesn’t cover how to perform the variety of Windows Admin commands that the Scripting Guide did. But what it replaces that with is a guide on getting the most out of PowerShell. PowerShell is used in so many different products now, it’s no longer just the domain of Windows Administrators. Developers can use PowerShell for unit tests. DBAs can use PowerShell to perform routine maintenance. Just about everyone in IT could use PowerShell to make their job easier. My background is in development, not system administration, so I’m always keen to write code to automate tasks. I was never that keen on VBScript, but PowerShell gives me a much richer environment while also being much closer to the system itself. I can hook into subsystems of Windows and .Net objects easily, and into environment variables, certificates and more, piping the results into other functions and utilities to extend the scripts as much as my imagination can provide. I’m always happy to recommend PowerShell as an important skill for the future.

And this book can people get introduced to PowerShell, walking them (step by step, just like the title suggests) into the depths of PowerShell – leveraging functions and providers, and a good introduction to using PowerShell with WMI and Exchange. I’ve enjoyed reading it, and plan to re-read it a few times over the next months, looking for those techniques that I’m not using (yet) but that I’d like become more familiar with. PowerShell reminds me of my early days using the vi editor (which I still use as my preferred text editor in Windows). We were forced to use vi at university, and the learning curve felt very steep. It seemed as if I learned some new (and better) way of doing something every day, to the extent that these days I still find it by far the quickest way to edit many types of text files. This book feels like those fellow students of mine, pointing out features I didn’t know existed even after I felt proficient (although I’m pleased to mention, not as many as I thought there might have been).

The book also has a CD full of examples that I need to find time to go through (and tweak, and practise, and learn). It includes a bunch of utilities, and an electronic copy of the book as well. Having said that, the book isn’t too big to carry with me for a while, and I’m sure will be a regular read for those “no electronic device” periods of flights.

A review – Windows PowerShell Scripting Guide

As a User Group leader, I have the chance to review books for MSPress (and then give a copy away to the user group too!). So at the end of last month I got sent a copy of Windows PowerShell Scripting Guide, by Ed Wilson (I hope that link works – if it doesn’t, find it in the Windows section).

I wasn’t sure what to expect of this book. I had just done a presentation at the user group about PowerShell with SQL Server, and I was curious to see what kinds of things this book covered. People ask me now and then about a book for learning PowerShell, and I wanted to see if this could be the one to recommend.

As a book for learning PowerShell, I’m not sure that it’s really the best one to grab. I intend to get a look at Microsoft Windows PowerShell Step By Step (also by Ed Wilson) to see how it compares. But that doesn’t mean that I won’t recommend this book to people.

What this book does give is a nice overview of PowerShell, followed by a bunch of areas within Windows for which people often do scripting. So for me, this is really handy. I don’t consider myself much of a Windows Administrator, and this book does a nice job of filling in some of the gaps. It’s quite heavy on the WMI side, but that’s probably fair enough (considering that you really can’t do much in Windows Scripting before wanting to take advantage of WMI). It goes through subjects such as services, shares, logs, networking, user admin, IIS, and more. As someone involved primarily on the SQL side, I’m not sure how much I’m really going to use much of this, but there will definitely be times when I do. Scripts for creating local users and groups will certainly come in handy, as will many others in the book.

This book also comes with a CD, containing all the scripts that are in the book. It seems like a great resource, which I’m sure I’ll go back to repeatedly. The book is over 650 pages, which will certainly take up space in anyone’s bookshelf, but if you’re a Windows Administrator, or someone who’s just looking to expand their PowerShell ability, then I can thoroughly recommend it.

If you’re a member of my user group, then you can currently buy this book for 40% less than the price listed at the MSPress store, but as well as that I’ll have a copy of it to give away at the March meeting.

Disable WiFi with PowerShell

A bit like putting your laptop into Flight Mode, I guess.

I fly fairly often at the moment, but my Wireless switch on the side of my laptop is slowly wearing out. Occasionally when it’s in the OFF position, it thinks it’s actually in the ON position for a moment, and I see the WiFi LED come on. It’s probably a warranty item which I should get fixed soon. But until I do, I’m in the habit of opening up Computer Manager and disabling the device.

So I got to thinking that I should be able to do this with PowerShell, and make myself a “FlightMode” script.

I started by figuring that there must be a WMI class that described the network adapters. So I ran:

get-wmiobject -list | select-string adapter

Sure enough, one of them was Win32_NetworkAdapter. So now I could identify the network adapters available.

get-wmiobject -class win32_networkadapter -namespace root\CIMV2

Great. Plenty of them, but in particular, one that was my Wireless one. I could grab that alone by running:

get-wmiobject -class win32_networkadapter -namespace root\CIMV2 | where-object {$_.Name -match “Wireless”}

Now to find out out how to disable or enable it. Easy… pipe my object into get-member.

get-wmiobject -class win32_networkadapter -namespace root\CIMV2 | where-object {$_.Name -match “Wireless”} | get-member

Seeing Enable() and Disable() methods makes this easy…

get-wmiobject -class win32_networkadapter -namespace root\CIMV2 | where-object {$_.Name -match “Wireless”} | % {$_.Disable()}

get-wmiobject -class win32_networkadapter -namespace root\CIMV2 | where-object {$_.Name -match “Wireless”} | % {$_.Enable()}

I’m looking for anything that matches “Wireless”, just in case. I really don’t want to be on a plane and accidentally using a WiFi network adapter. Next I need to work out a script which will switch my timezone.

PowerShell changes in SQL Server 2008 RC0

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular PowerShell window. Except that if you’ve just installed SQL Server 2008 RC0, you may have noticed it doesn’t work any more.

Never fear, they’ve just changed the name a bit. They’ve put 100 on the end of both. So just change your console file to list the two snapins as SqlServerProviderSnapin100 and SqlServerCmdletSnapin100. Calling PowerShell with the -PSConsoleFile option still works as before of course.

On a lighter note, they’ve fixed the case sensitivity. I can now “cd databases” just fine, without it needing me to use a capital D.

SQL Server 2008 PowerShell SnapIn

Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).

But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it’s not a SnapIn for PowerShell, but it turns out it is. We ran:

Get-PSSnapin -reg

…and got the following results:

Name        : SqlServerCmdletSnapin
PSVersion   : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin
PSVersion   : 1.0
Description : SQL Server Provider

So then I ran:

Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin

And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.

<?xml version=”1.0″ encoding=”utf-8″?>
<PSConsoleFile ConsoleSchemaVersion=”1.0″>
  <PSVersion>1.0</PSVersion>
  <PSSnapIns>
    <PSSnapIn Name=”SqlServerProviderSnapin” />
    <PSSnapIn Name=”SqlServerCmdletSnapin” />
  </PSSnapIns>
</PSConsoleFile>

I’m still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it’s worth having it as an option.

Using ScriptingOptions with SQLPS

I’ve written before that SQL Server 2008 (February CTP) gives you SQLPS – a PowerShell interface to SQL Server.

So I was trying to get the ScriptingOptions happening, and this is the only way I’ve found so far. It’s ugly, and if you have a better way of doing this, please post a comment for me.

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.DriAllConstraints = $true
(get-item Sales.Store).Script($so)

And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.

SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

powershell_sql Basically, you can now open up PowerShell, and change directory to the PowerShell drive “SQL:”. Then change directories through the instances, databases, tables, and so on. At any point, try something like “dir | gm” (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.

Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO – when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions – I found this a little disappointing, and I haven’t figured out the best way of getting help on methods that are on these objects either.

It’s definitely a good start though – should be a very nice feature of SQL Server 2008.

Two ways to find / drop a default constraint without knowing its name

So suppose you’ve created a default constraint without specifying the name (on a SQL Server 2005 machine). You do know the table and column, of course – you just don’t have the name of the default constraint. Now you want to find the name so that you can drop the constraint.

A nice query against the system catalog views can help. Something like:

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N’Department’
set @col_name = N’ModifiedDate’

select t.name, c.name, d.name, d.definition
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name = @col_name

–should do the trick. If you want to then create some dynamic SQL to do the remove, that’s quite easy too.

Alternatively, if you’d rather use PowerShell, you could do something like:

[reflection.assembly]::LoadwithPartialName(“Microsoft.SQLServer.SMO”)
$sql = New-Object ‘Microsoft.sqlserver.management.smo.server’ ‘localhost’
($sql.Databases[“AdventureWorks”].Tables | where-object -filter {$_.Name -eq “Department”}).Columns[“ModifiedDate”].DefaultConstraint.Drop()

This is using SMO to jump to the appropriate database, find the table called Department, grab the appropriate column out of it and drop the default constraint on it. I’m not sure why I couldn’t just use $sql.Databases[“AdventureWorks”].Tables[“Department”].Columns[“… I guess there’s something I’m not quite getting there. Possibly something about schemas, because I can use this method if the table is in the dbo schema (possibly read ‘default’ there).

Powershell script from my SQL presentation

Last week I presented at my user-group about PowerShell and why every DBA should know this. The talk went for just over an hour, and as most of the audience hadn’t used PowerShell at all, I started from the top and really pushed concepts like “You pipe objects not text”. The script can be downloaded from here.

So then by the time I got around to talking about the fact that you can really easily hook into ADO and SMO, I think the audience were already caught on the idea that PowerShell really is very powerful and that anything you can do with SMO can be done really easily with PowerShell too. Most DBAs write scripts that use SMO to manage their SQL boxes. In the past they’ve used VBScript, but I think they should use PowerShell – it’s got so much more going for it, and because it can replace cmd, there’s almost no reason not to use it.

The talk didn’t go into all the stuff you can do with SMO – that’s a different presentation. This was a way of demonstrating that you can use PowerShell for SMO, as well as everything else you might want from a sysadmin perspective.