How to get Open Live Writer working with Blogger

Wow – I didn’t blog at all in 2016!  While I’m certain that falls squarely on me, perhaps a small part of it is related to a bug connecting to Blogger (formerly Blogspot) blogs in my favorite blogging software – Open Live Writer.  This blog post explains the workaround and how I figured it out.  Hopefully this issue will get patched by the Open Live Writer team and the workaround won’t be needed in the future.

The Issue

Trying to connect to a Google Blogger blog with Open Live Writer v0.6.0.0 doesn’t work (this is the latest version as of this post).  Clicking the Sign In button brings the user to this URL:

https://accounts.google.com/o/oauth2/auth?access_type=offline&response_type=code&client_id=PASTE_YOUR_CLIENT_ID_HERE&redirect_uri=http:%2F%2Flocalhost:59530%2Fauthorize%2F&scope=https:%2F%2Fwww.googleapis.com%2Fauth%2Fblogger%20https:%2F%2Fpicasaweb.google.com%2Fdata

Notice the client_id value “PASTE_YOUR_CLIENT_ID_HERE” in that URL!  That’s definitely not right, and it breaks the attempt to authenticate to Google via OAuth.

The Workaround

I tried installing several different versions of Open Live Writer via Chocolatey.  I found that the issue is also present in versions v0.5.1.4, and v0.5.1.3, but version v0.5.1.2 works.  The best way to get Open Live Writer working with Blogger is to install Chocolatey, and then run this command:

choco install openlivewriter -version 0.5.1.2 -force
 
That will install the correct version of Open Live Writer that we need to authenticate.  However, you can’t run Open Live Writer from the main shortcut.  Open Live Writer uses Squirrel which is a tool that assists with automatically updating software when it is launched.  Almost all the time this is great, but for our purposes it will not help since we want to run the exact version we’ve just installed.
 
So run this, and you should be able to authenticate with Google’s Blogger service via OAuth:
 
%localappdata%\OpenLiveWriter\app-0.5.1.2\OpenLiveWriter.exe
 
Once you’ve authenticated to Blogger, you can quit out and then use the Start Menu shortcut.  This will run the Squirrel updater, and your system will be updated to the latest version which is v0.6.0.0 as of this writing.
 
How I Figured This Out
 
It was a bit of brute force.  First I cloned the repository for Open Live Writer from here.  Then, I opened up the solution in Visual Studio 2015 Community Edition and searched for the string “PASTE_YOUR_CLIENT_ID_HERE” in all files.  This led me to the src/managed/OpenLiveWriter.BlogClient/Clients/GoogleBloggerv3Secrets.json file.  When I noticed that the file was called “Secrets” it made me think that perhaps this was a compilation/deployment issue.  I looked into the .gitignore file at the root of the repo, and sure enough the GoogleBloggerv3Secrets.json file was specifically ignored.
 
Therefore, my theory was that the person who packaged up Open Live Writer versions v0.6.0.0 didn’t have the Secrets file updated on their machine.  This leads to the idea that probably someone in the past DID have it updated, so I tried successive earlier and earlier versions until I hit on v0.5.1.2 working (I would have tried a bisect approach but there were only four historical releases).
 
Why was this broken?
 
It’s good that the Open Live Writer team moved the Google Blogger secrets out of their public repo.  However, this makes it difficult for individuals to build a complete version of the app.  A good solution could be to use a CI/CD build solution – I wonder if the Microsoft team might look into Visual Studio Team Services?  I suspect they could get a good deal from that vendor.
 
I wrote up the bug in Issue #561.

There is still an issue with using images and Open Live Writer with a Blogger account due to the Picassa shutdown.  I will post again if I find a workaround for that issue.

Fixing Truncation Issues with T-SQL Flex

If you’ve worked with SQL Server for any length of time, you’ve run into this error:

Msg 8152, Level 16, State 14, Procedure MyProcedure, Line 162
String or binary data would be truncated.

