Category: Windows Tips

How to Run SQL Server Management Studio as a Different Windows User

If you’re using Windows Authentication for your SQL Server database you’ll need to run SQL Server Management Studio (SSMS) as that Windows account to test queries.  SSMS normally runs as the user logged in to Windows, but there is a simple way to run it as the database access account.

Just shift-right-click on a shortcut and select “Run as a different user“.  (Windows 7)

Shift-Right-Click a shortcut to run the program as a different Windows User.
Shift-Right-Click a shortcut to run the program as a different Windows User. (Windows 7)

You’ll be presented with a Windows login prompt.  Log in and SSMS will open.  Note that the connection dialog has the new user in the Windows Authentication area

Connect to Server Dialog
Connect to Server Dialog

Note: You’re now running the program as a different user, so it probably won’t have access to the files you normally use.  My fix was to give the account read/write to the folder where I hold my SQL scripts.

This tip will work for other programs too.  Let me know how you used it in the comments below.

Open a CMD Window From Anywhere!

In Windows 7, hold down shift and right click a folder to see some additional menu items.  You’ll notice a few extra options like “Open command window here” and “Copy as path”.

Shift + Right Click
Shift + Right Click

Open command window here

Often when browsing folders in Windows Explorer you will want to open a command shell, using the directory being viewed as the current directory:

cmd.exe opened to the selected folder
cmd.exe opened to the selected folder

Then you can do all sorts of cool things, like list out all the files in the directory using dir.

List a folder contents using DIR
List a folder contents using DIR

Copy as path

The “copy as path” option is also really handy on files, especially when uploading files to websites.  Now you can browse to an upload form, hit browse, and just paste in your copied path.

Copy as path, then paste into an upload form
Copy as path, then paste into an upload form

 

Command Line Display of a Folder Tree

If you have ever needed to print out a folder hierarchy in Windows this is for you. Go to the command line (Start > Run > cmd), traverse to the folder you want to list out (ie: cd c:/reallysweetfolder/) and type tree.
Your view will look something like this:

├───Browsers
│   ├───Minefield
│   ├───Mozilla Firefox
│   ├───Mozilla Firefox 2 Beta 2
│   └───Other
│       ├───Mozilla Firefox
│       ├───Netscape4
│       └───Opera

Now, of course you’d want to throw in some options (display files in the folders and list it out in ASCII characters) and export this to a text file right? Well, just type:

tree /f /a >c:/myFolderList.txt

Here’s the help for reference:

Graphically displays the folder structure of a drive or path.
TREE [drive:][path] [/F] [/A]

/F Display the names of the files in each folder.
/A Use ASCII instead of extended characters.

Merge Worksheets in Excel

Lately there has been a lot of merging of worksheets in Excel at my work. Now, I don’t know why, but it seems that everyone is working in 10 or 15 different spreadsheets and keep on asking me to merge them. By merging they usually mean combining the columns based on a single column in one sheet.

For example, say I have two spreadsheets. One with Products and some information like color and UPC number. The other with products and some other information like length, width, and weight. The column that both sheets have in common is the product_id column. Now I want all that information in ONE spreadsheet.

So, the way to do this is with a simple Excel function called VLOOKUP.

Here’s the syntax (or how to use it) from Microsoft:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value
The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array
Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index_num
The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

  • Less than 1, VLOOKUP returns the #VALUE! error value.
  • Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

  • If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
  • If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

So now, for the example above, we need to make sure that the product_id columns are both in the first column in each worksheet – “A”. The VLOOKUP function needs that to be able to work correctly. We’ll then start off with:

=VLOOKUP(A2,

This means that we’re looking at the first column, 2nd row (A2).

Now we’ll put in the range that we are looking into. The second sheet will have the product_id in column “A”, and the height in column “B”. There 1000 lines in this sheet (including the header on row 1). So our code now looks like:

=VLOOKUP(A2, Sheet2!A2:B1000,

The neat thing here is that you can reference worksheets that aren’t even in the same file.

We’re going to modify that a bit so that the Table_Array is fixed for when we drag this function down through the cells later on. The way you “fix” the range is to add dollar signs ($) before each of the cell references in the function:

=VLOOKUP(A2, Sheet2!$A$2:$B$1000,

The third and forth things are pretty simple so we’ll combine them into one step here. The 2 means that we want to return the value for the 2nd column where the first columns (product_id) match up. The 0 (or false) means that we want to have the function find an EXACT match for the product_id. Keep in mind that EXACT means EXACT. If there are spaces at the end of the product_id in one worksheet and not the other the fields will NOT MATCH EXACTLY.

So here’s the final VLOOKUP code:

=VLOOKUP(A2, Sheet2!$A$2:$B$1000, 2, FALSE)

Remember that these are references and sometimes you’ll need to copy and paste-special with just the values to keep the references static. If not you just have to make sure whatever you are referencing is always available to this spreadsheet function.