THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

My notes from SQL Saturday

My SQL Server Saturday topic is SQL Server Tips and Tricks, primarily SSMS Tips and Tricks. Keep in mind that some suggestions appropriate for a developer's workstation might not be appropriate for a production server. This post provides additional detail to certain topics in my presentation.

The two special files/folders I demo are:

connection.udl
settings.{ED7BA470-8E54-465E-825C-99712043E01C}

The first is a file. It can have any file name, but the extension must be udl. Be sure that you are configured to show file extensions before creating it, of course. For full details and screen captures, see my post here. The second is actually a folder, not a file. Yes, it is a folder with a dot in the folder name. You can put anything to the left of the dot, but you can’t change the guid. Double click the icon and you have access to all system settings in a single place.

The additional right-click menu options in Windows Explorer on Windows 7 and Windows Server 2008 R2 are activated by holder down the Shift key before and while doing the right-click. When you do this with focus set to a folder, you’ll see Open command window here. When focus is set to either a file or folder and you press the Shift key before and while doing the right-click, you will see Copy as path as an additional menu option.

Linked servers can often be a pain to deal with. I had one particular troublesome third party driver that I never could use to create a working linked server. The vendor worked with me and confirmed that the driver was working at the command line. It just didn’t work within SSMS. The operating system was Windows Server 2008 R2. The command prompt was an elevated command prompt. Do you see what the problem is now? I could get a linked server working in SSMS if I ran SSMS as administrator. The driver worked only when running as an administrator.

I demonstrate using regular expressions in my demos. I’m writing a chapter about regular expressions for the upcoming SQL Server MVP Deep Dives Volume 2.

SQL Search is free and can be downloaded from http://www.red-gate.com/products/sql-development/sql-search/. It is a huge timesaver.

My favorite free tool for mounting iso files is Virtual CloneDrive which can be downloaded from http://www.slysoft.com/en/virtual-clonedrive.html. I've used better known iso mounting utilities and I greatly prefer this one. Since SQL Server installation media is most commonly in iso form, it's great to be able to mount and use the iso file directly. It's much faster to install SQL Server from a mounted iso file instead of optical media.

For additional right-click send to options, I use Send To Toys, which is free and available at http://www.gabrieleponti.com/software/#sendtotoys. I do a custom installation and only install the send to sa name feature. After installing it, I configure it so that send to as name does not include the double quotes and the trailing blank. It's a great tool for capturing a file's entire path and name for pasting into a script that you're developing.

Another one of the things I demonstrate is using Ctrl-R. It toggles the results pane on and off. When you open a new query window, you only have a query pane at first. After executing the query, you have a results pane. What a lot of developers don’t know is that you can use Ctrl-R to hide the results pane. Use it again to restore the results pane. It’s just a toggle for visibility. It doesn’t execute the query.

One of the things I encourage people to do is use portable applications. Think about this. You’re doing work on a server, perhaps installing a third party driver, and discover that you need to refer to the documentation which is either a Word document or a PDF file. Or maybe you are using SSIS to import an Excel file and you’d like to see what’s in the file. You don’t want Microsoft Office or OpenOffice or Adobe Acrobat or anything else installed on your server. You want it to stay clean and not junked up. But you also want to get your work done. That’s where portable applications can help. A portable application is an application that works without being installed. All you have to do is copy the executable to a location and double-click it to run it. If you have the portable application’s exe on a USB stick, you don’t even have to copy it, you can run it from the USB stick. It’s great for consultants.

A portable application is not the same thing as a virtualized application. A virtualized application doesn’t touch the local filesystem or registry. A portable application might. It could leave traces behind in the user profile. I don’t mean to imply that’s a problem, but I want you to be informed.

A good way to get started with portable applications is to go to http://portableapps.com and download the Suite Standard to your personal machine – not your production server. Notice that it includes a portable version of OpenOffice and the Sumatra PDF reader.

When you first run the full suite from PortableApps.com, you’ll see something like this in the lower right corner:

image

You do not have to use the PortableApps launcher to use the portable applications. Just navigate to the folder where the executable of interest is and double-click it. Or create a shortcut to the executable. I don’t use the launcher. I suppose if you install it to a memory stick, you could find it convenient to use when plugging the stick into a machine. Internet Explorer 9 does not like the individual downloads from the PortableApps site. I use Firefox to get additional apps. Other portable applications I download from the PortableApps site include:

7-Zip (it also handles rar files; allows you to read iso files)
Database Browser
Eraser (securely deletes files)
FileZilla (if you need to ftp a large file, you should use this instead of a browser)
Foxit Reader (I prefer it to the Sumatra PDF Reader; if actually installing a PDF reader is an option, consider Nitro PDF Reader)
Frhed (free hex editor)
Google Chrome
gVim (vi editor)
InfraRecorder (create iso file)
Java (OpenOffice needs this if there isn’t a JRE installed)
Java launcher
PuTTY
Skype
md5 checksum
WinMerge

There are a few additional applications I add from other sources:

CPU-Z and Hardware Monitor from www.cpuid.com, see my post for more information

Orca from http://www.softpedia.com/get/Authoring-tools/Setup-creators/Orca.shtml, see my post for more information

SQLjobvis from http://www.sqlsoft.co.uk/sqljobvis.php to see a graphical view of SQL Server Agent jobs, see my post for more information

WinSQL from http://www.synametrics.com/SynametricsWebApp/Download.do?ProgName=WinSQL, see my post for more information

Not all freeware applications are free for commercial use, so be careful and read the license agreements.

If you create a shortcut to a portable executable and put the shortcut in your %APPDATA%\Microsoft\Windows\SendTo folder, you’ll be able to invoke the portable app by selecting it from the Send to submenu of the right-click menu option in Windows Explorer. (Hint: paste %APPDATA%\Microsoft\Windows\SendTo into the address bar in Windows Explorer.)

image

Published Friday, April 01, 2011 12:03 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jason said:

Great information!  I'd like to add two apps that I always keep in my SQL Server toolkit.  

eseutil - an Exchange file copy program that moves large files without soaking up memory (unbuffered large file copy).  I had our Exchange admin email me eseutil.exe and ese.dll.

Process Explorer a beefed up Task manager that lets you dig into the internals of running programs.  http://technet.microsoft.com/en-us/sysinternals/bb896653

April 19, 2011 10:32 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement