It has been a long time since my last post. Currently I'm very busy on a big project...

Yesterday we had a nice problem. A company I work is using the following configuration on it's webserver: IIS, PHP and MSSQL. (yes an S). What's wrong with this configuration? Well two things: IIS and MSSQL.

That's not all: in PHP we connect to the database via 2 methods: via the ADODB COM object and the normal mssql functions from PHP.

We had the following problem in a certain module:

Every time we executed a Second query, the second query failed but we NEVER got an error message. Example:

(pseudo code:)

BEGIN TRANSACTION
SELECT * FROM mytable WHERE kind='item1'
... more code ...
SELECT * FROM mytable WHERE kind='item2' // <= This query failed every time, without an error message ... more code 2 ... COMMIT TRANSACTION

Looks very strange, but after too many hours we found our problem:

(pseudo code:)

BEGIN TRANSACTION
... code 1 ...
SELECT * FROM mytable WHERE kind='item1'
...
INSERT .. INTO mytable( ... ) VALUES ( ... )
..
SELECT * FROM mytable WHERE kind='item2' // <= This query failed every time, without an error message ... code 3 ... COMMIT TRANSACTION

What's the problem:

BEGIN TRANSACTION # is executed by ADODB
SELECT * FROM ... # is executed by mssql_* functions from PHP
INSERT ... # is executed by ADODB
SELECT * FROM # is executed by mssql_* functions from PHP
..


Well when retrieving the data again the mssql_* connection fails because it notices the data has changed and it must wait for the other transaction to finish...

Aaaaaargh.... 8 hours of my time wasted!

Btw. A few year ago we had a good reason forusing ADODB in PHP: the mssql version of PHP wasn't realiable. And we are using "queries from hell" ( sql server XML queries ), which (at that time) couldn't be retrieve by the mssql functions of PHP.