412 errors on Xero API using Classic ASP

I started to see errors on our calls to the Xero API, with status 412 returned. The initial clue was in this Xero Community post.

On windows 7 machines or older machines, you may need to install a fix to ensure that windows can handle TLS 1.1 or 1.2. I usually recommend this update as a first step as it mitigates not knowing how the underlying code, wrapper may work.

This led me to a post about TLS versions, with a useful piece of code to check the TLS version of your server:

Set objHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.6.0")
objHttp.open "GET", "https://howsmyssl.com/a/check", False
Response.Write objHttp.responseText 
Set objHttp = Nothing 

Running this I found the particular VM was showing TLS1.0, which is not good. I recalled I’ve seen something similar a few weeks ago on another project which was calling the Vimeo API, which now requires TLS1.2 or above.

This particular server is 2008 R2, and there’s a patch to upgrade your TLS to 1.2.

I used the Windows Catalog Update link in the article plus used the Registry EasyFix to sort the registry key out.

Now the TLS test code above is returning

"tls_version":"TLS 1.2"

And my calls to the Xero API are working.

Finally solved the mystery of why isNumeric doesn’t work how you might think

In Classic ASP there is the function isNumeric, which one uses to test if a value is a number. So for example:

Response.write IsNumeric("21")  // true
Response.write IsNumeric("David")  // false

Seems straightforward so far. Then you try this:

Response.write IsNumeric("15447D")  // true

Every so often this little quirk would popup. This morning it’s been driving me mad because I had a set of string values that were mixtures of alphanumeric, or numeric values. I wanted to discriminate between numbers and not-numbers and store the values in different columns in a database table. Using isNumeric though kept throwing errors – it tested 15447D as being a number, and of course once I tried to insert that into an integer column an error was thrown.

Finally figured how what’s happening after a little digging online. isNumeric also considers hex values to be valid numbers. 15447D is hex for 1393789. Now the lights go on – only wondered about this for 20 years.

Apparently this is also the case for octal values.

So a custom function along these lines is the answer:

Function myIsNumeric(ByVal Value)
	Dim regEx
	Set regEx = New RegExp
	regEx.pattern = "^(0|[1-9][0-9]*)$"
	myIsNumeric = Regex.Test(Value)
End Function

Configure FTPS on IIS7 for SSL file transfers

I’ll presume you already have an SSL certificate installed to your IIS web server and thus have your web site running with https://www.domain.com. This post is about how to enable SSL for your FTP connections to the server.

Set the SSL cert for your FTP server and site

  • Go to IIS Manager
  • Click the Server name at top of left tree and open FTP SSL Settings icon
  • In the SSL Certificate drop down select your certificate
  • Check the Require SSL connections
  • Expand the list of Sites in the left tree and select your FTP site
  • Open FTP SSL Settings icon
  • In the SSL Certificate drop down select your certificate
  • Check the Require SSL connections

Yes I know we’ve done this in two places, but it does appear to be necessary.

Set the Bindings for the FTP Site

  • Go to IIS Manager
  • Expand the list of Sites in the left tree and select your FTP site
  • Over on the right click Bindings…
  • Click Add button, add Type = FTP, IP Address = All Unassigned, Port = 21 and Host Name = http://www.domain.com (as in whatever your domain is)

The following ports work is because you need to specify and open the data ports.

Configure Firewall Ports

  • Go to IIS Manager
  • Click the Server name at top of left tree and open FTP Firewall Support icon
  • The Data Channel Port Range may be set already, if not make it ‘7000-7003’
  • Set the External IP Address of Firewall to the public IP of your server
  • Go to Windows Firewall
  • Add a new Inbound Rule, named something like FTPS for IIS
  • On the Protocols and Ports tab select Protocol Type = TCP, and Local Port = Specific Ports, with the range 7000-7003
  • Leave Remote Port  = All Ports

This allowed me to FTP into my server, with FTPS selected. Don’t forget because you have set the Bindings for the FTP site to your domain, the user name in your FTP program needs to be domain name and user name separated by a pipe, for example http://www.domain.com|userName.


Using grunt-prompt with grunt-git

Screen Shot 2014-10-10 at 12.15.11 AM

I’ve become a big fan of Grunt in recent months, and now have all my current main projects set up with Grunt for linting, minifying, revving, pushing to Git etc. It means I have a nice smooth build and deployment pathway, and saves me heaps of time.

I’m using grunt-git to push to Github, and started with a default commit message along the lines:

