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:
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:
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)
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)
gVim (vi editor)
InfraRecorder (create iso file)
Java (OpenOffice needs this if there isn’t a JRE installed)
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.)