PHP on IIS 6 w/MS SQL HELP!!

[idgaf]

Does anyone know why PHP might have intermittent connection problems to an MS SQL database?

PHP 4.3.9
WIN 2003 w/IIS 6

Sometimes the script will work fine but then for minutes at a time and for no aparent reason, the PHP script will yield:

Warning: mssql_pconnect(): Unable to connect to server: XBOX in C:\Siteroot\crm\data\main.php on line 7

The relevant code follows:

define("db_hostname", "XBOX");
define("db_database", "CRM_DEV");

function query() {
    $_conn = mssql_pconnect(db_hostname) or die(mssql_get_last_message());
    mssql_select_db(db_database, $_conn) or    die(mssql_get_last_message());
    $_rset = mssql_query(@func_get_arg(0), $_conn) or die(mssql_get_last_message());
    mssql_close($_conn) or die(mssql_get_last_message());
    return $_rset;
} 

Not even restarting MS SQL or IIS seems to fix the problem, it still errors out whenever it wants.

[ober0330]

I have almost your exact setup, however I do not use persistent connections and it works fine.

However I doubt that is this issue. Your problems sound more like a physical hardware problem. If it works some of the time, I don't think your problem is software related.

Just a few questions however:

1) What version of MSSQL?
2) What is the traffic load on the server?
3) Why are you putting the connection in a function?

A few things that you are doing that I am not:

1) I never close the connections. They close automatically, most likely after the execution of the page, so closing the connection is not necessary and could be causing some of your problems, especially since you're using a persisitent connection type.

2) I always open the database at the top of my page and run various queries further down the page.

HTH

[idgaf]

I tried both connect and pconnect and at the time, pconnect seemed to give the error less often. Some wierd observations:

- Changing mssql.allow_persistent to Off NEVER works
- mssql.max_persistent has to be set to >0
- IIS/PHP and MS SQL run on the same machine
- Hostname (localhost, 127.0.0.1 or xbox) don't seem to matter

And to answer your questions:

1) 8.00.760
2) 1 or 2 concurrent users
3) To return a disconnected resultset

I open the connection, get the results and close the connection because it's 90% read and 10% write and I've always been taught to close connections when you're not using them.

There are the obvious resource consequences of an open connection but when I use these same practices on PHP/MySQL or ASP/MSSQL, I never have any issues at all.

I'm really cornfused.

[ober0330]

Ok... another difference, my webserver and DBMS are not on the same system. However, I do have Apache and PHP running on a development server with MSSQL 2K and it works fine.

Have you run ASP/MSSQL on the same server you're using with your current config? I guess my point is that I'm trying to rule out the computer itself as being the problem and not something with the software configuration.

Is there any specific characteristics of the times that it won't connect? (extended inactivity, after subsequent calls, etc?)

How often is it happening, what are you doing at the time, is it limited to one set of queries or is it independent of the task?

[idgaf]

I also tried just _connect and _pconnect with no close and it actually seems to get a little worse in terms of pressing F5 10 times, 9 will yield an error vs. ~50/50 before.

[idgaf]

The same server has ASP .NET applications running just fine.

I was just watching SQL Profiler and when it errors, nothing gets logged but when there is no error, I see a login, t-sql batch and logout.

It's like PHP physically can't connect for some reason.

All queries run through that function so when it decides to stop, it dies on the first query.

There really aren't any specific characteristics about the errors. It worked fine all day Friday and now the users were supposed to start testing it today, the first person couldn't even login because of this error.