Using Bookmarks in SQL Server Management Studio

In SQL Server one way we optimize code is by adding indexes. Indexes help queries by essentially letting the engine know “hey, here’s the fast way to get directly to the data you want”. The alternative is doing full table scans to read all the data, which sucks.

When reviewing code in SQL Server Management Studio (SSMS) we have a similar built-in tool to help save you time: Bookmarks! Bookmarks aren’t quite like indexes but they do help you mark locations in code that you can quickly jump to. This is especially helpful when reviewing code in large scripts.

For this example I’m going to use the amazingly useful Diagnostic Information Queries script (which you should be using if you’re not already) from Glenn Berry (Twitter | Blog). This script is jam packed with all sorts of amazingly insightful information on your server and databases. However I often need to jump around to different parts of the script.

For example, when performance tuning I like to run Glenn’s code for getting back the top wait types on the server. After that I jump down to the section on top cached queries/procedures to get a sense of what’s happening. If I do this manually there’s about 300 lines of code separating the two sections. That’s a lot of annoying scrolling!

Instead what you can do is create bookmarks at these locations. You can do this one of two ways. First set your cursor at the desired location in code. You can set bookmarks by going to the Edit menu –>Bookmarks->Toggle Bookmark. The other way is when you have your cursor set you can use the keyboard shortcut of Ctrl+K, Ctrl+K to add a bookmark. When you’ve created a bookmark you’ll see a small grey/white box in the left side window. Now scroll to other locations in code you’d like a bookmark and repeat this process.

To navigate between Bookmarks you can use the keyboard shortcut Ctrl+K, Ctrl+N to go to next one or Ctrl+K, Ctrl+P to navigate to previous. Now you can quickly and easily jump between sections of code! Happy coding.

Cross posted at my new MSDN blog: http://blogs.msdn.com/b/sqlchicken_coop/archive/2014/11/06/using-bookmarks-in-sql-server-management-studio.aspx

2013/05/232013/05/22

Business Objects on Linux and SQL Server

This is just a quick post to share a lesson learned while I was on an engagement where the client’s reporting environment was using SAP Business Objects (BO)running on Linux for reporting. We were doing a test to move the underlying data warehouse from another database platform to SQL Server 2008 R2.

As we changed connections over, however, we quickly ran into a roadblock. It seems when we tried to make a connection to SQL Server via BO we got the error of ‘Unable to bind to Configuration Objects WIS 10901’. What made this situation strange is that from a Windows box you could connect but from Linux itself, it wasn’t having any of it. After some digging around we found we needed a third party ODBC driver to make this connection work.

It was suggested to us by the folks at SAP that we use a third party driver for ODBC connectivity. We were pointed to drivers by DataDirect (NOTE: This is not an endorsement for said product, this is simply the solution we tried and went with. There are several third party vendors that offer Linux ODBC connectivity so please evaluate and choose what works for your environment). Have you run in to this issue before? How’d you handle it? Feel free to share your solutions in comments.

2013/05/162013/05/15

Magnify SQL Text with SSMS 2012

This is just a quick tip to help with folks who present SQL code at events such as SQL Saturday. While most presenters use tools like ZoomIt (which if you present, please please learn to use this wonderful, free tool) sometimes it can get nauseating for attendees to watch you constantly zooming in and out, especially on code.

A quick way around this is by using the magnification feature in SQL Server Management Studio 2012. To do this simply hold down the Ctrl button on your keyboard and with your mouse scroll the mouse wheel up to increase the magnification and scroll down to decrease it. Alternatively you can simply click on the magnification dropdown, which is located at the bottom left of the query window (by default) and select your desired level of magnification.

That’s it! Now you can quickly magnify your code to make it easier for your audience to see and you can reserve the zooming to highlight other areas as needed.

2012/02/13

Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly

SSIS Expressions

Expressions in SSIS are great. They allow you to create dynamic values for all sorts of stuff like variables, connection strings, properties for almost anything, etc. One huge issue that tends to trip up a lot of folks, especially those new to SSIS, is the evaluation of those expressions when using variables.

The Issue

You create an SSIS variable with an expression but at runtime the expression is not evaluating as expected. Instead the expression value is using the default/static value of the variable.

Quick Answer

Make sure property for ‘Evaluate as an Expression” on the variable is set to True. Without it being set to true, the variable will evaluate to the hard set value.

Continue reading “Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly”

2012/01/23

Monday Morning Mistakes: Remote Connectivity to SQL Server