While the problem is simple enough - you’re trying to put a big value in a small field - it’s a maddening error because SQL Server doesn’t give you a hint as to which field or value is causing the issue.

It’s not even so bad when you’re copying data from a well-known source (like many fields from a single table), but sometmes you’re copying data from an arbitrary query.  Having to look up the data sizes from across your database (or worse - figuring out the data types from calculated fields or functions/views/etc.) can be terribly time-consuming.

I wrote a tool called T-SQL Flex that can help.  It’s a plugin for SQL Server Management Studio that generates scripts for any arbitrary SQL query.  It has many uses, but one I like is helping to fix “String or binary data would be truncated” problems.

Assumptions:

1. You’re working in a dev environment and have good + tested backups
2. You understand the risks of “following instructions on some guy’s blog”.
3. You have T-SQL Flex installed in your SSMS.
4. You have a query that is giving “String or binary data would be truncated” errors.

General process for fixing a truncation issue with T-SQL Flex:

1. Find the statement in your query that is causing the error.  The easiest way is to comment out everything but the first statement (Highlight, then CTRL+K, then CTRL+C).  Then run and see if it bombs.  If not, uncomment the next statement (Highlight, then CTRL+K, then CTRL+U) and repeat.  Eventually you should get the error.  Please note that you will have to be smart about things like branching logic (IF/ELSE), changes being made to permanent tables, and any sort of transaction work (BEGIN TRAN/COMMIT/ROLLBACK etc.) – that’s all out of scope for this blog post. If your procedure is really big, you may wish to search by halves.
2. Once you’ve found the offending statement, do two things:
   • Add a new SELECT TOP 0 statement to query the schema of the destination table.  If your INSERT statement had field names, use them in the SELECT statement.  If not, it should be SELECT TOP 0 * FROM (whatever the destination is).  This should work for regular tables as well as #temp tables and @table variables.
   • Comment out the INSERT INTO part of the offending statement, and leave the SELECT part there.

   For example, if the offending statement that you found in step 1 was:

   INSERT INTO #MyTempTable (field1, field2, field3)
   SELECT productId, price, [description]
   FROM #MyOtherTempTable
   WHERE productId = @productId;
   --rest of query is still commented out.

   You should convert it to this:

   SELECT TOP 0 field1, field2, field3 FROM #MyTempTable;
   --INSERT INTO #MyTempTable (field1, field2, field3)
   SELECT productId, price, [description]
   FROM #MyOtherTempTable
   WHERE productId = @productId;
   --rest of query is still commented out.

3. Now you should be ready to run your statement in T-SQL Flex.  With the above modifications, the output from T-SQL Flex after clicking Run ‘n’ Rollback should be something like this:

   CREATE TABLE #Result1 (
     field1 INT NOT NULL,
     field2 DECIMAL(18,5) NOT NULL,
     field3 NVARCHAR(100) NOT NULL
   );
   
   CREATE TABLE #Result2 (
     productId INT NOT NULL,
     price DECIMAL(18,5) NOT NULL,
     [description] NVARCHAR(200) NOT NULL
   );

   Notice how in the above results, field3 in the first table (our destination) has a NVARCHAR(100) column, but the corresponding field in the second table (our data source) is NVARCHAR(200).  In this scenario, that is likely our problem – a value that fits in a NVARCHAR(200) field may not fit in a NVARCHAR(100).  You still have to compare the type of each column in the source to each column in the destination, but T-SQL Flex does all the leg work for you.  If there are a lot of fields, it shouldn’t be too bad to paste the temp table definitions side-by-side in Excel.

4. Once you’ve identified the mismatched fields, you should either fix the capacity of the too-small fields in the destination, or else explicitly truncate the source data (such as specifying LEFT([description],100)).  Put the statement back to how it was, and now it should no longer raise the truncation error.
5. Once that statement is fixed, you may wish to try the whole query again to see if the main issue is resolved (uncomment the rest of the query), or else continue “stepping through” by going back to step 1.

Good luck!

