David Eedle

SQL Server 2008 Database Mail Failed

Had one of those 'how weird' moments today. We seemed to not be sending emails from a web site I look after. We're using SQL Server 2008, and the terrificly handy Database Mail service.

I originally set this up, using instructions I found in a well explained blog post 'Installing and Configuring Windows Server 2008 SMTP'.

When I checked the Database Mail logs I found a bunch of errors saying:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-03-25T18:13:13). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 127.0.0.1:25).

Which seemed odd. So I tried a simple Telnet localhost 25, and received connection refused. Which made me wonder where the SMTP service had gone.

I checked the Features list in Server Manager, and the SMTP service was showing - so I definitely wasn't hallucinating that I had originally installed the service (it's not installed by default).

I tried sending a Test Mail (right click on Database Mail in SQL Server Management Studio, then used the following to check the send log and the error log:

SELECT * FROM msdb.dbo.sysmail_allitems WHERE mailitem_id = [test mail id]
  
SELECT * FROM msdb.dbo.sysmail_event_log WHERE mailitem_id = [test mail id]

And it showed as failed, with the same error message I'd been seeing in the logs. Then I opened IIS6.0 - the SMTP service was not showing.

Checking the logs showed the emails starting to error on a specific day. Which makes me wonder whether someone else on the team has been messing around the on the server.

I decided to try and reinstall, so I used Remove Feature in Server Manager to remove the SMTP service, then Add Feature to reinstall, and then again followed the instructions in the above blog post - which is really just about ensuring you've set Relaying for the localhost, so the SMTP service will accept emails from the SQL Database Mail service.

A test email went through fine. And trying functions on the web application showed emails sending fine as well.

So I have a little bit of a mystery. SQL Database Mail was all fully installed and operational. But the SMTP service seemed to have disappeared, or somehow been changed. Think I had better ask around to see who has been doing what.

 

 

 

 

The microsoft.ace.oledb.12.0 provider is not registered on the local machine

Every so often I experience problems importing data from Office files (usually XLS) into SQL. Sometimes everything works fine, others not.  I've always suspected it's because I don't actually have Office installed on my Windows part of my Mac, although I do have SQL, SQL Express, and an old copy of Access.

This morning I was importing an XLS with a couple of thousand lines, into SQL 2008. And up popped this message:

The microsoft.ace.oledb.12.0 provider is not registered on the local machine

A quick Google and I found this forum which leads to this Microsoft download page for the 2007 Office System Driver: Data Connectivity Components. I downloaded and installed and now SQL likes my XLS file.

 

Mobile device IP not necessarily a geolocator

Following on from my post yesterday 'Calculate distance between web site visitor IP and list of locations' I've come across an interesting revelation.

The client was off playing with my new page that establishes the web site visitor's geolocation from their IP number. And he complained because nothing was right.

Turns out he was browsing the page via iPhone. So I set up a test with my own iPhone, and hey presto, the IP geolocator system database thinks my phone is in Sydney. Which is definitely incorrect because it's sitting on my desk in front of me here in Melbourne. I proved the point by switching the iPhone's wifi back on, and now it correctly reports its location as Melbourne.

What I presume is that mobile carriers assign IP numbers to mobile devices from a central place - eg the IP range is probably designated to their head office or datacentre. And thus, like me, your phone can think it's in Sydney when in reality it's in Melbourne.

Of course iPhone Apps can access a phone's coordinates in a different way via the cellular network - hence some applications popping up a message asking you to agree to them accessing your location. That's how cool gadgets like Four Square work.

But it you rely on IP/coordinate lookups then the answer will be wrong.

For the moment I think we'll disable the fancy stuff on the web site for people browsing with mobile devices, but now I'm wondering how we can deal with this revelation.

 

Calculate distance between web site visitor IP and list of locations

I've been having fun this morning writing code to calculate the distance between a web site visitor's location, and a number of fixed locations, using the visitor's IP number.

I have a list of events in different geographic locations. The idea is that when someone arrives at the web site we show them information about the event that is closest to them.

I've used a couple of tools to make this work.

First up I found the GeoLiteCity database from MaxMind. This is a free database that can calculate the longitude and latitude of an IP number. They say they cover the whole of the IPv4 system (and a fair chunk of the IPv6).

MaxMind does have a web service available (paid) but you can download the GeoLiteCity database and query it directly - they have a good set of example APIs. I'm using the COM object on a Windows server.

The database is a binary file that you reference via file path. They do supply a CSV to import into SQL but they make the point the binary is seriously faster, so unless you need additional access to the data, it's not worth the loss of speed. If you also use the COM just take note of their instruction to rename the database file from GeoLiteCity.dat to GeoIPCity.dat. I have the file sitting just above the www root folder so it's not visible to the world.

Finding the site visitor's latitude and longitude is a doddle:

Dim geoip,visitorLat,visitorLong

set geoip = Server.CreateObject("GeoIPCOMEx.GeoIPEx")

geoip.set_db_path("D:\inetpub\GeoIPDb\")

geoip.find_by_addr(request.ServerVariables("REMOTE_ADDR"))

visitorLat=geoip.latitude

visitorLong=geoip.longitude

I then hunted around online and found the longitute and latitude of the cities where my events are occuring - we're not looking for 'to the metre' resolution here, just a quick guesstimate of which event and city is closest. When I get some more time I'll sit down and figure out the coordinates of the actual venue for each event.

So now I need a way to calculate the distance between the visitor's IP, and all of the events. Along the way I found this really useful page from Moveable Type  'Calculate distance, bearing and more between Latitude/Longitude points'. It's a great introduction to all the maths needed.

But another Google search found a forum post with a SQL stored procedure that calculates the distance between two coordinates, and it works well.

CREATE FUNCTION dbo.UDF_Distance
( 
  @FromLat float, @FromLong float, @ToLat float, @ToLong float
) 
RETURNS FLOAT 
AS 
BEGIN
	-------------------------------------------------------------------------------------------------
	-- Function Name	: UDF_Distance
	-- Description		: Returns distance in KM between two points on earth
	-- Created by		: David Eedle
	-- Created on		: 24 Jan 2011
	-- Source:			: http://www.dbnewsgroups.net/sqlprogramming/t19131-sql-distance-between-latitude-longitude.aspx
	-------------------------------------------------------------------------------------------------
	declare @X float
	SET @X =
	   Sin(Radians(@FromLat))
	* Sin(Radians(@ToLat))
	+ Cos(Radians(@FromLat))
	* Cos(Radians(@ToLat))
	* Cos(Radians(@ToLong)-Radians(@FromLong))

	SET @X = Acos(@X)
	RETURN 1.852 * 60.0 * Degrees(@X)
END

GO 
/*
select dbo.uf_Distance (41.63,-87.73,41.7,-88.07)
go

*/

I used the Moveable Type page to double check the results and the procedure yields numbers within a few kilometres of MT's answers over distances of 12,000km. If I was a maths whiz I'd probably now sit down for a day and figure out the inconsistency but I'm not trying to send a rocket to the moon so we'll leave that for the experts.

Finally it was just a question of writing a quick query that pulls the coordinates of the events out, along with a calculation of the distance between the visitor and each event along the lines of:

SELECT cityId,cityName,cityState,cityDate,
CAST((select dbo.UDF_Distance ("&visitorLat&","&visitorLong&",V_ListCities.cityLat,V_ListCities.cityLong)) AS int) distanceKM 
FROM V_ListCities ORDER BY (select dbo.UDF_Distance ("&visitorLat&","&visitorLong&",V_ListCities.cityLat,V_ListCities.cityLong))

 

 

 

 

 

 

 

 

ISAPI ReWrite Rules on Individual Web Sites

ISAPI ReWrite has been driving me up the wall for the last day or two. I have Version 3 (fully registered) installed on a web server, which has half a dozen individual web sites up and running. I wanted to implement some rewrite rules for one of the web sites, so I used the ISAPI ReWrite Manager interface to edit the .htaccess file in the root of that specific web site and add a rule along the lines of:

RewriteRule /register/([^/]+)/ /register/register.asp\?eventId=$1 [I,L]

I already have this web site running on my development server, and I know this rule works.

The rules didn't work. I moved them up to the top level of the Manager, which edits the httpd.conf file in the installation directory for ISAPI ReWrite. And the rules worked.

I turned on logging by adding this to the httpd.conf file:

#enabling rewrite.log
RewriteLogLevel 9
#enabling error.log
LogLevel debug

The logging clearly showed the .htaccess file being accessed and the rules in that file being run through:

Htaccess process request d:\inetpub\wwwroot\mydomain.com\.htaccess
applying pattern '/register/([^/]+)/' to uri 'register/My-Event-Name/'

But no dice in the browser, just file not found errors. So the rule is being processed, but the resulting URL clearly is not right.

I read through the troubleshooting information on the Helicon web site. Which is when this phrase caught my eye:

Also remember that rewriting base inside .htaccess files by default is different from httpd.conf. ISAPI_Rewrite automatically strips the local directory prefix from the path when in .htaccess or <Directory> section and applies rules only to the remainder. So if your rules start with a '/' character, this should be removed in .htaccess file in the root folder of web site.

This is the first time I've used the registered version of ISAPI ReWrite, in the past I've only used the Lite version, mostly because I didn't need to bring rules down to the level of the individual web site (on this project I will need to because multiple sites will need the same URL/folder combinations).

So the idea that there is a subtle but key difference in the way you construct the rewrite rules between .htaccess at the individual site level, and the top level httpd.conf file has never come across my radar before.

I amended the rule to:

RewriteRule register/([^/]+)/ /register/register.asp\?eventId=$1 [I,L]

Notice there is no forward slash in front of the register/ name. And the redirect worked completely fine.

 

 

 

 

I love descriptive, intuitive error messages

If you see an error like the following in your web code and SQL Server, what do you naturally think is the problem:

Microsoft Cursor Engine error '80040e21'
Multiple-step operation generated errors. Check each status value.

Well, looks like a database error, given the 'cursor engine' phrase. But a 'multi-step operation'?

I've now discovered that it appears almost always to be you trying to insert a string that's too long for the field.

I love descriptive, intuitive error messages.

Solving 'Login timeout expired' Problem on Windows Server 2008

I've been helping set up a new web site, we have two servers, all very off the shelf, both running Windows Server 2008, one with SQL Server 2008 as the database sever, the other acting as the web server.

The two boxes sit behind a firewall with only the necessary ports open (eg 80, 21, 443 etc). Windows FIrewall is turned on for both boxes, and on the SQL box we have port 1433 open but restricted to only the internal IP of the web server, so the web application can connect to the database.

The application is talking to the database using a standard connection string along the lines of:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

We loaded up the site without major problems, but then started to notice this error occuring at seemingly random moments:

Microsoft OLE DB Provider for SQL Server error '80004005'
Login timeout expired

There seemed no rhyme nor reason, one minute you are browsing pages, the next up pops this error, then you refresh the page and all was well.

Clearly it's a connection error between the web application and the SQL server. I dug into the Windows and SQL logs everywhere trying to correlate entries with the time the error occured, but to no avail.

There's a fair amount online about this error, although it's often tied to simple connectivity issues, such as incorrect IP numbers, server address, firewall settings and the like.

Finally I stumbled over this page 'Troubleshooting: Timeout Expired' which gave the clue, and particularly:

'Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.'

We don't have the SQL Server Browser service running - it's not started by default. So I simply added the port number to the connection string and, fingers crossed, everything's been fine since, eg:

Provider=sqloledb; Network Library=DBMSSOCN; Data Source=[sql server ip],[ip]; Initial Catalog=[db name]; User ID=[db user]; Password=[password];

Actually I normally DO have the port number in the connection string - because I fell into the habit for a long time of running SQL on a non-standard port to avoid nasties with 1433 worms and the like in situations where I wasn't so confident about the firewalling, or where we needed to leave the SQL Server exposed with IP or tunnel protection.

 

 

Scheduled Tasks Failing to Fire VBS Scripts

I have a Windows Server 2003 box with a bunch of scheduled tasks that fire VBS scripts through the day, handling various maintenance, data import and other jobs for a client's web site.

Over the past few days I noticed the jobs were not firing, and after a fair bit of research I finally found a solution.

The first clue came when I tried to call the VBS scripts directly in the command prompt, and received the following error message:

cscript error loading your settings failed access is denied

Which seemed a strange error given I was logged in as a user with Administrator permissions.

After a little more poking around I noticed two more oddities:

1. The Start Menu didn't look right - for example, the Accessories folder didn't contain many of the shortcuts I'd grown used to

2. The Documents and Settings folder contained duplicate user profile folders, for example:

- [username]
- [username].[machinename]
- [username].[machinename].000
- [username].[machinename].001
- [username].[machinename].002

all the way through to

- [username].[machinename].012

I also noticed in my command prompt window that I appeared to be logged in under the '012' profile.

I found this article that pointed the way:

http://support.microsoft.com/?kbid=314045

I searched the Registry for the ProfileImagePath key and found it was set to:

%SystemDrive%\Documents and Settings\[username].[machinename].002

I changed it to:

%SystemDrive%\Documents and Settings\[username]

I logged off, and logged back on again, and this time I was logged in with the [username].[machinename] profile - confirmed by opening the command prompt:

C:\Douments and Settings \[username].[machinename]>

I then tried to call the VBS scripts in the command prompt and they worked fine. Then via Scheduled Jobs window. Again all ok. And my Start Menu is back to normal as well.

As a sidebar I've noticed that the duplicate user profile folders were all created over the space of a single day - Christmas Day. Which is strange because I know that on Xmas Day I wasn't hunched over a computer RDCing into web servers, rather I was enjoying a day off with my family. The duplicate folders were all empty (eg they didn't have the normal complement of folders such as My Documents, Desktop, Favourites and so forth). All except the last one, 012. But that was the one I was logged into. So somehow Windows reacted to something and created the duplicates - but presumably whatever the 'something' was didn't constitute a login otherwise it would have populated the folders with the subfolders needed for the profile.

I've Google a little but can't immediately find anything that explains a big bunch of duplicate user profile folders turning up that are all empty.

All a bit strange, and I wish I had a more concrete explanation for how this all came about.

 

 

 

 

 

Telstra Probably Just Cost Me $1000

In May this year we bought our eldest daughter a Nokia N97 phone at the local Telstra store, to replace her well-worn Hiptop Slide. The Nokia cost $36 a month to pay off the handset over 2 years, along with the monthly phone plan.

The Nokia proved a dog of a phone, slow, unreliable, prone to crashes. We went back to the Telstra store a couple of times to ask them to check the phone, and were always told "there's nothing wrong", until after a couple of months they then informed us the problem was the SIM card. The salesperson back in May had simply moved the SIM from the Hiptop to the Nokia. The Hiptop was a 2G device, the Nokia a 3G. Apparently they should have burnt a new SIM. So we dutifully trooped back in and had a new SIM burnt, that we were assured would solved all our problems.

Of course, nothing really improved. Finally I caved in and this week bought a new iPhone4 to replace the Nokia. I've had my iPhone4 for several weeks and been extremely happy, and our daughter was also comfortable that it would be a viable replacement.

We also had noticed that her phone bills were getting expensive so today I rang Telstra and asked to move up a plan level on the phone.

Amazingly, the Telstra guy on the phone this morning told me that there were still old 2G phone codes on the account, left over from the Hiptop, and thus 3G service would never have worked properly.

So it's entirely conceivable, and the Telstra guy concurred, that all our problems with the Nokia were probably a result of Telstra not changing the codes on the account when we switched from a 2G to a 3G device.

Which means I probably just spent $1000 this week for absolutely no reason. Plus I'm stuck with another year of making the remainder of the $36 payments on the Nokia handset, which is not being used.

Is it any wonder, with such excreable sevice that we all hate Telstra. I'm going to be paying the price for this fuck up for a long time.

 

Check the page set up on your browser to make sure it prints text as black - NOT!

Here's an easy, blatant example of a web site with a built in problem, that the owner acknowledges but who has decided to make it the user's problem.

I picked up a parking ticket the other day and just went to pay the fine. It was with the City of Port Phillip, which like a bunch of councils around Melbourne uses the maxi.com.au online payment system (I remember Maxi launching years ago amidst much hullabaloo, ideas like terminals all over the city etc etc. Nowadays it still exists but only for these small irritating payments).

Anyhow, I dutifully pumped my Visa card number in, and back came the receipt page, which is a classic fail. Here's the page:

The text I've highlighted says:

(If you print your receipt, check the page set up on your browser to make sure it prints text as black.)

So of course, red rag to a bull, I clicked the print button, yup, an entire page of dark blue background plus the white text.

They KNOW it's a problem, but they put it onto the user to solve the problem. Hands up everyone who knows how to change the page set up on their browser so that it would reverse or otherwise deal with this ludicrous situation? I do this for a living and I'd probably fiddle for 5 minutes.

Yet the issue is so incredibly simple to fix - just change the styling on the page to a white background and dark text. In fact, I'm positive the last time I used Maxi this issue didn't exist - which means they've updated the site and created a new problem.