3

I am trying to call a MS SQL stored procedure form php.

I have tested the stored procedure by itself in management studio with hard coded parameters and was able to get it to execute with the expected results.

However when attempt to call it from php i get these errors.

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in C:\xampp\htdocs\index.php on line 145

Fatal error: Maximum execution time of 60 seconds exceeded in C:\xampp\htdocs\index.php on line 146

in the code I am posting line 145 is the mssql_execute command.

any help would be appreciated.

    $userQuery = "select top 1 id from bravo_biometric_batch order by id desc";
    $result = mssql_query($userQuery,$DB);
    $bravo_biometric_batch_id = mssql_result($result,0,0);

    $company_year_id = 237;
    $date = date("Ymd");
    $server_file_name = "e:\\bravo_csv\\".$filename;

    $csv_proc_name = mssql_init('bulk_insert_bravo_csv',$DB);
    mssql_bind($csv_proc_name, '@data_csv_file',$server_file_name,SQLVARCHAR, false,false,200);
    mssql_bind($csv_proc_name, '@bravo_biometric_batch_id',$bravo_biometric_batch_id, SQLINT2, false,false, 10);
    mssql_bind($csv_proc_name, '@company_year_id',$company_year_id, SQLINT2, false,false,10);
    mssql_bind($csv_proc_name, '@upload_date',$date, SQLVARCHAR, false,false,20);

    mssql_execute($csv_proc_name);
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Michael Cole
  • 289
  • 2
  • 7
  • 21
  • echo mssql_get_last_message() after the execute line to find out more about your error. Post back with the details. http://www.php.net/manual/en/function.mssql-get-last-message.php – Jared Oct 17 '11 at 13:08
  • I added the timout error message and did not receive any new error information. – Michael Cole Oct 17 '11 at 13:15

2 Answers2

3

The error is saying that your stored procedure is taking more than 60 seconds to run. By default, PHP times out at 60 seconds (this is set in seconds in php.ini with mysql.connect_timeout)

You should try running the procedure by hand in SQL Query Analyzer with the same values you're timing out on, and see how long it takes. For example, if the file was c:\temp\csvfile.csv on the server, the batch ID 1234, company year of 2011 and the current time for the date, you'd use:

print 'Start time: ' + getdate()
exec 'bulk_insert_bravo_csv' 'c:\\temp\\csvfile.csv', 1234, 2011, getdate()
print 'End time: ' + getdate()

Note that any \ characters must be escaped as \ when you run the SQL in query analyzer.

This will run the same command, and print the time before and after. You can then see how long it's taking. If you're not sure of the values to use, have your PHP page print them out before the mysql_execute() command, then plug them in in query analyzer.

When you know how long it takes, you can then adjust the timeout for your procedure to be more than 60 seconds. Another method would be to optimize your stored procedure (if possible), or run smaller CSV batches if you can. Be aware that you may hit other timeouts after you increase the 60 second query timeout. For instance, PHP itself will have a timeout set in max_execution_time in PHP.ini. Apache will have a default timeout of 300 (5 minutes) as well. So as you increase one timeout, you may run into another and have to increase that too.

Yet another method to get around this problem is to run PHP in command line mode, from a shell script or batch file or something. That gets you around most of the timeouts (Apache, PHP at least).

Professor Falken
  • 1,017
  • 11
  • 13
  • Thank you all for your responses i wrestled with it for a few days and what i ended up using that worked for me was this. I set the execute with php variables. $userquery = "execute storedproc $var1,$var2..." then used mssql_query($userquery,$db) and got it to work without having to initlize and bind all the paramters thank you all for your responses helped me get to the solution that worked. – Michael Cole Oct 19 '11 at 13:29
0

You actually want to be looking for the mssql.timeout value in the php.ini file. Or if its not there setting it. The default is 60 (seconds) which is what you were hitting.

icc97
  • 11,395
  • 8
  • 76
  • 90