Page 1 of 1

SQL Server, PHP, FreeTDS, Stored Procedure execution failed

Posted: Mon Jul 11, 2011 8:40 am
by lunagra80
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:

Code: Select all

$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:

Code: Select all

[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

Re: SQL Server, PHP, FreeTDS, Stored Procedure execution fai

Posted: Mon Jul 11, 2011 9:16 am
by Weirdan
Have you tried to set timeout in freetds.conf (see http://www.freetds.org/userguide/freetdsconf.htm for details)?

Re: SQL Server, PHP, FreeTDS, Stored Procedure execution fai

Posted: Mon Jul 11, 2011 9:25 am
by lunagra80
Ah yes I tried it, and it didn't work... sorry I forgot to mention it.

Re: SQL Server, PHP, FreeTDS, Stored Procedure execution fai

Posted: Mon Jul 11, 2011 11:07 am
by Weirdan
What is the exact error message you're getting? Also the mssql_bind() man page says you have to use @ sign in front of the stored procedure parameter names when binding them.

Re: SQL Server, PHP, FreeTDS, Stored Procedure execution fai

Posted: Tue Jul 12, 2011 3:41 am
by lunagra80
The error I'm getting is

Code: Select all

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(): Unable to set query in /usr/local/apache/htdocs/xxx/xxx/xxx.php3 on line 292
....
Sorry I forgot to keep them when I substiture the real veriables with those generic, it is like that

Code: Select all

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

Re: SQL Server, PHP, FreeTDS, Stored Procedure execution fai

Posted: Tue Jul 12, 2011 10:42 am
by lunagra80
I changed my code in this way becuase there was some errors,

Code: Select all

$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