FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi David,

some other thoughts on this...

The RETURNSTATUS token, as you may know, is reporting the return status of a
stored procedure.
On the face of it, getting this back from the server is counter-intuitive as
you haven't exec'd a stored procedure...

A couple of possibilities arise. 
The first is that FreeTDS is issuing a stored procedure call to the server. 
We do this sometimes, as much functionality on SQL server is accessible only
through some (undocumented) stored procedures.
"prepare" functionality - SQL with placeholders and parameters - is one
occasion when we do this, hence my previous reply.

The second possibility is that the Server is internally executing a stored
procedure as it processes your statement, and sending the return status to
you.
We have seen this behaviour before, and it might well upset an application
that was not expecting to receive this.

One way of finding out for sure what is going on would be to capture a SQL
server profiler trace for your test scenario. 
That would tell us from the servers point of view everything that's
happening with your session.

Are you familiar with running SQL server profiler traces ?

Bill

> -----Original Message-----
> From: David Barnwell [SMTP:david.barnwell AT well.ox.ac.uk]
> Sent: 25 June 2004 20:08
> To:   freetds AT lists.ibiblio.org
> Subject:      [freetds] intermittent empty results
> 
> Hi,
> 
> We have been using freetds successfully for four months on a website that
> displays medical research data. The database server runs SQL Server 2000,
> and the webserver (and database client) is Linux.
> 
> However, the website has an intermittent problem: occasionally, it
> incorrectly returns an empty data set. It usually returns the correct data
> if the user clicks again.
> 
> After a lot of investigation, I have managed to reproduce this problem
> consistently with a simple perl script that prints one column from a table
> using the following SQL:
> 
>     SELECT assay
>     FROM xxxxxx.xxxxx.assay
>     where [assay description] is not null
>     order by 1
> 
> The script seems to be upset by certain server activity. If I drop and
> recreate my table on the server, then the first time I run the script on
> the
> client I just get one line containing "0". Thereafter, it's OK.
> 
> The problem occurs whether I call the script from CGI or from the command
> line. It occurs with TDSVER = 4.2, 7.0 and 8.0. It still occurs if I
> reduce
> the select to a single line (using SELECT top 1).
> 
> This is what the TDS log shows. In the error case, the server always
> returns
> an extra 5 bytes (hex 79 00 00 00 00) at the start of the data stream. The
> log then shows:
> 
>     processing result tokens.  marker is  79(RETURNSTATUS)
>     ct_results() process_result_tokens returned 1 (type 4043)
>     generating return status row. type = 56(int), varint_size 0
> 
> Shortly after that, a ct_cancel is sent and we get repeated calls of
> "tds_process_default_tokens" until the incoming data is exhausted, rather
> than repeated "processing row tokens" that we see in the in the normal
> case.
> 
> In the error case, the log ends with:
> 
>   tds_process_end: more_results = 0
>     was_cancelled = 1
>     error = 0
>     done_count_valid = 0
>   tds_process_end() state set to TDS_IDLE
>   ct_close()
>   ct_con_drop()
>   ct_cmd_drop()
> 
> Any ideas or help would be gratefully received!
> 
> Many thanks,
> David Barnwell
> 
> Database Developer/Systems Administrator
> Wellcome Trust Centre for Human Genetics
> University of Oxford, Roosevelt Drive, Oxford OX3 7BN, UK
> 
> --------------------------------------------------------------------------
> -
> 
> (1) Server software:
> Windows 2000 5.00.2195 (Service Pack 4)
> Microsoft SQL Server  2000 - 8.00.818 (Intel X86)
> 
> (2) Client software:
> Red Hat Enterprise Linux 3
> perl 5.8.0
> Apache 2.0.46
> Apache-DBI-0.94
> DBD-Sybase-1.02
> freetds-0.62.1 (loaded from freetds-0.62.1-1.i586.rpm)
> 
> (3) The server SQL that triggers the problem:
> drop table assay
> select * into assay from copy_assay
> grant all on assay to xxxxxx
> 
> (4) The perl script that shows the problem:
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> 
> $ENV{'TDSVER'} = '4.2';
> $ENV{'TDSDUMP'} = "/tmp/dbtest.log";
> $ENV{'TDSDUMPCONFIG'} = "/tmp/dbtest.config";
> 
> my $HOST     = 'xxxxxx.xxx.xxx';
> my $USER     = 'xxxxxx';
> my $PASSWORD = 'xxxxxxxxxxx';
> 
> my $dsn = "DBI:Sybase:server=$HOST" ;
> 
> my $sql = "
>   SELECT top 1 assay
>   FROM xxxxxx.xxxxx.assay
>   where [assay description] is not null
>   order by 1
> ";
> 
> my $dbh = DBI->connect($dsn, $USER, $PASSWORD) or die 'connect';
> my $sth = $dbh->prepare($sql);
> $sth->execute;
> 
> while (my @row = $sth->fetchrow_array) {
>   print join "  ", @row;
>   print "\n";
> }
> 
> $sth->finish;
> $dbh->disconnect;
> 
> --------------------------------------------------------------------------
> -
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================