Jul 19

SSMS: Keyboard shortcut for "change connection"

When deploying database code, I’m running each SQL script on several servers, and having to click the “change connection” button dozens of times in SSMS is very frustrating. I submitted this as a bug to Microsoft, and Bill Ramos offered the following method for creating a keyboard shortcut:

  1. Open a Query Editor window connected to your favorite server.
  2. Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.
  3. Right click on Change Connection button again and then change the command name “C&hange Connection…” to “Chan&ge Connection…”. This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.
  4. Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both “Image and Text”. This exposes the Alt-G command to the Query Editor.
  5. You can now close the Customize dialog and use Alt-G as your hot key!

I love this shortcut. I use it dozens of times daily. It’s still too much overhead for deploying, though, so I either use Query Analyzer (open a window, drag all the files into it, and they all open with a connection to that same server without prompting) or an application I wrote to accept a deploy list, get the files from our source control provider, and put them all into one script with appropriate USE [database] statements.

Apr 20

Orphaned Logins

When you transfer SQL Server logins to a new server with DTS or create them on the new server manually, the SIDs don’t match up with the server that you are importing your database backups from. To avoid this, Microsoft has created handy scripts (for transferring from 2000 to 2005, for transferring between 2005 instances). If you’ve already botched it up, you can find the orphaned logins like so:

EXEC sp_change_users_login ‘Report’

And you can fix them one at a time like so:

EXEC sp_change_users_login ‘Update_One’, ‘username’, ‘login’

which links users in databases with the username “username” to SQL Server logins with the name “login”.

Mar 20

Identifying Service Packs

Don’t use SQL Server without service packs! We upgraded one node of our sql cluster to a faster computer, and when the network admin went to install SQL Server SP3 on it, it claimed that it didn’t need to do anything since it was a part of a cluster. When people started using the site, the CPU on the database shot up to 100% and we started mysterious getting SQL Server internal errors.

We eventually determined that even though Enterprise Manager was claiming that SP3 was installed, it wasn’t. This site and this KB article describe how to more accurately figure out service-package.

Today’s lessons are 1) don’t believe Microsoft and 2) don’t use any Microsoft product until they’ve patched it at least once!

Jun 02

Access Cheat Sheet

These are from ye olde Access 97 days and may no longer apply.

COUNT DISTINCT isn’t supported

To get around it: SELECT COUNT(1) FROM (SELECT DISTINCT column FROM table)

Joins

When using multiple ANSI joins, you must surround each set in parentheses.

SELECT topic.name
FROM (users INNER JOIN messages ON users.id = messages.idUser)
INNER JOIN topics ON messages.idTopic = topics.id

Object Names

If there are queer characters or reserved words in your table/query names, you can reference them in SQL statements by surrounding the name with square brackets, just like SQL Server.

SELECT email, [date] FROM [List Members]

Dates

Surround dates in SQL with #.

SELECT * FROM users WHERE joined > #3/4/2002#

 

Apr 16

Return the folder of the current ASP file

ASPToday ASP Tip of Day for 17 April 2001, by Ian Vink

Return the folder of the current ASP file being accessed.

set fs = server.createObject("scripting.fileSystemObject")

Full_ASP_FILE_PATH = server.mapPath(request.serverVariables("PATH_INFO"))

PATH_ONLY = fs.getParentFolderName(Full_ASP_FILE_PATH)

response.write "This ASP page is being run in the folder: " & PATH_ONLY