Wednesday, January 14, 2015

Excel 2013 Conditional Formatting for Columns

If you have two columns in an Excel sheet, and you want to conditionally format fields in the second column when the values are not equal, do this:
image
Our starting point – we want Grape to be highlighted because it is not the same as Cherry.
Select all of column B by clicking the header.  On the Home tab, click Conditional Formatting… New Rule.
Choose “Format only cells that contain”, and “Cell Value” “not equal to” “=A1”.  Note that putting the “=” is very important because otherwise Excel will think you’re comparing it to the string literal “A1”.
image
Then click “Format…” and select the appropriate formatting (such as setting a fill color).
Then click OK and OK.
image
Note: If you don’t want the header to be highlighted, you can apply the conditional formatting to only the cells with actual data, but you will have to adjust the corresponding comparison row in the formula.  In our example, we might apply the conditional formatting to cells B$2$:B$5$ (which starts at row 2), but we’d have to set the formula to “not equal to” “=A2” (also starts at row 2).  If you want to select down to the end of the sheet, your selection formula would be something like =$B$2:$B$1048576 on the Excel 2007+ “big grid”.

Thursday, January 8, 2015

Enabling Portable Git in Node.js command prompt on Windows

GitHub for Windows doesn't put Git in the PATH by default.  If you'd like your Node.js command prompt to have the git command available by default, simply edit your nodevars.bat file.  By default, this is in C:\Program Files\nodejs\.  You will have to run your text editor in an administrative context for this to work.

Replace this line in your nodevars.bat file:
set PATH=%APPDATA%\npm;%~dp0;%PATH%

With these two lines:

for /F %%A in ('"dir /s /b /OD %userprofile%\appdata\local\github\portableGit_*"') do set gitPath=%%A\bin
set PATH=%APPDATA%\npm;%~dp0;%PATH%;%gitPath%

See this gist for an easier-to-copy version:


https://gist.github.com/nycdotnet/f7d7b8de0c55b7081cb0#file-new-code


That will set a variable called %gitPath% with the location of git.exe, and then append it at the end of your path in the Node.js command prompt.  Because of the /OD switch, it will use the version of Portable Git whose folder has the latest modified date.  If Portable Git is not found, you will get a harmless extra ; in the PATH.

Tuesday, January 6, 2015

Using an alternate TypeScript compiler inside Visual Studio

TypeScript team member Daniel Rosenwasser provided instructions for replacing the TypeScript compiler and language service used by Visual Studio here:

https://github.com/Microsoft/TypeScript/issues/1110#issuecomment-62451204

He also described how to update the lib.d.ts file later in the same issue:

https://github.com/Microsoft/TypeScript/issues/1110#issuecomment-65865932

These instructions are not guaranteed to work, so be sure to back up your original files.

Monday, December 1, 2014

Gmail and Match.com are Conspiring to Breakup My Marriage

My wife and I are happily married.  Both of us were surprised the other day when she started getting Match.com personals results sent to her GMail account!  She was a bit freaked out that it was some creepy malicious person, but it seems that it was just some end-user carelessness (by someone we don’t know) combined with inconsistent email address parsing implementations between two big companies.  Here's what happened.

