Skip to Content.
Sympa Menu

freetds - Re: [freetds] Problem with a query against MS SQL 2005

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Problem with a query against MS SQL 2005
  • Date: Fri, 4 Sep 2015 05:12:16 +0100

Il 01/Set/2015 13:21, "Rob Morin" <morinr AT us.ibm.com> ha scritto:
>
> All,
>
> First, please let me apologize if this already went to the group. I think
> I tried to send it before I was officially a member, so I don't know if it
> actually went or not.
>
> I'm upgrading a server from PHP 4.3 to PHP 5.6 and doing the necessary
> code remediation. I've got most things working, but I'm having a problem
> on certain queries and I'm to the point that I believe it is the driver
> (PHP 4.3 used a mssql driver, PHP 5.6 uses FreeTDS) that's causing the
> problem.
>
> The Environment
> CentOS 7.1 with PHP 5.6.8 and php56w-mssql from webtatic
> This comes with FreeTDS 0.91 from epel
>
> I've set the FreeTDS global protocol version to 7.2 in freetds.conf. When
> it starts, I see an error in the log : "TDS version downgraded to 7.1!"
> The wait time is also set to 180.
>
> The log of the downgrade:
> util.c:331:tdserror(0x7fd16ec064c0, 0x7fd16ec9edd0, 100, 0)
> dblib.c:7929:dbperror(0x7fd16ec9e360, 100, 0)
> dblib.c:7981:100: "TDS version downgraded to 7.1!"
> dblib.c:8002:"TDS version downgraded to 7.1!", client returns 2
> (INT_CANCEL)
> util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
> util.c:384:tdserror: returning TDS_INT_CANCEL(2)
> iconv.c:330:tds_iconv_open(0x7fd16ec9edd0, UTF-8)
> iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
> iconv.c:187:local name for UTF-8 is UTF-8
> iconv.c:187:local name for UCS-2LE is UCS-2LE
> iconv.c:187:local name for UCS-2BE is UCS-2BE
> iconv.c:349:setting up conversions for client charset "UTF-8"
> iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
> iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE"
> conversion
> iconv.c:394:tds_iconv_open: done
> net.c:205:Connecting to 10.100.5.201 port 1433 (TDS version 7.1)
> net.c:270:tds_open_socket: connect(2) returned "Operation now in
> progress"
> net.c:310:tds_open_socket() succeeded
> util.c:156:Changed query state from DEAD to IDLE
>
> I've established that the server is able to connect to the SQL server and
> run 'normal' queries. Apparently this one is 'special', but I'm not sure
> at what level.
>
> Here's the query:
> IF OBJECT_ID('tempdb..#EarlyRenewals') IS NULL
> SELECT * INTO #EarlyRenewals FROM
> [atl-sportal01].sportal.dbo.EarlyRenewalsBasedOnDates)
>
> It appears to be timing out while waiting on the SQL server (SQL 2005). It
> takes the server ~8-10 secs to respond to the query. The php script is
> 'failing' after waiting ~3 minutes.
>
> Here's the log after the query is sent:
> dblib.c:4639:dbsqlok(0x7fd16ecc1540)
> dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
> token.c:540:tds_process_tokens(0x7fd16ecc2890, 0x7fff3872e248,
> 0x7fff3872e24c, 0x6914)
> util.c:156:Changed query state from PENDING to READING
> util.c:331:tdserror(0x7fd16ec064c0, 0x7fd16ecc2890, 20003, 0)
> dblib.c:7929:dbperror(0x7fd16ecc1540, 20003, 0)
> dblib.c:7981:20003: "Adaptive Server connection timed out"
> dblib.c:8002:"Adaptive Server connection timed out", client
> returns 2 (INT_CANCEL)
> util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
> util.c:384:tdserror: returning TDS_INT_CANCEL(2)
> util.c:156:Changed query state from READING to DEAD
> token.c:555:processing result tokens. marker is 0()
> token.c:122:tds_process_default_tokens() marker is 0()
> token.c:125:leaving tds_process_default_tokens() connection dead
> util.c:104:logic error: cannot change query state from DEAD to
> PENDING
> dblib.c:3196:dbcancel(0x7fd16ecc1540)
> dblib.c:7929:dbperror(0x7fd16ecc1540, 20047, 0)
> dblib.c:7981:20047: "DBPROCESS is dead or not enabled"
> dblib.c:8002:"DBPROCESS is dead or not enabled", client returns 2
> (INT_CANCEL)
>
> Any help or guidance you can provide is greatly appreciated. Please let me
> know if there's any additional information I can provide.
>
> Thank you,
> Rob
>

One thing you can do to try if is a problem with connection settings is to
try different freetds libraries. We provide dblib (that you are using),
ODBC and ctlib. The key is mainly ODBC or not ODBC so you should try same
query with ODBC. So run the query with tsql on one test and the other with
bsqlodbc, isql or any other ODBC tool using freetds driver (many
distribution package ODBC driver in a different package).
All Microsoft tools use ODBC settings for connection (beside dblib which
Microsoft declared obsolete)

Frediano




Archive powered by MHonArc 2.6.24.

Top of Page