Talking TypeScript on the .NET Rocks! Podcast

I appeared on the .NET Rocks podcast show #1149 this week.  I had a blast talking about TypeScript, the new ES6 features coming in TypeScript 1.5, and grunt-ts, our TypeScript compiler task for Grunt.

I wanted to add a few things.

Firstly, here are a number of additional links that are relevant to our discussion:

My Pluralsight Course: Practical TypeScript Migration
Great review of what it's been like working with TypeScript over 24 months:  http://tedpatrick.com/2014/10/16/24-months-with-typescript/
Destructuring in ES6: https://hacks.mozilla.org/2015/05/es6-in-depth-destructuring/
Get started with grunt-ts if you've never used Grunt: https://github.com/TypeStrong/grunt-ts/blob/master/docs/DetailedGettingStartedInstructions.md
Node Inspector - debug Node.js code in the Chrome Dev Tools - works great with TypeScript also: https://github.com/node-inspector/node-inspector
Download TypeScript 1.5 beta for Visual Studio 2013 (transpiles much of ES6): https://visualstudiogallery.msdn.microsoft.com/107f89a0-a542-4264-b0a9-eb91037cf7af
Download TypeScript 1.4 for Visual Studio 2013 (current official release with minimal ES6 transpilation support): https://visualstudiogallery.msdn.microsoft.com/2d42d8dc-e085-45eb-a30b-3f7d50d55304

Around the 10 minute mark, I said that you should put type annotations on function calls.  That would be awful – I meant to say you should put type annotations on function declarations.

Around the 20 minute mark, I said that you can use ES6 features today with TypeScript 1.5 because it offers some transpilation features.  Richard then said transpilation was interesting because (paraphrasing) "when browsers come out with finished ES6 implementations, your page should go faster", and I sort of disagreed.  The reason is that transpiling actually changes your code from using one pattern to code using a different pattern.  Richard's statement was correct with regards to polyfills which is something that a dynamic language like JavaScript makes possible.  Developers who create polyfills attempt to implement future features in legacy environments.  For example, in ES6, there is a new `hypot` method on the JavaScript Math object, so Math.hypot(3,4) === 5.  A polyfill could be created for that feature to work in ES5 fairly easily.  A well-written polyfill would check to see if there was already a hypot property on the Math object; if so, it would just fall back to the native implementation which would (theoretically) be faster (though for something simple like a hypotenuse calculation it'd likely be no different).

Features that there's no way to polyfill require transpilation.  For example: class MyClass { }.  There is no (usable) class keyword in ES5, and so there is no way to create something in ES5 at runtime that would allow that code to run and do something reasonable; the syntax just doesn't exist.  That's why the transpilers have to change that code into a function declaration.  Since it becomes a function declaration, there's no way to natively optimize it as a class (beyond general optimizations for constructor function patterns that the engines would already have).  So for transpiled features, if you want to let the engine act natively, you'd have to dynamically serve the right code to the right engines - or (more practically) just serve the lowest common denominator like ES5 to all, which generally works great in today's browsers.

// Here's a cool destructuring example:
// This is valid TypeScript (in 1.5) and JavaScript (ES6)
// TypeScript will compile it down to work correctly with ES5
//  by creating a "normal" parameter in the signature of doSomething
//  and add code in the body to pull out its baz property.

var myObject = {
  foo: 1,
  bar: false,
  baz: "hello"
};

function doSomething({ baz }) {
  console.log(baz);
}

doSomething(myObject); // hello