Inspired by common emails and questions I see, I figured I’d do a series of blog posts on common mistakes folks make with SQL Server called Monday Morning Mistakes (or #sqlM3 for short, since we all love quick hashtags these days). These are meant as quick fixes, nothing too comprehensive. Also since I just made up a hashtag, feel free to share your own #sqlM3 tips on Twitter anytime! Without further ado…

Today’s quick issue: Can connect to SQL Server locally but can’t connect from other server or computer.

Quick answer: Remote connections (read also: any connections that are not local) to SQL Server are disabled by default. This behavior is default in SQL Server 2005 and higher. You have to manually enable TCP/IP protocol to instance to allow connectivity. This requires a service restart to take effect.

Continue reading “Monday Morning Mistakes: Remote Connectivity to SQL Server”

2010/07/282014/08/14

Sharing Remote Desktop Sessions

I work for a very large company where divisions are spread out literally all over the world. There are many times where I have to confer with someone else remotely in order to get something done or figure out an issue. When working on a server we all know and love using Remote Desktop to connect but did you know there’s a way to share your session so both parties can interact? Well did you know it’s built in to Windows 2003/2008?

To do this is actually quite simple and I’m surprised it’s not more well known. The first step is to remote in to your server as you normally would. Once you’re logged in, go to the Terminal Services Manager by clicking on the Start button, going to Administrative Tools and then opening Terminal Services Manager (NOTE: In 2008, Terminal Services manager is in it’s own Terminal Services folder under Administrative tools)

Once you open Terminal Services Manager you’ll see a box showing which users are currently logged into the system. The icon with the little guy’s green head is your session. Other sessions will show up with a white head (no, not the Clearasil variety).

Now right click the other user’s session and select Remote Control from the context menu. You’ll be asked which hotkey combination you want to use to end the remote session. By default the star key on your numeric pad + Ctrl key will end your session. Click OK to accept this default.

Once you click ok the other person will be prompted if they want to share their session. Once they accept you’ll be connected to their session! This allows both of you to control the same session so you can both type, move windows, check out settings, etc. Once you’re done simply hold down the Ctrl key and hit the star (*) key on your numeric pad to exit the session (assuming you used default escape Hot Key combination). This is a built-in function of Windows Server and makes a handy tool when collaborating with co-workers!

2010/04/27

The PowerShell and XML Corollary

big-bang-theory-cast — Girl Property Surrounded by Geek Objects

I’ve just started watching The Big Bang Theory so I figured I’d borrow a naming convention from their episodes with this blog post. This is a quick post as the problem itself is small and doesn’t have an application (yet).

A co-worker of mine was asked by a higher-up about possibly creating an in-house iPhone application that displays information from an existing SQL Server database. He is currently dabbling in iPhone development and he found that querying SQL Server directly was going to be a bit of a pain so he asked me if we could access the data via other (read also: easier) means such as reading from a data dump file that is in XML format. This limitation comes from the fact that there are no native API’s for Microsoft SQL in Cocoa. As a production DBA seeing anything involving XML gives me the heebie jeebies and I rely on the kindness of strangers, Scarlett O’Hara-style, to help me bridge my ignorance gap. So first thing I needed to find out was what was the easiest way to translate SQL Server data into XML. Now, I’m not completely dense and I know that from SQL Server 2005 and higher there were “a lot of things” put into the product that helped in the XML space but this particular server I am connecting to is SQL 2000 (ewww I know) and I wasn’t sure if it even handled XML the way I needed. I turned on the SQL Bat-signal and asked my Twitter folks to enlighten me on this enigma.

Continue reading “The PowerShell and XML Corollary”

2010/03/29

Missing Crystal Reports 10.5 runtime?

The other morning I was migrating an application from a desktop machine to server. Some real fun with this project includes zero documentation from the developer (he was a contractor who did a rush job and left). Thankfully I have experience migrating applications from dev to acceptance to prod so I knew to look for missing assemblies and whatnot.

My big headache this morning, and the source of this posting, was the missing Crystal Reports assemblies since this was developed in Visual Studio 2008. My first indication of a problem was that locally (on the server) I tried pulling up the page and got greeted with the following:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly ‘CrystalDecisions.CrystalReports.Engine, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304’ or one of its dependencies. The system cannot find the file specified.

Source Error:

After some quick Google searching I came across this post at Egghead Cafe with my solution. They provided me links to download the runtime I needed to install. For the sake of helping others I have decided to host the files as well just in case those links die from that link. After installing the runtime on the server I restarted IIS by issuing the iisreset /restart command from the command prompt. After the restart I was able to successfully pull up my page without a problem.

Download Crystal runtime files here (both x86 and x64)

2009/12/18

Need #SQLHelp? Throw up the Bat-Signal

Brent Ozar (@Brento) and Aaron Nelson (@sqlvariant) were talking discussing on Twitter the creation of dedicated hashtag for those who need help with SQL Server issues. As Brent is known for doing, he magically whipped up a blog post on the matter explaining how this new hashtag, #SQLHELP, should be used. I highly recommend you head over to Brent’s blog and read up on how to use #SQLHelp. I’ve found Twitter to be an invaluable tool for getting quick responses to any SQL (and non-SQL as well) questions I have. If anything this should be a great hashtag to search on and show to management as a good business-value for Twitter in the workplace!

2009/10/27

Windows 7 and Multiple Clocks

So unless you’ve been living under a rock for the last few weeks/months, you may have heard about a little thing called Windows 7. Windows 7 is Microsoft’s latest iteration of its Operating System and along with a slew of other stuff (i.e. security enhancements, pretty new stuff to love, Windows management features, etc.) there’s a nice little feature I find comes in handy, especially when you work with a global community like PASS (and you SQL peeps thought this post wasn’t going to pertain to you…)

The feature I’m talking about is multiple clocks in your system tray. I’m not sure if this was available in Vista as well but I thought I’d share with everyone how to do it in Windows 7. First click on your clock in the bottom right-hand corner.

Next click on the ‘Change date and time settings’ link to bring up the Date and Time box. Click on the tab for Additional Clocks.

Once here you can add up to two additional clocks in addition to your default clock for a total number of 3 possible clocks! Simple check the ‘Show this clock’ box to enable the clock. Use the dropdown menu to select the appropriate time zone you wish to monitor. You can also give the clock a custom label for display purposes. Once you’re done click OK.

Now if you click on your clock in your taskbar again you should see your newly added clocks!

I live in Florida so the clocks I like to monitor are either West Coast or India since those are the two time zones I tend to end up working with the most. Sure I could probably do the math for someplace close like the West Coast but I’m a geek, and I like to see my information quick and at a glance.

And since we’re talking about Windows 7, I’ve included in this blog a zip file of step-by-step directions on how to do this (screenshots included) courtesy of another really cool feature in Windows 7 called the Problem Steps Recorder. With this tool you can see step-by-step how a user got to a problem or you can use it to document an issue like I have here.

Download Zip File