PHP, MSSQL & FreeTDS - Stored Procedure execution failed

[lunagra80]

Hi,

 

the problem is similar to other posts in the forum, 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 (MSSQL 2008), it takes minutes to run, even using the Management Studio but it doesn’t fail there, but when I try to call it from my PHPpage after 60 seconds it stops and gives me “stored procedure execution failed” error.

 

This is my code

 

PHP:

$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

 

[lunagra80]

I changed my code in this way becuase there was some errors,

 

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

}

 

but 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