FreeTDS Development Group

["Adrian Lattke" <adrian AT techonline.com>]

Hi Guys,
 
David Barnwell posted a very similar problem to the one I am experiencing on 
Fri Jun 25 15:07:35 EDT 2004:
 
https://lists.ibiblio.org/sympa/arc/freetds/2004q2/016465.html
 
We were using 
  - freetds-0.51  (tds 4.2)
  - DBD::Sybase-0.23 
  - Perl 5.6.0
  - Linux Redhat  7.1
  - MS SQL Server 7.0
 
Let me preface by saying that the above configuration was working splendidly.
 
We decided it was time to upgrade the database server. We installed SQL 
Server 2000 on new hardware, backed up the old database, restored, and voila; 
it all worked great...
 
... Until we upgraded the web server to:
  - freetds-0.62.4  (tds 7.0)
  - DBD::Sybase-1.04 
  - Perl 5.6.0 (left unchanged)
  - Linux Redhat  7.1
  - MS SQL Server 2000
 
First, I tried using TDS 8.0, but I kept getting charset errors. I reverted 
to using 7.0, and those errors went away. Everything seemed to work fine, 
until I came across queries like the following:
 
----------------------------------------------
set rowcount 0
select distinct 
 c.content_id as content_id,
 c.content_title as content_title
into  #temptable
from  MYDB.dbo.content c
where  c.content_type_name = 'mycontenttype'
select  t.content_id,
 t.content_title,
 c.content_summary,
 c.broadcast_date,
 c.broadcast_length,
 c.content_author_company
from  TOL.dbo.content c, #temptable
where  c.content_id = t.content_id
order by c.broadcast_date desc
drop table #temptable
set rowcount 0
----------------------------------------------
 
If I run this query against my SQL Server 7.0 server, using the new 
configuration (i.e. freetds-0.62.4), it works fine. In fact, if I run the 
query by tsql and run it against my SQL Server 2000 server, it runs fine. For 
some reason, it returns "0" when I try fetching the results in PERL against 
SQL Server 2000.
 
BTW - Even if I take out the "drop table" and "set rowcount 0" statements at 
the end, it still returns "0".
 
I set the "debug level" to 10 on both databases (TDS 7.0) and I tried to 
compare the dumps.
 
SQL Server 7.0 (TDS 7.0)
  - 39053 lines
  - 2.139 MB
 
SQL Server 2000 (TDS 7.0)
  - 2263 lines
  - 132 KB
 
 
Where the big differences are:
 
 
At the end of the files:
 
7.0 (lines 39018 - 39052)
-------------------------
21:37:42.406114 _ct_get_client_type(type 39, user 0, size 50)
21:37:42.406134 cs_convert()
21:37:42.406149 _ct_get_server_type(1)
21:37:42.406165 _ct_get_server_type(1)
21:37:42.406182 cs_convert() srctype = 47 (7) desttype = 47 (51)
21:37:42.406202 cs_convert() srctype = desttype
21:37:42.406218 cs_convert() desttype = character
21:37:42.406235 _ct_bind_data(): column_type: 39 column_len: 15
21:37:42.406254 _ct_get_server_type(1)
21:37:42.406271 _ct_get_client_type(type 39, user 0, size 100)
21:37:42.406291 cs_convert()
21:37:42.406306 _ct_get_server_type(1)
21:37:42.406322 _ct_get_server_type(1)
21:37:42.406338 cs_convert() srctype = 47 (15) desttype = 47 (101)
21:37:42.406359 cs_convert() srctype = desttype
21:37:42.406375 cs_convert() desttype = character
21:37:42.406406 ct_fetch()
21:37:42.406425 ct_results()
21:37:42.406442 processing result tokens.  marker is  fd(DONE)
21:37:42.406464 tds_process_end: more_results = 0
  was_cancelled = 0
  error = 0
  done_count_valid = 1
21:37:42.406490 tds_process_end() state set to TDS_IDLE
21:37:42.406508                  rows_affected = 340
21:37:42.406526 ct_results() process_result_tokens returned 1 (type 4052) 
21:37:42.406547 ct_results() results state = 2
21:37:42.406567 ct_res_info()
21:37:42.406582 ct_res_info(): Number of rows is 340
21:37:42.406600 ct_results()
21:37:42.406615 tds_process_result_tokens() state is COMPLETED
21:37:42.406632 ct_results() process_result_tokens returned 2 (type 4052) 
21:37:42.406802 ct_cmd_drop()
21:37:42.406904 ct_close()
21:37:42.407107 ct_con_drop()
------------------------------
 
2000 (lines 2233-2262)
----------------------
21:26:02.676348 processing row.  column is 3 varint size = 1
21:26:02.676369 processing row.  column size is 8 
21:26:02.676387 clearing column 3 NULL bit
21:26:02.676405 processing row.  column is 4 varint size = 2
21:26:02.676425 processing row.  column size is 7 
21:26:02.676443 clearing column 4 NULL bit
21:26:02.676461 processing row.  column is 5 varint size = 2
21:26:02.676481 processing row.  column size is 16 
21:26:02.676499 clearing column 5 NULL bit
21:26:02.676518 tds_process_end: more_results = 0
  was_cancelled = 0
  error = 0
  done_count_valid = 0
21:26:02.676544 tds_process_end() state set to TDS_IDLE
Received header @ 21:26:02.676573
0000 04 01 00 11 00 3d 01 00-                        |.....=..|

Received packet @ 21:26:02.676625
0000 fd 20 00 fd 00 00 00 00-00                      |ý .ý.... .|

21:26:02.676665 tds_process_end: more_results = 0
  was_cancelled = 1
  error = 0
  done_count_valid = 0
21:26:02.676691 tds_process_end() state set to TDS_IDLE
21:26:02.676724 ct_cmd_drop()
21:26:02.676850 ct_close()
21:26:02.677048 ct_con_drop()

------------------------------
 
I am unsure about the protocol on sending full logs on this mailing list, so 
I will refrain from it until someone lets me know (especially files as big as 
these).
 
Any help will be greatly appreciated.
 
Thank you,
-Adrian Lattke