PHP Error after starting and stopping sql server

Posted on 2011-10-25
Last Modified: 2012-05-12
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. 
NbrOfRecipes FROM LEFT OUTER JOIN dbo.items ON = dbo.items.item_code AND = 
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

Question by:Scamquist
    LVL 11

    Accepted Solution

    Well, you can do a simple trick to keep trying connect until it be done.

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

    Open in new window

    Maybe this code will work but it is not really a solution for your trouble.
    LVL 1

    Author Comment

    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?

    _exec('c:\Inetpub\wwwroot\RunRefresh.bat');  //this is a new function
    _exec('c:\Inetpub\wwwroot\RunRefresh2.bat');  //other batch file
    continue on with my php code

    Open in new window

    LVL 11

    Expert Comment

    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.

    LVL 1

    Author Comment

    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
    LVL 11

    Expert Comment

    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
    LVL 1

    Author Comment

    $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.
    LVL 1

    Author Closing Comment

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

    Expert Comment

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now