Hi,

The problem is similar to other posts in internet, but I can't fix my issue using their solutions, so I thought to open a new topic.

I implemented a stored procedure in my db (SQL Server 2008), it takes minutes to run, even using the SQL Server Management Studio but it doesn’t fail there, but when I try to call it from my PHP page after 60 seconds it stops and gives me “stored procedure execution failed” error.

This is my PHP code:

$sql = “dbname.dbo.mySP”; mssql_init($sql); mssql_bind($sql, “Param1”, $param1, SQLVARCHAR, false, false, 40); //varchar mssql_bind($sql, “Param2”, $param2, SQLVARCHAR, false, false, 10); //datetime mssql_bind($sql, “Param3”, $param3, SQLVARCHAR, false, false, 10); //datetime mssql_bind($sql, “Param4”, $param4, SQLVARCHAR, false, false, 10); //varchar mssql_execute($sql)); The SP should insert records in a table in the db, so I don’t have any variable as result.

FreeTDS:

[myserver] host = servername port = 1433 tds version = 8.0 client charset = UTF-8 instance= myinstance I tried to:

•Change the host in the FreeTDS to the IP instead of the server name.
•Increase and decrease PHP variables in the php.ini file (set_time_limit, default_socket_timeout, mssql.timeout, max_execution_time, etc) to check what was the actual variable to command the timeout. No luck with that, those changes didn’t affect the page.
•Run the SP in Java, code. It worked, so I think the issue is about PHP.
•Use the SQL Server Profile to check if the process hangs in the db. But the sql process stops after the page gives back the error. So the db is working fine, but the connection just stops.
•Create a fresh new connection to the db.
Any of those above work, I don’t know what to try more.

Can someone please advice?

Thanks

    For one, can you show us the actual code you're using? (It can't be exactly as shown above, since that code would never even run at all due to PHP parse errors. 😉)

    Second, can you show us the exact error message returned by the MSSQL server just after the call to the stored proc fails?

      Sorry there was one more round bracket...

      $sql = 'dbname.dbo.mySP';
      mssql_init($sql);
      mssql_bind($sql, 'Param1', $param1, SQLVARCHAR, false, false, 40); //varchar
      mssql_bind($sql, 'Param2', $param2, SQLVARCHAR, false, false, 10); //datetime
      mssql_bind($sql, 'Param3', $param3, SQLVARCHAR, false, false, 10); //datetime
      mssql_bind($sql, 'Param4', $param4, SQLVARCHAR, false, false, 10); //varchar
      $result = mssql_execute($sql);

      The error I get is

      Warning: mssql_execute(): stored procedure execution failed in /usr/local/apache/htdocs/xxx/xxx/xxx.php3 on line 302

      Warning: mssql_query(): Query failed in /usr/local/apache/htdocs/xxx/xxx/xxx.php3 on line 292

      Warning: mssql_query(): Query failed in /usr/local/apache/htdocs/xxx/xxx/xxx.php3 on line 292
      ....

        If [man]mssql_query/man returns boolean FALSE, use [man]mssql_get_last_message/man to retrieve the error message from the MSSQL server.

        What does the actual MSSQL error message say?

        EDIT: Wait a minute - I think I see the problem now.

        [man]mssql_bind/man expects the first parameter to be a MSSQL statement resource (e.g. one returned by [man]mssql_init/man). You, however, are passing it a string (which I would think would cause error messages before mssql_execute()... odd).

          Thanks bradgrafelman, I changed it like that

          $sql = 'dbname.dbo.mySP';
          $var = mssql_init($sql);
          mssql_bind($var, 'Param1', $param1, SQLVARCHAR, false, false, 40); //varchar
          mssql_bind($var, 'Param2', $param2, SQLVARCHAR, false, false, 10); //datetime
          mssql_bind($var, 'Param3', $param3, SQLVARCHAR, false, false, 10); //datetime
          mssql_bind($var, 'Param4', $param4, SQLVARCHAR, false, false, 10); //varchar
          if (!($result = mssql_execute($var)))
          {
          print mssql_get_last_message();
          }

          and I get the same error

          Warning: mssql_execute(): stored procedure execution failed in /usr/local/apache/htdocs/xxx/xxx/xxx.php3 on line 1072 // this line is the if statement of course

            Not much else I can suggest to help, sorry... I'm not that well versed in M$SQL. :p

              Write a Reply...