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,
          ignoreEmpty:true
       },
       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,
          ignoreEmpty:true
       },
       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:

.*
!/.gitignore

Parse JSON with TSQL in a SQL Server Stored Procedure

json_logo-555px

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.

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

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

SELECT OBJECT_NAME(object_id),definition
FROM sys.sql_modules
WHERE
(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!