• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 834
  • Last Modified:

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

0
Scamquist
Asked:
Scamquist
  • 4
  • 4
1 Solution
 
NoiSCommented:
Well, you can do a simple trick to keep trying connect until it be done.

<?php

$tries=30; // Number of retries
$cntr=0;
$conn=false;
do{
  // Your connection code here
  //example
  $conn = mssql_connect ($hostname, $username, $password) || sleep(500); // If the connection fails, wait 500 
ms  
  $cntr++;
}while(!$conn || $cntr<$tries);

Open in new window

Maybe this code will work but it is not really a solution for your trouble.
0
 
ScamquistAuthor Commented:
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

0
 
NoiSCommented:
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.

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
ScamquistAuthor Commented:
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?
0
 
NoiSCommented:
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
0
 
ScamquistAuthor Commented:
$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.
0
 
ScamquistAuthor Commented:
Thank you.  You did provide a method to do what I asked.  I need to tweak a bit.
0
 
NoiSCommented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now