SQLSTATE[HY000]: General error: 20003 Adaptive Server connection timed out [20003]

1.6k views Asked by At

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.

0

There are 0 answers