An "interesting" feature of consumer GMail is that it ignores dots in email addresses.  So if you send an email to homer.j.simpson@gmail.com, or hom.er.j.sim.ps.on@gmail.com, your message will be delivered to the same GMail inbox.  (documented here: https://support.google.com/mail/answer/10313?hl=en)  However, any messages that you send are always delivered as the "official" email address - whatever you actually typed in when you signed-up for GMail.

It seems that someone else with a similar email address to my wife signed-up for Match.com and entered an incorrect email account.  No big deal – I'm sure this happens all the time – but what's bad is that they used an email address that was the same as my wife's but with no dots.  So it's the “same” for incoming mail, but “different” than my wife’s email address when she sends mail.

We reported the problem, but sadly the robo-support at Match.com has been unhelpful so far.  When she tries to explain what is going on, she’s getting back messages from support saying that she’s not the owner of the account (because the email address is different), so there’s nothing that can be done.  But of course she is the owner of the account based on the way GMail handles dots – it’s just not an exact string match.  I can't imagine this is the first time this has ever happened, and you'd think a company as big as Match.com would account for an oddity like this for a big email provider like GMail.

My wife and I met on Match.com many years ago and we were very happy with their service at that time.  If a human from Match.com spots this, please take a closer look at Incident:141126-001205.  (I guess this was the 1205th ticket from November 26…)  We’d love it if you could close out the account that was recently mistakenly registered.  I’m sure that other person has opened a different account already and you could probably verify that by IP.

Email address as a unique identifier – what could possibly go wrong?

Let’s invent our own email standards – because reasons!

Friday, November 28, 2014

Setting up WebStorm to debug grunt with TypeScript

It’s possible to use the WebStorm editor to debug grunt tasks if you are using TypeScript and generating .map files.  Assuming that grunt is already launching from the command line, here’s how to step into the debugger:
  • Open your project’s folder in WebStorm.
  • Click Run… Edit Configurations…
  • Add a new configuration for Node.js (the green + sign)
  • Name it “Debug Grunt”
  • Set the JavaScript File to C:\Users\YOUR_USER_ID_GOES_HERE\AppData\Roaming\npm\node_modules\grunt-cli\bin\grunt (make sure you update this path with your user ID)
  • Set the application parameters to the grunt task you wish to debug, for example dev or just leave blank if it is default.
a screenshot of the Run... Edit Configuration window after following this procedure.

Now you should be able to hit breakpoints when running the Debug Grunt task.  WebStorm is also smart enough to notice if your JavaScript has a map file that points to a TypeScript file; if it does, you can even set breakpoints in the TypeScript code.

Thanks to Diego and everyone else who answered this question on Stack Overflow.

Monday, September 8, 2014

New Add-on for SSMS: T-SQL Flex

Do you ever copy query results out of SQL Server Management Studio, paste them into your text editor, and then perform gold-medal find + replace gymnastics to create T-SQL scripts?

Do you ever need to copy a small amount of data from one SQL server to another, but don’t want to set up a linked server or take the time to create an SSIS package?

Do you ever need to create simple Excel sheets from SQL Server queries – preferably one tab per query result and with valid formatting (such as showing dates correctly and not truncating leading zeros)?

Have you ever wanted to quickly script a temp table that has the exact schema (including nullability and precision) of a query, stored procedure, or table-valued function?

If you answered yes to any of the above questions, there is a new add-on available for SQL Server Management Studio that you may like called T-SQL Flex.

TSqlFlexScriptToInserts

T-SQL Flex is a free, open-source add-on for SQL Server Management Studio that does only two things:

  1. It scripts SQL query results to INSERT statements.
  2. It scripts SQL query results to Excel-compatible spreadsheets (specifically, XML Spreadsheet 2003 format).

Because T-SQL Flex does only these two things, it is very easy to use.  Simply paste in your query and click the “Run ‘n’ Rollback” button.  T-SQL Flex will create an ADO.NET transaction, run your query, collect the results, and then roll back the transaction.

You can check out the latest release here:

https://github.com/nycdotnet/TSqlFlex/releases

If you like T-SQL Flex, please let Steve know on Twitter:

https://twitter.com/nycdotnet

Thanks to the team at Red-Gate that created the SIP framework used by T-SQL Flex.

Happy scripting!

Tuesday, July 8, 2014

How to Generate a Fiddler SAZ File

Fiddler is a free program that will log HTTP and HTTPS requests on Windows.  These instructions demonstrate how to use Fiddler to create an archive file (.SAZ) of these requests for troubleshooting purposes using the full version of Fiddler.  As an alternative, there is a simplified version of Fiddler available that only supports capturing here: http://fiddlercap.com.

Warning: Distributing a SAZ file is potentially a massive security risk, especially if HTTPS decryption was enabled during the traffic capture, because any secrets that are sent over the wire (including even passwords or session cookies) will be included in the file.  You can use Fiddler to AES-encrypt the SAZ file using a password via the save dialog, but you must still treat the exported SAZ file (and password) with extreme care!!

  1. If the web site that you need to profile uses HTTPS, please consider getting a dedicated VM set up for installing Fiddler.  This will make trusting the Fiddler certificate (essentially a self-inflicted man-in-the-middle attack) less problematic.  There are instructions on how to do that here if you’re going to be testing with old IE versions.
  2. Go to http://fiddler2.com and download Fiddler.  You can use either the .NET 2 or .NET 4 version depending on which version of the framework you have installed.  Install it accepting the defaults and run it.
  3. Do not perform this step if the site that you need to profile is HTTP-only.  If the site that you need to profile uses HTTPS, open Tools… Fiddler Options… HTTPS tab.  Enable “Decrypt HTTPS traffic”.  When the SCARY TEXT AHEAD dialog comes up, click “Yes” to trust the Fiddler Root certificate, and then click “Yes” again to acknowledge that you want to allow man in the middle attacks on this computer (which is how Fiddler can decrypt your HTTPS traffic (good), and how malicious actors who also have a copy of the Fiddler certificate could too (bad)).  More information is available here: http://docs.telerik.com/fiddler/configure-fiddler/tasks/DecryptHTTPS

    Screenshot of Fiddler Options dialog
  4. If you want to use a browser other than Internet Explorer to generate the SAZ file, you may need to follow additional instructions here: http://docs.telerik.com/fiddler/configure-fiddler/tasks/ConfigureBrowsers 
  5. Minimize Fiddler, and now browse to the site you want to test.  Tab back to Fiddler and you should see the HTTP and HTTPS calls that were made while the capture was running.  For example, here is a visit to the www.google.com homepage.  To see the session details, select the Inspectors tab, and click the “Raw” on both the top and bottom panels – this allows you to see the exact text that was requested from the server by the browser and the browser’s exact response.

    Screenshot of Fiddler showing the left and right hand sides, plus the top and bottom request and response panels.
    In this screenshot, you can see the list of sessions that happened as a result of accessing www.google.com.  When I clicked on a session at random, I was able to see the details on the right-hand pane when I selected Inspectors and then Raw on the top and bottom section.
  6. To export the SAZ file, select the sessions that are interesting in the left-hand pane and click File… Save… Selected Sessions… in ArchiveZip…  Select “Password Protected SAZ” in the “Save as type” field.  “Save the SAZ file in a secure location.  It’s best to exclude things like login pages or other forms with secrets if they’re not relevant to the issue you’re troubleshooting because exported SAZ file will contain any secrets that were transmitted (including HTTPS transmitted items if HTTPS Decryption is enabled).  Choose a strong password.  You will need to share this password with whomever you wish to open the file.
  7. Securely provide the SAZ file and password to whomever needs it.  Again - this file contains EVERYTHING that was sent on the computer via HTTP (and potentially HTTPS) including potentially passwords, session cookies, and other secrets that can be read in clear text if you have the SAZ password.  It is critically important to care for this file and the password in a secure manner!! (Can you tell that this is important?)
  8. When the other party has the SAZ file on their computer, they can open it by opening Fiddler and clicking File… Load Archive…
  9. If you enabled HTTPS traffic decryption, your computer is still at risk until you perform the following two steps:
    1. Disable HTTPS decryption by opening Tools… Fiddler Options… HTTPS tab, and unchecking Decrypt HTTPS traffic.
    2. Also, click the “Remove Interception Certificates” button to delete the Fiddler certificates.

      See: http://stackoverflow.com/questions/16827901/how-do-you-remove-the-root-ca-certificate-that-fiddler-installs