Re: Problem with a query against MS SQL 2005

Frediano,

Thank you for your guidance. I dug in more deeply.

The second part of the execute statement is obviously the most critical. I 
ran it directly on the database server and it is exceeding the time-outs.

So, what once took 8-10 seconds to run is now taking the SQL server 15+ 
minutes to produce the same results. Clearly that isn't a problem with 
freetds.

(I started looking there, since the commands shouldn't actually return 
anything to freetds other than a 'success' status. At one point, I thought 
it might be returning multiple result sets (from the IF.. and then the 
SELECT...), which is why I thought it might be freetds (since 7.1 doesn't 
handle MARS).)

Thank you again,
Rob





Rob Morin


Systems Integration Architect
Silverpop, an IBM Company 

Office: 404.348.9124
Email: [email protected]
6303 Barfield Rd 
Atlanta, GA 30328

Twitter | Facebook | LinkedIn





From:   Frediano Ziglio <[email protected]>
To:     FreeTDS Development Group <[email protected]>
Date:   09/04/2015 04:37 PM
Subject:        Re: [freetds] Problem with a query against MS SQL 2005
Sent by:        [email protected]



Il 01/Set/2015 13:21, "Rob Morin" <[email protected]> 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
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds