PSC ODBC Driver via PHP under IIS6 blows up when retrieving large sets of records.
Problem:
PSC ODBC driver randomly blows up on large datasets when called using PHP under IIS6
Solution: From PSC tech support kbase entry:
See PSC tech support kbase entry P149377.
(reproduced below if the link above is broken)
ID: P149377
Title: "PHP, running under IIS6, error log shows ODBC errors when the SQL-92 query retrieves a large sets of records from the progress/OpenEdge database"
Created: 07/10/2009 Last Modified: 07/10/2009
Status: Unverified
Symptoms:
# PHP, running under IIS6, error log shows ODBC errors when the SQL-92 query retrieves a large sets of records from the progress/OpenEdge database
# PHP Warning: odbc_exec() [<a href='function.odbc-exec'>function.odbc-exec</a>]: SQL error: [DataDirect-Technologies][ODBC PROGRESS driver]Cannot open file 'C:\WINDOWS\SYSTEM32\INETSRV\'., SQL state S1000 in SQLExecDirect in C:\Inetpub\wwwroot\Jargon_TT\select.php on line 55
# The ODBC trace log is intermittently generated but when it works shows the following errors
# DIAG [S1000] [DataDirect-Technologies][ODBC PROGRESS driver]Cannot open file 'C:\WINDOWS\SYSTEM32\INETSRV\'. (30040)
# DIAG [S1000] [DataDirect-Technologies][ODBC PROGRESS driver]Cannot delete file: 'C:\WINDOWS\SYSTEM32\INETSRV\'. (1502)
# DIAG [01S02] [DataDirect-Technologies][ODBC PROGRESS driver]Option value changed. (0)
Facts:
# Allowing full control to IUSR_MACHINE-NAME user or everyone to 'C:\WINDOWS\SYSTEM32\INETSRV folder did not resolve the issue
# When retrieving small sets or results, everything works fine
# Other 3rd party ODBC tools, such as WinSQL, works fine regardless of the size of the record set provided that the database is started with some SQL tuning parameters
# The JDBC also works fine
# Running the same PHP script from DOS prompt works fine, and the ODBC trace log also gets created every time as well
# Changing the anonymous user of IIS from IUSR_Machine-name to a user with administrator rights resolved the issue
# PHP 5.2.x or 5.3.x
# IIS 6 on Windows 2003
# Windows
# Progress 9.x
# OpenEdge 10.x
Cause:
# This is an IIS issue with IIS user privileges. IIS user uses IUSR_MACHINE-NAME user with very limited rights on different folders outside of wwwroot folder. On the other hand ODBC driver tries to create a temp file to cache the records after a certain records set. Since IIS user is trying to run the query against ODBC driver, it lacks privileges to create temporary file by Progress/OpenEdge SQL-92 ODBC driver. Therefore, the errors are apparent.
Fixes:
Change the anonymous user (IUSR_MACHINE-NAME) of IIS to a local system user with some local security policies added under Anonymous access and authentication control on the the directory security tab of the Web sites properties
Notes:
You can further research with FileMon or process monitor utility from Microsoft TechNet web site to determine what other directories the IIS user needs read, write and modify privileges if the anonymous user can not be changed for business reasons.
http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx
PSC ODBC driver randomly blows up on large datasets when called using PHP under IIS6
Solution: From PSC tech support kbase entry:
See PSC tech support kbase entry P149377.
(reproduced below if the link above is broken)
ID: P149377
Title: "PHP, running under IIS6, error log shows ODBC errors when the SQL-92 query retrieves a large sets of records from the progress/OpenEdge database"
Created: 07/10/2009 Last Modified: 07/10/2009
Status: Unverified
Symptoms:
# PHP, running under IIS6, error log shows ODBC errors when the SQL-92 query retrieves a large sets of records from the progress/OpenEdge database
# PHP Warning: odbc_exec() [<a href='function.odbc-exec'>function.odbc-exec</a>]: SQL error: [DataDirect-Technologies][ODBC PROGRESS driver]Cannot open file 'C:\WINDOWS\SYSTEM32\INETSRV\'., SQL state S1000 in SQLExecDirect in C:\Inetpub\wwwroot\Jargon_TT\select.php on line 55
# The ODBC trace log is intermittently generated but when it works shows the following errors
# DIAG [S1000] [DataDirect-Technologies][ODBC PROGRESS driver]Cannot open file 'C:\WINDOWS\SYSTEM32\INETSRV\'. (30040)
# DIAG [S1000] [DataDirect-Technologies][ODBC PROGRESS driver]Cannot delete file: 'C:\WINDOWS\SYSTEM32\INETSRV\'. (1502)
# DIAG [01S02] [DataDirect-Technologies][ODBC PROGRESS driver]Option value changed. (0)
Facts:
# Allowing full control to IUSR_MACHINE-NAME user or everyone to 'C:\WINDOWS\SYSTEM32\INETSRV folder did not resolve the issue
# When retrieving small sets or results, everything works fine
# Other 3rd party ODBC tools, such as WinSQL, works fine regardless of the size of the record set provided that the database is started with some SQL tuning parameters
# The JDBC also works fine
# Running the same PHP script from DOS prompt works fine, and the ODBC trace log also gets created every time as well
# Changing the anonymous user of IIS from IUSR_Machine-name to a user with administrator rights resolved the issue
# PHP 5.2.x or 5.3.x
# IIS 6 on Windows 2003
# Windows
# Progress 9.x
# OpenEdge 10.x
Cause:
# This is an IIS issue with IIS user privileges. IIS user uses IUSR_MACHINE-NAME user with very limited rights on different folders outside of wwwroot folder. On the other hand ODBC driver tries to create a temp file to cache the records after a certain records set. Since IIS user is trying to run the query against ODBC driver, it lacks privileges to create temporary file by Progress/OpenEdge SQL-92 ODBC driver. Therefore, the errors are apparent.
Fixes:
Change the anonymous user (IUSR_MACHINE-NAME) of IIS to a local system user with some local security policies added under Anonymous access and authentication control on the the directory security tab of the Web sites properties
Notes:
You can further research with FileMon or process monitor utility from Microsoft TechNet web site to determine what other directories the IIS user needs read, write and modify privileges if the anonymous user can not be changed for business reasons.
http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx