August 2008 - Posts

When is a number not a number?

 Bob Dorr has posted about conversion issues with isnumeric

http://blogs.msdn.com/psssql/archive/2008/08/25/isnumeric-returns-1-true-and-i-expected-0-false.aspx?CommentPosted=true#commentmessage

This reminds me of a difficult "bug" in an application whereby a lookup actually entered a numeric value into a table from a text list. For the user the list was too long - why some developers think a drop down list of over a thousand entries is a "good idea" I never know - anyway the users had discovered that you could bypass the drop down to get an unknown by entering a full stop ( decimal point )  Now if you type

Select isnumeric('.');  this will return true/1 ,  however  select convert(int,'.');  will fail.

Sadly for the application in question the data entry didn't fail it was a process which occurred somewhat later - it took some considerable time to sort it out. Personally I wouldn't allow users to enter commas and full stops but it's worth noting that the logic behind a function doesn't always follow what we might consider "commonsense decisions" as amany a bug will prove.

 

Posted by GrumpyOldDBA with 4 comment(s)
Filed under:

" Seeing " data.

Sometimes the visualisation of data can be difficult, consider displaying user activity for a multinational application.
This is exactly what I wanted to do in such a manner that I could see who and when was using the system, however with over fifty countries at a five minute granularity this means a grid of 288 x 50 cells and even if you could get it on screen the actual information would be difficult to take in.
My solution was to use SSRS and an area graph, the link below shows a sanitised version of the report but it gives a flavour of the real thing, the only thing not changed are the times and colours.

http://www.grumpyolddba.co.uk/monitoring/MERKATActivity.htm

The actual report has real country names, links to tabular reports and each country has a click through to show a line graph for that country's activity for the day. The report also takes a parameter to allow historical reporting and can show activity for the current day in near real time , sadly it can't forward predict as a colleague suggested it might!

It's actually a very easy way to see if you have available windows of low activity for maintenance and it's easy for anyone who might be interested in system use. It can also answer questions such as how many users from Xyz were in today and overall system peaks are easy to view.

There's no code as the data comes from an application. This is part of a suite of reports for tracking server and application performance, I'll publish more at another time.

Posted by GrumpyOldDBA with 1 comment(s)