I said something silly around the 36 minute mark: "convert the inside of a tight loop first"; that's a very bad idea and doesn't even really make sense. :-(  What I should have said is "convert something central to your application first - like a common utilities library or a configuration file".  The reason for converting "from the inside out" as I recommend is that each new thing you convert will infer many types from the things that have already been typed.  As you grow the scope of TypeScript in your application, you will begin to find that you need to annotate less and less (other than function signatures) - and the things you do need to annotate may even be errors.  I feel that annotating function signatures is something that ultimately helps you write better code since it leads to compiler-enforced documentation, and it's usually necessary if you've activated --noImplicitAny.

Around the 51 minute mark, I was talking about how TypeScript doesn't help people enough with regards to componentizing projects of medium complexity and I misspoke a few times by using the word Solution (meaning a Visual Studio Solution) when I meant to say Project (meaning a Visual Studio Project).  TypeScript works great within a single Visual Studio project - the trouble comes when you want to use TypeScript across projects (like in a large/complex solution).  There are many hacky workarounds to this, but I still don't know of a great "this is the obviously right way to do it" story; I hope that the TypeScript team and the community will eventually put together a great solution for this.

I had a great time appearing on the podcast – thanks for the opportunity!

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:

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”.

Then click “Format…” and select the appropriate formatting (such as setting a fill color).
Then click OK and OK.

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”.

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.

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.

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!

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.

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.

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.

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!

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
   
   
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.
   
   
   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 

Getting Started with the Modern.ie VMs on VirtualBox with IIS Express

Microsoft has made pre-packaged Virtual Machine images available for purposes of testing different versions of IE via the modern.ie web site.  This blog post describes how you can get these VM images loaded into the free VirtualBox software and talking to an IIS Express web site running on your local PC.

Warning: These instructions were tested on VirtualBox 4.3.12, the latest version as of May 22, 2014.  By default, this is set up to make your VM get Internet access via your PC using a virtual NAT.  The VM will be assigned an IP address by a built-in DHCP server that runs on the VirtualBox Host-Only NIC.  Unless you start changing things, this should all remain on your local PC and your VM won’t accidentally get an IP address from your network’s real DHCP server – but your mileage may vary and you should confirm that!  The author of these instructions makes no warranty that these settings may not cause a problem on your particular network, so you should proceed with caution and at your own risk.  Do not follow these instructions if you are not comfortable with the concepts of IP addresses, network cards, virtualization, or web servers – or what havoc a rogue DHCP server could cause.

1. Download and install Virtual Box: http://www.virtualbox.org (yes I know it’s Oracle, but it is gratis and it works even on some older Core2 processors that Hyper-V doesn’t support)
• Be sure that Virtual Box is fully installed before starting the next step because installing the drivers will temporarily disable your physical NIC.
2. Open your Network control panel and ensure that the “VirtualBox Host-Only Network” adapter is set to use the static IP 192.168.56.1 and the Subnet Mask is 255.255.255.0.  You can leave the DNS server blank.
3. Download the appropriate VM image from the modern.ie web site
• Open this page: http://modern.ie/en-us/virtualization-tools#downloads
• Select "VirtualBox on Windows".
• Download part1.exe and all of the .rar files for the particular VM you want.  Save these to a temporary "downloads" folder.
  
• When all of the files are downloaded, run the EXE and allow it to extract the .OVA file.  Once you have the .OVA file, you can then delete the corresponding .EXE and .RAR files.
• Move the .OVA file to a “special place”.  This is the file you have to run to set up a new VM of that variety.
• Run the .OVA file by double-clicking on it.  You will get an "import" screen in Virtual Box.
• Change the "Virtual Disk Image" path to point to the appropriate place where you want the virtual hard drive to live for the VM you’re creating.
• Check the box to reinitialize the MAC addresses.
• Click “Import”
  
4. When the import is complete, go into the settings for the VM.  On the Network tab, confirm that network Adapter 1 is attached to NAT.  Click OK.
5. Boot the VM by selecting it and clicking the Start button in the button bar.
6. Note: the login information on all of the modern.ie VMs is IEUser and Passw0rd!
7. When the VM boots up, open a command line in the VM and run ipconfig /all
• The first VM you launch should have an IPv4 address like 10.0.2.15 which was served by the virtual DHCP server on 10.0.2.2.
8. The next step assumes that you already have a working site in IIS Express.  If not, use Visual Studio or some other tool to create one.
9. In your “Documents” folder on the host PC, open the IISExpress\config\applicationhost.config file.
• Find the web site that you want to set up for the VM to use and add an extra entry to the bindings tag to allow IIS Express to listen on the internal VirtualBox IP address (in my example, the site was set up on port 59707 – you can use whatever port you wish) and save the config file:
  
10. Start the site.
11. Try to access the site by visiting http://192.168.56.1:59707 (or whatever port number that you used) – it should hopefully work as expected.

Have fun getting your new site working with old IE!

From Zero to Pull Request with Git on Windows

I wrote a guide about using Git and GitHub for "Windows people".

This is the guide I wish was available when I got started with Git last year.

The guide takes you through everything you need to know to get started with Git and GitHub, and only requires a Windows PC where you have admin rights to complete.  You can probably get through the whole thing in an evening or two.

This guide is unique because:

• It is specifically for developers who use Windows
• It only describes how to contribute to an existing project
• It prioritizes the GitHub for Windows UI when the command-line is not required
• It is not embarrassed to give step-by-step directions or definitions of basic terms
• It prioritizes "the simple way for the common circumstance" over "every possible way for every possible circumstance"

Check out the guide here:

https://github.com/nycdotnet/FromZeroToPullRequestWindows/blob/master/FromZeroToPullRequestWindows.md

If you like it, please hit me up on Twitter and let me know: @nycdotnet

Rounding versus truncating

Be careful when implicitly converting data types in T-SQL.  Directly assigning 1.5 (either as a FLOAT or a NUMERIC) to an INT value in SQL Server may truncate the value to 1 rather than rounding it up as you might expect.  Explicitly calling ROUND( ,0) as part of the assignment will round 1.5 up to 2.

Here's an example:

DECLARE @Value INT;

DECLARE @NumericValue NUMERIC(10,1) = 1.5;

DECLARE @FloatValue FLOAT(53) = 1.5;

SET @Value = @NumericValue;  --Not calling ROUND()

SELECT @Value; --Returns 1

SET @Value = @FloatValue; --Not calling ROUND()

SELECT @Value; --Returns 1

SET @Value = ROUND(@NumericValue,0);

SELECT @Value; --Returns 2

SET @Value = ROUND(@FloatValue,0);

SELECT @Value; --Returns 2

Query to Find "Update" Dependencies on a Table or View (SQL Server 2012)

The built-in dependency finder in SQL Server Management Studio does not provide the ability to distinguish between dependencies that are read-only and dependencies that are read-write.  However, SQL Server 2012 does make this information available via the management views.

 

The query below will return all of the objects that reference the table (or view) identified by @SchemaName and @ObjectName where the referencing object can modify the data in the table (or view)... specifically this means an INSERT, UPDATE, or DELETE operation.

 

Note that dependency tracking in SQL Server is not perfect because of the possibility of using dynamic SQL, but this will hopefully be useful information.

DECLARE @SchemaName NVARCHAR(128) = 'dbo';

DECLARE @ObjectName NVARCHAR(128) = 'MyTableName';

SELECT re.referencing_schema_name, re.referencing_entity_name,

  ref.*

FROM sys.dm_sql_referencing_entities(@SchemaName + '.' + @ObjectName,'OBJECT') re

CROSS APPLY sys.dm_sql_referenced_entities(referencing_schema_name + '.' +

    referencing_entity_name,'OBJECT') ref

WHERE ref.is_updated = 1

  AND ref.referenced_entity_name = @ObjectName

  AND (ref.referenced_schema_name IS NULL OR ref.referenced_schema_name = @SchemaName);

 

This post was edited to fix code formatting and to specify SQL Server 2012 as this sadly does not appear to be supported in SQL Server 2008 or before.

 

References

MSDN: sys.dm_sql_referenced_entities (Transact-SQL)

MSDN sys.dm_sql_referencing_entities (Transact-SQL)

How To Make Your DLL Into A NuGet Package for a Private NuGet Feed

This is my reinterpretation of the documentation on NuGet.org, modified to remove the public publishing steps, and to add local publishing steps and a source control reminder.  This guide is intended to help enable the benefits of NuGet for proprietary code such as business-specific DLLs that are not fit for public distribution.  There are plenty of details, options, features, and gotchas with NuGet that are not covered here, but this should hopefully be enough to help you get started.

Step 1 is to set up a private NuGet feed.  You only need to do this once for your organization.

• You can find out how to do that using the official documentation here: Creating a private NuGet feed
• You should create a file share that you have at least "Change" rights on that points to the NuGet packages folder on the server where you configure the feed.

Step 2 is to write a cool or useful library (such as a DLL) in Visual Studio.

• Be sure to fill-in the project properties under Assembly Information, including the version number if you haven't.

Step 3 is to get the NuGet.exe file which is not included by default even if you have NuGet Package Explorer working in Visual Studio.

• Download NuGet.exe bootstrapper here: http://nuget.codeplex.com/releases/view/58939 .  This is a "bootstrapper only" which means that the first time you run it, it will download the real NuGet.exe program and overwrite itself.  Run it once from a command-line (just NuGet.exe) and you'll have the latest version (should be 600 KB or more).

Step 4 is to set up NuGet in your solution.

• Create a folder under your solution called "NuGet" and copy NuGet.exe there.
• Open the Package Manager Console window inside Visual Studio (under View... Other Windows... if you don't see it).
• At the PM> prompt, type dir and hit Enter.  You should see the files and folders in your solution as well as the new NuGet folder that you just created.
• At the PM> prompt, type .\NuGet\NuGet.exe   You should see the help for NuGet.exe printed in the console.  If so, PowerShell is working and NuGet is in the correct place in the subfolder under your solution.
• At the PM> prompt, type .\NuGet\nuget.exe spec MyProjectFolderName\MyProjectName.csproj  This assumes that there is a C# project called MyProjectName in a subfolder under MyProjectFolderName under the current directory.  If so, NuGet will create a .nuspec file under the project subfolder.  If you click the Show All Files button in Visual Studio, you should see it in the Solution Explorer.
• Right-click the file and choose "Include in Project".  With the file selected in the Solution Explorer, hit F4 and ensure the Build Action is "None" and the Copy to Output Directory is set to "Do not copy".
• Open the file and update all fields that you feel are relevant.  In particular, you should change the package ID field to be the name that you want the project to show as in the NuGet Package Explorer and get rid of any backslashes in the name.  You can remove the children of the dependencies attribute if the package stands alone.
• Check-in your changes to Source Control!  This should include the .nuspec file, and ideally the path and binary to NuGet.exe (if your organization permits keeping binaries in Source Control).

Step 5 is to package up your project.

• At the PM> prompt, type .\NuGet\nuget.exe pack MyProjectFolderName\MyProjectName.csproj  This will run NuGet to package up the built project to a .nupkg file.  This is the command that you will run every time you wish to update your NuGet package for publication.
• You can confirm that this file was successfully created by unzipping it and checking to see if your DLL is inside.

Step 6 is to copy your .nupkg file to the shared folder that you set up in step 1.

• You can do this with PowerShell in the Package Manager Console via COPY *.nupkg \\MyNuGetServerName\MyNuGetPathName$

Step 7 is to begin using your freshly created NuGet package in your team's other projects!

Performing an INSERT from a PowerShell script

This code demonstrates how to do an INSERT into SQL Server from a PowerShell script using an ADO.NET command object with strongly-typed parameters.  This script issues a DIR *.* command and inserts the results to the database.  While this example is not particularly useful, it is hopefully very simple and understandable to explain the concepts.

In general, parameterized SQL queries (like the below) perform better and are less vulnerable to SQL injection attacks than SQL queries created using string concatenation*.

The script code displayed in the blog post performs several INSERT statements using a row-by-row method.  As a bonus, I have uploaded a version of this script to SkyDrive that uses two flavors of Table-Valued parameters which should give you better performance for big datasets.

 

Download Scripts:

SkyDrive link to Row by Row only Script (has less commentary than this blog post, but code is identical)

SkyDrive link to Row by Row and Table Valued Parameter Script

Note: You have to edit the above files in a text editor to set the SQL Server name and DB name.  Once you edit the file it is no longer considered "remote" so you can run the file as long as the PowerShell ExecutionPolicy is set to RemoteSigned.

 

To try this script, first create a table in a junk database on a test SQL server:

CREATE TABLE FilesInFolder (

       ID INT IDENTITY(1,1) NOT NULL,

       TheFileName NVARCHAR(260) NOT NULL,

       FileLength BIGINT NOT NULL,

       LastModified DATETIME2 NOT NULL,

       PRIMARY KEY (ID)

);

 

Then paste the following code into a .PS1 file (you have to edit the $DBServer and $DBName values first) and run it using Powershell:

 

function Do-FilesInsertRowByRow ([Data.SqlClient.SqlConnection] $OpenSQLConnection) {

 

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand

    $sqlCommand.Connection = $sqlConnection

 

    # This SQL query will insert 1 row based on the parameters, and then will return the ID

    # field of the row that was inserted.

    $sqlCommand.CommandText = "SET NOCOUNT ON; " +

        "INSERT INTO dbo.FilesInFolder (TheFileName,FileLength,LastModified) " +

        "VALUES (@TheFileName,@FileLength,@LastModified); " +

        "SELECT SCOPE_IDENTITY() as [InsertedID]; "

   

    # I am adding the parameters without values outside the loop.  This means that inside the

    #  loop all I have to do is assign the values and say "run" - much less work than setting

    #  up everything from scratch in each iteration.

    # Also notice the doubled-up (()) - this is how you create a new object and then

    #  immediately pass it as a function parameter in PowerShell.

    # Next, the class names in square brackets are .NET types.  Powershell assumes System.,

    #  so you can omit it.  The double-colon is how you reference enumeration members.

    # Finally, I am piping the output of these calls to Out-Null since otherwise PowerShell

    #  would output the properties of the command object to the console on each call since

    #  there is no assignment made to a variable or another pipeline destination.

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@TheFileName",[Data.SQLDBType]::NVarChar, 260))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FileLength",[Data.SQLDBType]::BigInt))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastModified",[Data.SQLDBType]::DateTime2))) | Out-Null

   

    # I love how I can foreach over a call like "dir *.*" in PowerShell!!

    foreach ($file in dir *.*) {

        # Here we set the values of the pre-existing parameters based on the $file iterator

        $sqlCommand.Parameters[0].Value = $file.FullName

        $sqlCommand.Parameters[1].Value = $file.Length

        $sqlCommand.Parameters[2].Value = $file.LastWriteTime

 

        # Run the query and get the scope ID back into $InsertedID

        $InsertedID = $sqlCommand.ExecuteScalar()

        # Write to the console.

        "Inserted row ID $InsertedID for file " + $file.Name

    }

 

}

 

 

# Open SQL connection (you have to change these variables)

$DBServer = "MySQLServerName\MyInstanceName"

$DBName = "MyJunkDBName"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"

$sqlConnection.Open()

 

# Quit if the SQL connection didn't open properly.

if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {

    "Connection to DB is not open."

    Exit

}

 

# Call the function that does the inserts.

Do-FilesInsertRowByRow ($sqlConnection)

 

# Close the connection.

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

    $sqlConnection.Close()

}

 

 

 

*By "in general", I mean that this is true most of the time, and in the few times when it is not true, the use of parameters is unlikely to leave you worse off than not using parameters.  Plainly, if you are using string concatenation to write SQL statements in any language, you are doing it very wrong and potentially leaving your database open for attack and exploitation.

 

I'm a member of the SkyDrive Insiders program; you can learn more about this program here.  I published a quick-start guide to using SkyDrive at work here.  If you have any questions about SkyDrive, please ask me!