Tuesday, March 3, 2009

Specify the default, stupid!

I've been struggling all week to connect to a Microsoft SQL server. It was SQL Express. I'm using "ADODB.Connection" COM object from PHP.

(The reason to use that is you can specify the 3rd param as CP_UTF8 (without quotes) and then it will convert from UTF-8 to the UCS-2 that SQL Server works in, all behind the scenes.)

First, if it says it cannot connect due to there being no server: you have to enable TCP/IP connections in SQL Server. They are off by default.

That got us to the next error, which said "接続が正しくありません。" which translates as "The connection is incorrect." Not much to go on. I think this may be the same as "error 26", but I'm not 100% sure on that.

My code could connect fine to the production DB machine, running SQL workstation (which is the more expensive version I believe). And a client on another machine that could also connect to that production DB, could not connect to our SQL Express machine.

So, with all fingers pointing at the SQL Express server, we tried things and googled things and swore at things. Wrong barking tree!

I've given the answer in the subject. SQL Server listens on port 1433 by default. Our SQL server was listening on that port, so you would think no need to specify it. But this DSN fails:

DRIVER={SQL Server};
SERVER={127.0.0.1};UID={myuser};PWD={mypassword}; DATABASE={mydatabase}

whereas this one works:
DRIVER={SQL Server};
SERVER={127.0.0.1,1433};UID={myuser};PWD={mypassword}; DATABASE={mydatabase}

Only for SQL Express it seems. No need to specify the default port when connecting to SQL workstation.

Nice one Microsoft. Just got to work on quality control and documentation a bit more, oh, and consistency, and then they could probably go professional with this software business of theirs.

1 comment:

darren said...

By the way, Microsoft have now released a PHP driver for SQL Server:
http://msdn.microsoft.com/en-us/library/cc296170(SQL.90).aspx

It is fairly new (e.g. they say they've been testing on php 5.2.6). If anyone is using it, especially for reading/writing text in Asian languages, I'd love to hear your experiences.