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 … Continue reading Finding the Microsoft File Transfer Manager

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 … Continue reading A review – Microsoft Windows PowerShell Step By Step (Ed Wilson)

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 … Continue reading A review – Windows PowerShell Scripting Guide

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 … Continue reading Disable WiFi with PowerShell

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, … Continue reading PowerShell changes in SQL Server 2008 RC0

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        : SqlServerCmdletSnapinPSVersion   : 1.0Description : This is a PowerShell snap-in that includes various SQL Server cmdlets. Name        : SqlServerProviderSnapinPSVersion   … Continue reading SQL Server 2008 PowerShell SnapIn

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. 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 … Continue reading SQL Server 2008 CTP6 gives you PowerShell

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.definitionfrom 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_idwhere t.name … Continue reading Two ways to find / drop a default constraint without knowing its name

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 … Continue reading Powershell script from my SQL presentation