桃子视频

Category: Tips

Categories
Syndication Tips

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 鈥渉ey, here鈥檚 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鈥檛 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鈥檓 going to use the amazingly useful聽 (which you should be using if you鈥檙e not already) from Glenn Berry ( | ). 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鈥檚 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鈥檚 happening. If I do this manually there鈥檚 about 300 lines of code separating the two sections. That鈥檚 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鈥檝e created a bookmark you鈥檒l see a small grey/white box in the left side window. Now scroll to other locations in code you鈥檇 like a bookmark and repeat this process.

Adding Bookmarks in SSMS

 

 

 

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.

Bookmarks in action

Cross posted at my new MSDN blog:聽

Categories
Syndication Tips

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 (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.

Categories
Syndication Tips

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 (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鈥檚 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.

Categories
Monday Morning Mistakes Syndication Tips

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 鈥楨valuate 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”
Categories
Monday Morning Mistakes Syndication Tips

Monday Morning Mistakes: Remote Connectivity to SQL Server

Inspired by common emails and questions I聽 see, I figured I鈥檇 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 anytime! Without further ado鈥�

Today鈥檚 quick issue: Can connect to SQL Server locally but can鈥檛 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”
Categories
Syndication Tips

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 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!

Categories
PowerShell Syndication Tips

The PowerShell and XML Corollary

Girl Property Surrounded by Geek Objects

I’ve just started watching 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 and asked my Twitter folks to enlighten me on this enigma.

Continue reading “The PowerShell and XML Corollary”
Categories
Documentation Tips

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 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.

Categories
Syndication Tips

Need #SQLHelp? Throw up the Bat-Signal

Get yourself some #SQLHelp
Get yourself some #SQLHelp

Brent Ozar ()聽and Aaron Nelson ()聽were talking discussing on Twitter the creation of聽.聽聽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 . 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!

Categories
Syndication Tips Windows

Windows 7 and Multiple Clocks

So unless you鈥檝e been living under a rock for the last few weeks/months, you may have heard about a little thing called . Windows 7 is Microsoft鈥檚 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鈥檚 a nice little feature I find comes in handy, especially when you work with a global community like (and you SQL peeps thought this post wasn’t going to pertain to you…)

The feature I鈥檓 talking about is multiple clocks in your system tray. I鈥檓 not sure if this was available in Vista as well but I thought I鈥檇 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 鈥楥hange 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 鈥楽how 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鈥檙e 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鈥檙e talking about Windows 7, I鈥檝e 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 .聽 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