SSIS Junkie : sql server management studio, sql server

Query window custom colouring now works properly [SSMS]

jamiet — Thu, 11 Aug 2011 19:28:00 GMT

SQL Server 2008 introduced a new feature that had the potential to be very useful, the ability to ~~color~~ colour-code a query window in SSMS to represent the server that was being connected to. This was configured using the properties of a server in the Registered Servers pane:

Unfortunately this feature was made less than useful, dangerous even, by its behaviour. Changing the connection of a query window did not affect the colour thus raising the prospect of a user issuing queries against what they believe is a development server which in actuality is a production server. I haven’t met anyone since that hasn’t denounced this “feature” of SSMS.

Good news though. According to these Connect submissions:

this issue was fixed in SQL Server 2008 R2 Service Pack 1 and the fix will also appear in SQL Server Denali RTM (its not in any of the currently available CTPs). From Program Manager Sanjay Nagamangalam:

This bug and a related connect bug have been fixed in SSMS in SQL Server 2008 R2 SP1. Unfortunately, the fix did not make it in time for SSMS in Denali CTP3 and will be available in a subsequent release.

Good news indeed!

@jamiet

Thoughts on Nexus in SQL Server PDW

jamiet — Fri, 17 Jun 2011 10:49:30 GMT

I have been on a SQL Server Parallel Data Warehouse (aka PDW) training course this week and was interested to learn that you can't (yet) use SQL Server Management Studio (SSMS) against PDW, instead they ship a 3rd party tool called Nexus Chameleon. This was a bit of a disappointment at the beginning of the week (I'd prefer parity across SQL Server editions) but actually, having used Nexus for 3 days, I'm rather getting used to it. Some of it is a bit clunky (e.g. everything goes via an ODBC DSN) but once you get into using it its the epitome of "it just works".

For example, over the past few years I have come to rely on intellisense in SSMS and have learnt to cope with its nuances. There is no intellisense in Nexus but you know what....I don't really miss it that much. In a sense its a breath of fresh air not having to hope that you've crossed the line into that will it work/won't it work grey area with SSMS intellisense. And I don't end up with writing @@CONNECTIONS instead of FROM anymore (anyone else suffer from this?) :)

Moreover, Nexus is a standalone tool. Its not a bunch of features shoehorned into something else (Visual Studio).

Another thing I like about Nexus is that you can actually do something with your resultset client-side. Take a look at the screenshots below:

You can see Nexus allows you to group a resultest by a column or set of columns. Nice touch. I know that many people have submitted Connect requests asking for the ability to do similar things in SSMS that would mean we don't have to copy resultsets into Excel (I know I have) - Nexus is a step in that direction.

Its refreshing to use a tool that just gets out of the way yet still has some really useful features. How ironic that it gets shipped inside an edition of SQL Server! If I had the option of using Nexus in my day job I suspect that over time I would probably gravitate back to SSMS because as yet I haven’t really stretched Nexus’ capabilities, overall SSMS *does* have more features and up until now I've never really had any objections to it ... but its been an interesting awakening into the nuances that plague SSMS.

Anyone else used Nexus? Any thoughts on it?

@Jamiet

SSMS in SQL Server Denali – “Insert snippet” and “Surround with”

jamiet — Mon, 08 Nov 2010 22:40:00 GMT

SQL Server Denali CTP1 has hit the streets! I’ve been lucky enough to have been playing with it for a couple of weeks (thank you to those responsible – you know who you are) and that means I can now start talking publicly about some of the cool stuff I’ve learnt about this next version of SQL Server. In this blog post I’m quickly going to cover two new productivity enhancements in SQL Server Management Studio (SSMS):

Surround With
Insert Snippet

They’re very similar to each other so it makes sense to cover them both together.

Surround With

First “Surround With”. This feature enables you to edit some existing code to wrap it with the scaffolding for common coding constructs like BEGIN…END, IF () BEGIN…END and WHILE () BEGIN…END. A picture speaks a thousand words so rather than tell you I’ll just show you instead.

Take the following code snippet:

Its a perfectly valid stored procedure definition although I happen to be one of those guys who likes to have BEGIN and END statements marking out the stored procedure body – Surround With can help me with that.

First I select the code that I want to “surround”:

Right-click and select “Surround With” (note that there is a keyboard shortcut too):

That brings up the surround with submenu where I browse to the option that I want, in this case its “BEGIN…END”

Hit Enter and voila…I’ve surrounded my code with a BEGIN…END block:

That’s it! A small new feature but a pretty useful one I hope you’ll agree. BEGIN…END is a fairly noddy use case but it gets more useful for the slightly more verbose IF() and WHILE() constructs.

Insert Snippet

This is probably the more useful of the two features I’m talking about here. “Insert Snippet” is similar to “Surround With” though I suspect you’ll be using it a lot more, in short what it does is create the skeleton DDL code for a new database object. All you need to do is right-click in a query window (or hit the keyboard shortcut CTRL+K, CTRKLX) and you’re presented with a list of code templates for various object types.

All you need to do is browse to the one you want, hit Enter, and you’re straight into the important business of development. Nice!

There is a Code Snippets Manager available for both Surround With and Insert Snippet that enables you to add your own snippets although not, as far I can see, edit the existing ones. I have an open Connect submission asking for that to be changed.

That’s it! Features such as these have been available in third party add-ins for a while but its nice to see them make their way into the core product. Enjoy!

@jamiet

Recover Backed-up query files in SQL Server Management Studio

jamiet — Mon, 19 Jul 2010 21:25:44 GMT

This is a useful little tip that I discovered today.

If SQL Server Management Studio (SSMS) ever crashes on you for whatever reason then there is a chance that you’ll lose whatever queries you had open at the time of the crash. Sometimes when you re-open SSMS it will ask you if you want to re-open any files that you recover but for some reason it doesn’t always do that and in that instance you may think that your work has gone for good.

Happily that’s not the case, not always anyway. Even if SSMS does not prompt you to recover those files they are probably still present on your machine. Go and check in your C:\Users\YOURUSERIDHERE\Documents\SQL Server Management Studio\Backup Files\Solution1 folder and you should find them waiting for you.

I’m sure most of you could have worked out where they were stored, the real trick is knowing that they are stored in the first place and now….you do!

@Jamiet