CODEWITHSUNDEEP
phpphp-mssql
Michael Cole
Michael Cole

Reputation: 289

Executing MSSQL stored procedure from php

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);

Upvotes: 3

Views: 2638

Answers (2)

icc97
icc97

Reputation: 12793

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.

Upvotes: 0

Professor Falken
Professor Falken

Reputation: 1077

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).

Upvotes: 3

Related Questions