Re: FreeTDS restrictions on query size?

Il giorno lun, 19/07/2010 alle 08.34 -0400, Jason Maur ha scritto:
> Hi all,
> 
> I'm having an issue with a couple separate queries, and I'm wondering if there's a some sort of restriction on the size of the query. When I break down the queries into their logical parts (as much as I can), they run with no problems.
> 
> I've posted one of the queries at the bottom of this message (note that the query I've posted here is just for illustrative purposes--I've sloppily changed all the variable names). I can say this about both queries:
> 
> 
> -          They are long: both over 2000 characters
> 
> -          They both work when run through FlySpeed query tool in windows
> 
> -          They both work when using CentOS 5's stock php-mssql package using "SELECT * FROM OPENQUERY(<link_name>, <my_query>)" --> this connects to a MSSQL 2008 install without encryption.  I'm pretty sure freeTDS isn't being used here, as the server is not in freetds.conf.
> 
> I compiled "freetds-0.83.dev.20100706" from source with the following ./configure command:
> 
> ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld --with-gnutls --libdir=/usr/local/freetds/lib
> 
> Most queries run successfully, but at least 2 don't. Here are the errors I get when running them with tsql:
> 
> Msg 20004, Level 9, State -1, Server OpenClient, Line -1
> Read from the server failed
> 
> and
> 
> Msg 20006, Level 9, State -1, Server OpenClient, Line -1
> Write to the server failed
> tds_submit_query() failed
> 

the server probably found an error in the protocol. There should be no
limit, tsql issue only language queries (that is simple text).

Please send a tdsdump.

freddy77

> I tried the queries on 2 machines: an up-to-date arch install and CentOS 5 -- both with freetds compiled from source as mentioned above.
> 
> Finally, the question is, am I missing something? Any reason why this would be happening? What do those error messages mean?
> 
> Thanks in advance!
> 
> -Jason
> 
> Here's one of the queries that doesn't work. It gives the "Msg 20006" / "Write to server failed" error when run through tsql:
> 
> Select * From
>         (
>         Select
>             e.Number As studlong,
>             e.Number7 As studentno,
>             e.fname,
>             e.lname,
>             p.num AS prognumber,
>             es.sessioncode AS session2,
>             c.num As courseeno,
>             g.num As groupno,
>             c.longtitle
>         From
>             registrations.reg_temp it,
>             students.student e,
>             students.studentsession es,
>             programs.program p,
>             courses.course c,
>             section.section g
>         Where
>             it.IDstudentsession = es.IDstudentsession And
>             es.IDstudent = e.IDstudent And
>             es.IDprogram = p.IDprogram And
>             it.IDcourse = g.IDcourse And
>             it.IDsection = g.IDsection And
>             g.IDcourse = c.IDcourse And
>             (e.Number7 = '0000001' And es.sessioncode = '99999')
>         ) prereg Left Join
>         (
>         Select
>             Day1.Session,
>             Day1.coursee,
>             Day1.Section,
>             Day1.Day,
>             Case
>                 When
>                     Day2.cnt > 1 Then '*'
>                     Else ''
>             End MoreDays,
>             Right('0000' + Convert(VarChar(4),Day1.StartTime), 4) StartTime,
>             Right('0000' + Convert(VarChar(4),Day1.EndTime), 4) EndTime,
>             Day1.Room
>         From
>             (
>             Select
>                 q.*
>             From
>                 (
>                 Select
>                     Row_Number()
>                     Over
>                         (
>                         Partition By g.session,
>                         c.num,
>                         g.num Order By g.session,
>                         c.num,
>                         g.num,
>                         hrt.nday,
>                         hrt.starttime,
>                         hrt.endtime
>                         ) As rowno,
>                     g.session Session,
>                     c.num coursee,
>                     g.num Section,
>                     Case hrt.nday
>                         When '1' Then 'M'
>                         When '2' Then 'T'
>                         When '3' Then 'W'
>                         When '4' Then 'H'
>                         When '5' Then 'F'
>                         When '6' Then 'S'
>                         When '7' Then 'Su'
>                         Else Null End 'Day',
>                     hrt.starttime,
>                     hrt.endtime,
>                     hrl.roomno
>                 From
>                     section.section g,
>                     coursees.course c,
>                     schedules.section hrg
>                 Left Outer Join schedules.classtime hrt On hrg.IDclass = hrt.IDclass
>                 Left Outer Join
>                     (
>                     Select
>                         l.IDclass,
>                         el.number
>                     From
>                         schedules.localclass l,
>                         physicalspace.local el
>                     Where
>                         l.IDlocal = el.IDlocal
>                     ) hrl On hrg.IDclass = hrl.IDclass
>                 Where
>                     g.IDsection = hrg.IDsection And
>                     g.IDcourse = c.IDcourse
>                 ) q
>             Where q.RowNo = 1
>         ) Day1
>         Left Join
>             (
>             Select
>                 g.session Session,
>                 c.num coursee,
>                 g.num Section,
>                 Count(*) cnt
>             From
>                 section.Groupe g,
>                 coursees.course c,
>                 schedules.classsection hrg,
>                 schedules.classtime hrt
>             Where
>                 g.IDsection = hrg.IDsection And
>                 g.IDcourse = c.IDcourse And
>                 hrg.IDRencontre = hrt.IDRencontre
>             Group By
>                 g.session,
>                 c.num,
>                 g.num
>             ) Day2 On
>                 Day1.Session = Day2.Session And
>                 Day1.coursee = Day2.coursee And
>                 Day1.Section = Day2.Section
>         ) sch On
>             prereg.session2 = sch.Session And
>             prereg.courseeno = sch.coursee And
>             prereg.groupno = sch.Section
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds