You are here: Home > 2009 > December
Archive | December, 2009

Little Things That Count: Copying Names in Management Studio

by on December 4, 2009

This post is about a really little detail that isn’t a big deal.

But to me it actually is a little bit of a big deal. Or would that be a little deal that just really adds up over time?  Either way, this is the most fun, trivial-but-helpful-and-fun-in-a-weird-way thing I’ve learned about SQL Server Management Studio in a long time. Maybe a lot of people know this already?

So here’s the revelation (a very small drumroll, please)…

In management studio, you just have to highlight an object name and use ctrl-c to copy the object name.

You don’t have to use F2 to edit the object name like you do in Windows Explorer (or double click).

Here’s the add on to the revelation (an even smaller drumroll, please)…

This also works with multi-select in the object explorer details window. More information about the Object Explorer Details pane are here on Technet.

Ta Da!

OK, so here’s why this matters to me.  First, it saves me time and anxiety. For years, I’ve been using F2 to get the cursor to go over the object name, then copying the name, getting nervous because I absolutely do NOT want to change the name, and then hitting escape really fast to make sure I don’t change anything. Because this whole process creeps me out (because I REALLY don’t want to change any object names in my application databases by accident). To avoid this whole issue, usually I just retype the name of whatever the object is. And some of our objects have really long names which are quite similar.

Second, it’s just really handy with the multi-select option in Object Explorer Details. Today I needed to look at a list of logins in production and create a list of how they relate to a list of logins in another domain. It was so simple, I just opened the object explorer details pane for the logins, highlighted all umpteen logins I wanted, and pasted them into Excel.

Why Didn’t I Figure This Out Before???

I know why I never knew this: it’s because when you have an item highlighted and right click in Management Studio, there is no ‘Copy’ or ‘Copy to Clipboard’ option. Maybe this is because they didn’t want people to think that somehow the whole table or login or object itself would be sucked into the Windows Clipboard? I’m not sure, but I’m guessing this is what kept me in the dark for so long.

Bonus Fun Little Trick

The bonus trick is that to use sp_configure, you don’t have to specify the entire name of the option. So for example, to show the value of ‘max degree of parallelism’, you can just run:

sp_configure degree

This also works for setting values, so make sure you’re setting the value you really want to set. SQL Server will print out the full name of the option you are configuring after it runs, so you can read that for reassurance before you reconfigure.

This last tidbit is filed under “Things Gail Shaw mentioned that people Tweeted” :)

Photo attribution:

Comments { 0 }

SQL 2008 Agent Jobs – Tokens work in PowerShell!

by on December 2, 2009

The Joy of Tokens and PoSH

I have been working away building out servers in our new prod test environment, and automating as much as possible along the way with PowerShell. I  have to say that it’s been really fun and PoSH has brought back that loving feeling that I always had for Perl.  If a programming language can be friendly, PowerShell manages it. 

One thing I had the chance to test out yesterday was using SQL Agent tokens in PowerShell type SQL Agent steps. I am very happy to find that the tokens work just like I was  hoping they would. In other words, this works:

$instanceName = "$(ESCAPE_SQUOTE(SRVR))"
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

The SRVR token will return the connection name of the sql server with the instance name. This works on standalone and named instances, and for clustered instances you get netname\instancename.  So this is perfect for creating a sql server object and connecting to it.

Why would you want to do this? Why not just use TSQL?

The primary reason I currently have to use a PoSH step is to execute commands against the operating system. In this case, I needed to connect to my sql  instance and get some information about it to then use in the OS level commands I needed to run. I  have a specific example of this I’ll post in the coming days.

How Do I Test This Out?

If you’re new to PowerShell, find yourself a test box to play around on. It needs to have PowerShell and SQL 2005 or 2008 (the client tools/SSMS are enough, they provide the SMO assemblies). Open a PowerShell command prompt as administrator. To get yourself connected to a SQL Server Instance, it’s as simple as this:

#This loads up the SMO module which will be used to connect to SQL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instanceName = 'NETNAME\INSTANCE'; # or just "COMPUTERNAME" for the default instance

#this passes in the name you used above to create a new object
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

#this will show you a table of all the properties of your SQL Server.
$srv.Properties | format-table -auto

#this will show you a table of all the properties of tempdb on your SQL Server.
$srv.Databases['tempdb'].Properties | format-table -auto

More Information

Comments { 0 }