I'm trying to execute a store procedure on a SQL Server 2008. After about 30 seconds it ends in
SQLSTATE[HY000]: General error: 20003 Adaptive Server connection timed out [20003]
I've checked if on the SQL Server remote connections are allowed and they are with a timeout of 600 seconds (default).
This is my config.yml
doctrine:
dbal:
default_connection: default
connections:
default: ...
mssql:
driver_class: \Lsw\DoctrinePdoDblib\Doctrine\DBAL\Driver\PDODblib\Driver
host: mssql_freetds
port: "%stage_database_port%"
dbname: "%stage_database_name%"
user: "%stage_database_user%"
password: "%stage_database_password%"
charset: UTF8
options:
timeout: 600 // I don't know if it's correct but it doesn't work even without this
And this is my code
$connection = $this->getDoctrine()->getManager('mssql')
->getConnection();
$stmt = $connection->prepare("Exec SP_MyStoreProcedure ?, ?");
$stmt->bindValue(1, $sd->format("Y-m-d") /* This is a date */);
$stmt->bindValue(2, $ed->format("Y-m-d") /* This is a date */);
$stmt->execute();
[EDIT]
So, i've found that i should configure time out on freetds configuration because of LswDoctrinePdoDblib. I'v edited the /etc/freetds/freetds.conf file but it still ends up connection after 30 seconds
/etc/freetds/freetds.conf
# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
timeout = 600
connect timeout = 600
[mssql_freetds]
host = 192.168.0.xx
port = 1433
timeout = 600
tds version = 8.0
client charset = UTF-8
text size = 20971520
[EDIT 2]
Nothing worked for timeout except this:
doctrine:
dbal:
default_connection: default
connections:
default: ...
mssql:
driver_class: \Lsw\DoctrinePdoDblib\Doctrine\DBAL\Driver\PDODblib\Driver
host: mssql_freetds
port: "%stage_database_port%"
dbname: "%stage_database_name%"
user: "%stage_database_user%"
password: "%stage_database_password%"
charset: UTF8
options:
2: 600 # 2 is the equivalent of \PDO::ATTR_TIMEOUT
But the store procedure should end in 17seconds, it waited 10 minutes ending with the same result. I don't know why. It seems to be a FREETDS/SQL SERVER bug or something like that...
[EDIT 3] Found that the problem was a "NOT IN" clause in the SQL of the stored procedure. I had to replace it with a LEFT JOIN combined with a IS NULL clause in WHERE statement. There's also a "IN" clause but it works. I don't know if it's a FREETDS or a LswDoctrinePdoDblib issue.