gitcommit: {
   'src-master': {
       options: {
          verbose: true,
          message: 'Commit src-master <%= grunt.template.today("isoDateTime") %> \n',
          noVerify: true,
          noStatus: false,
       files: {
          src: ['.']

But that’s not much use because it means I’m not adding my own commit message describing whatever it is I’ve just changed. Nor do I have any outlet for frustration and thus no chance of making it onto commitlogsfromlastnight.com.

So then I came across grunt-prompt, which does just what you expect, it creates a prompt as part of your tasks so you can input something. Then everything came together nicely:

// prompt for a commit message
 prompt: {
    commit: {
       options: {
          questions: [{
                     config: 'gitmessage',
                     type: 'input',
                     message: 'Commit Message'
// commit changes to github
gitcommit: {
   'src-master': {
       options: {
          verbose: true,
          message: '<%=grunt.config("gitmessage")%>',
          noVerify: true,
          noStatus: false,
       files: {
          src: ['.']


Git – ignore files starting with period / full stop / dot

I use Git on my Mac, but some repositories are on the Windows drive of my Mac. Mac creates system files in Windows for each of the files, for example default.html will wind up with another file in the same folder called .default.html. This is all a bit annoying in Git which picks up on these system files, even though I have no interest in then.

But you can exclude them. Edit your .gitignore file and add:


Parse JSON with TSQL in a SQL Server Stored Procedure


I’m a big fan of JSON as a data exchange format, it’s lightweight, and every API worth playing with is usually JSON-friendly. So in idle curiosity I wondered if it was possible to parse JSON with TSQL, and guess what, someone else has come up with a way to do so.

A hat tip to Phil Factor (and RedGate for their great Simple Talk blog) for his article Consuming JSON Strings in SQL Server. Phil not only has example code to parse JSON into a table format, he shows you how to go the other way – create JSON from table output.

SQL Server does handle XML, but let’s be honest, it’s a pain in rear to use. But it’s all we’ve got natively with SQL, as Phil says:

TSQL isn’t really designed for doing complex string parsing, particularly where strings represent nested data structures such as XML, JSON, YAML, or XHTML.

You can do it but it is not a pretty sight; but why would you ever want to do it anyway? Surely, if anything was meant for the ‘application layer’ in C# or VB.net, then this is it. ‘Oh yes’, will chime in the application thought police, ‘this is far better done in the application or with a CLR.’ Not necessarily.

Sometimes, you just need to do something inappropriate in TSQL.

I gave the code a quick try parsing JSON into a table and it worked great.


How to locate text string in a SQL server object – stored procedure, view or function

Sometimes it’s handy to just conduct a simple search of your SQL server objects to locate a text string.

FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%text%'

FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'isView') = 1
AND definition LIKE '%text%'

SELECT OBJECT_NAME(object_id),definition
FROM sys.sql_modules
(OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1)
AND definition LIKE '%text%'

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyName%'

An item with the same key has already been added (SQL Report Services)

Screen Shot 2014-02-26 at 7.42.34 AM


I was creating a new report in SQL Reporting Services, I pasted in the SQL query for the report and up popped the message “An item with the same key has already been added”.

Turns out I had two columns with the same name – which as always with these error messages, begs the question, why not just say so!

Copy Azure SQL Database to local machine (SQL 2008 R2)

You would have thought in these days of point and click a simple task like copying a database from one place to another would be a walk in the park. So when I wanted to take a copy of a SQL database from Azure and move it to my local machine, I thought it surely would be straightforward. It’s not. Here’s what I came up with:

In Azure make sure you have a Storage account set up, with a container inside. My container is called ‘files’.


In Azure go to your database. Click the Export button down the bottom.


A new window will pop up. The file name will probably be pre-filled. Select your blob storage account (if you only have one then it’s already selected). Select a container (mine is ‘files’). Enter your SQL Server log in name and password – these are the credentials you set up when you added the database. Click OK and the export should start – you’ll see the status down the bottom.


Once the export is done, you can click to view your Storage container, and you should see the export file there.


The trick now is to get the file from Azure to your local machine. I use Azure Storage Explorer, it’s a free download. Download the file from your container.


If you have SQL Server 2012, then you are home free, because you can import the BACPAC file in SQL Management Studio. Right click on the Databases folder under a server in the Object Explorer and choosing “Import Data-tier Application.

If like me you are on SQL Server 2008 R2, you need some help.

Create a new blank database on your local machine.

Go to  http://msdn.microsoft.com/en-us/jj650014 and install the SQL Server Data Tools for Visual Studio 2010. After install go and find SqlPackage.exe, I located it in C:\Program Files\Microsoft SQL Server\110\DAC\bin. Open up a DOS prompt and use the following command:

SqlPackage.exe /a:Import /sf:c:\Desktop\FILENAME.bacpac /tdn:DATABASENAME /tsn:SQLSERVERNAME
  • FILENAME.bapac is the file you downloaded from Azure, whereever it is located on your machine.
  • DATABASENAME is the name of the new blank database you created a minute ago.
  • SQLSERVERNAME is the name of the SQL server on your local machine.

I followed these steps, albeit for a fairly small file, and it worked perfectly.

Puzzle for the day, why one particular Glyphicon Halflings icon is acting weird


Today’s conundrum, why does one particular Glyphicon Halflings icon display differently between browsers? I have several other icons and they are all correctly displaying black and white. Yet this one icon – calendar – on the same page, same code, displays differently across my browsers.

On FireFox and Safari it displays in colour:

Screen Shot 2014-01-29 at 9.03.15 AM

On IE it displays in black and white:

Screen Shot 2014-01-29 at 9.04.37 AM

On Chrome it doesn’t display at all:

Screen Shot 2014-01-29 at 9.04.12 AM

The markup is straightforward:

Screen Shot 2014-01-29 at 9.11.55 AM

Finally tracked down this answer on Stackoverflow:

There is most likely an issue with inclusion of Glyphicons on BootstrapCDN 3.0.0 version. When 3.0 was released the glyphicons were in a separate repo, and then combined into the Bootstrap baseline again later around 3.0.1.

I updated the CSS CDN include from:

<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">


<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">

and my calendar icon is now correctly displaying as black and white across all browsers.