asked on

PHP Error after starting and stopping sql server

I have an intranet using php 5.3.8 on Server 2003 using IIS 6.0 and SQL 2005.

I run a command to call a batch file that starts and stops sql server and then calls a stored procedure to delete records from a table Items and then refreshes the records from the MAS90. I need to stop and start because the connection to a MAS90 system fails.

When the refresh is complete, when I go to any of the pages that connect to the database, I get the errors like what is in the code box below. If I go from the ListItems.php page to the ListRecipies.php to and back to ListIIems.php the values come up without errors.

I can also F5 three times or ctrl + F5 to get the page to refresh properly.

is there another way to do this. Javascript...Page Refresh code...Magic

Error in query preparation/execution. Array ( [0] => Array 
( [0] => 08S01 [SQLSTATE] => 08S01 [1] => 233 [code] => 
233 [2] => [Microsoft][SQL Server Native Client 10.0]
Shared Memory Provider: No process is on the other end of 
the pipe. [message] => [Microsoft][SQL Server Native 
Client 10.0]Shared Memory Provider: No process is on the 
other end of the pipe. ) [1] => Array ( [0] => 08S01 
[SQLSTATE] => 08S01 [1] => 233 [code] => 233 [2] => 
[Microsoft][SQL Server Native Client 10.0]Communication 
link failure [message] => [Microsoft][SQL Server Native 
Client 10.0]Communication link failure ) ) 

__________________________________________________

Warning: sqlsrv_fetch_array() expects parameter 1 to be 
resource, boolean given in 
C:\Inetpub\wwwroot\queries\qry_recipeList_Count.php on 
line 13 Error in query preparation/execution. 
qry_recipeList_count
SELECT TOP 100 PERCENT Count(dbo.recipes.recipe_id) AS 
NbrOfRecipes FROM dbo.recipes LEFT OUTER JOIN dbo.items ON 
dbo.recipes.recipe_code = dbo.items.item_code AND 
dbo.recipes.recipe_company_code = 
dbo.items.item_company_code WHERE recipe_company_code 
= 'GAM' AND (dbo.items.item_prod_type = 'F')
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP 
[1] => -14 [code] => -14 [2] => An invalid parameter was 
passed to sqlsrv_fetch_array. [message] => An invalid 
parameter was passed to sqlsrv_fetch_array. ) )

Open in new window

ASKER CERTIFIED SOLUTION

NoiS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scamquist

ASKER

I may have found a way to do the refresh without starting and stopping the sql server.

I have an include('queries/qry_list.php') which has a record $NbrOfListItems

I want to loop through up to 30 times. THe first time $NbrOfListItems > 0 exit the loop and do not run the other batch file.

After 30 tries run a different batch file and continue with the page.

It does not like my }while..... line.

Can you please help?

$tries=30;
$cntr=0
do{
_exec('c:\Inetpub\wwwroot\RunRefresh.bat');  //this is a new function
$cntr++
}while(("$NbrOfListItems")>0);
_exec('c:\Inetpub\wwwroot\RunRefresh2.bat');  //other batch file
continue on with my php code

Open in new window

NoiS

Look, run a batch script through PHP isn't a good idea for many reasons.
You must keep in mind that PHP scripts are a complete program.
When the batch runs, it can create vars that only exist until the end of the script.
You cannot share a var created on the batch (even a DB connection)

One solution is to read the output from your batch file and search for some content.

Scamquist

ASKER

The batch file calls a stored procedure to delete and refresh the items table.

All the loop is doing is confirming that there are any records in the items table. On occassion, when the refresh stored procedure runs, it is unable to connect to the MAS90 database. If I try the stored procedure a few times, it will ususally connect. That is why I want to loop until there are records. The batch file is not creating any variables in php.

Is there something wrong with my syntax that will causes the error at the
}while(
line?

NoiS

Where $NbrOfListItems were defined??

Your value come from where?

The refresh stored procedure is running through a PHP script?
If yes, maybe a simple mssql_close() can help you.
If not,
The SP locks the database?

Try changing the connection method to mssql_pconnect if you is using mssql_connect

Scamquist

ASKER

$NbrOfListItems is from the include('queries/qry_item_ListCount.php')

if i echo ($NbrOfListItems); it will display the number of rows in the table

The batchfile is running
osql -E -d DatabaseName -S ServerName -Q "Exec RunRefresh"

php 5.3.8 does not use mssql_connect

I am not having an issue connecting to sql. The MAS90 database is a linked server in sql. Sometimes I get a connect, other times I do not. Hence the loop.

Scamquist

ASKER

Thank you. You did provide a method to do what I asked. I need to tweak a bit.

NoiS

Scamquist, I did have a similar trouble with Database Link in Oracle using PDO.
My trouble were solved using AUTOCOMMIT in OFF mode.

http://www.php.net/manual/pt_BR/pdo.query.php#85919