ID: 33694 Updated by: [EMAIL PROTECTED] Reported By: spam at meyrick dot co dot nz -Status: Open +Status: Assigned Bug Type: MSSQL related Operating System: Windows 2003 Server Enterprise PHP Version: 4.4.0 -Assigned To: +Assigned To: fmk
Previous Comments: ------------------------------------------------------------------------ [2005-07-14 11:03:03] spam at meyrick dot co dot nz Description: ------------ I am using IIS6 (Win2k3 Server, SQL Server 2000, PHP 4.3.4 DLL) I am connecting to MSSQL using username/pwd and pconnect no probs there. My problem is that if I execute a horribly incorrect SQL statement one of the perminant connections to MSSQL is disabled giving 'unable to connec to database' (pconnect() == false) errors 50% of the time.. For example: Table: [Stock Items]: StockItemID int no nulls identity SupplierID int no nulls <--** test varchar(50) allow null ... $sql = "INSERT INTO [Stock Items] (test) VALUES ('this should fail')"; $result = mssql_query($sql); Warning: mssql_query(): message: Cannot insert the value NULL into column 'SupplierID', table 'Database Name.dbo.Stock Items'; column does not allow nulls. INSERT fails. (severity 16) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): message: The statement has been terminated. (severity 0) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): General SQL Server error: Check messages from the SQL Server. (severity 5) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): Query failed in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): Attempt to initiate a new SQL Server operation with results pending. (severity 7) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 186 The last error is the one that is the kick in the guts as now EVERY sql statement until the page finishes loading will fail. When the page is refreshed pconnect() will work 50% of the time (if they get a valid connection it works, if they get the dead connection it fails). The only way I can fix this problem is restarting IIS which is a pain. I can replicate the problem by killing the process from MSSQL Enterprise manager. Any help would be great. ps. an answer of "just fix your sql statement" is not what I'm looking for as it is not allways related to invalid sql statements, running large querieis using ODBC over the www seems to cause the exact same problem, so does re/syncronizing a large replicated sql database. Thanks. Reproduce code: --------------- Table: [Stock Items]: StockItemID int no nulls identity SupplierID int no nulls <--** test varchar(50) allow null ... $sql = "INSERT INTO [Stock Items] (test) VALUES ('this should fail')"; $result = mssql_query($sql); Expected result: ---------------- $result == valid mssql result Actual result: -------------- Warning: mssql_query(): message: Cannot insert the value NULL into column 'SupplierID', table 'Database Name.dbo.Stock Items'; column does not allow nulls. INSERT fails. (severity 16) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): message: The statement has been terminated. (severity 0) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): General SQL Server error: Check messages from the SQL Server. (severity 5) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): Query failed in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49 Warning: mssql_query(): Attempt to initiate a new SQL Server operation with results pending. (severity 7) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 186 + dead pconnection to MSSQL server until IIS (php dll) is restarted ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=33694&edit=1