"Eine" <eine.lundholm@hplmfsab.se> wrote:
>
>Hi
>
>I am working with an interface between two applications that runs on MS
SQL6.5
>and VB5.0 using rdo.
>After a couple of months this program that transfers transactions from database1
>to database2 using both stored procedures and SQL-queries defined in VB-code
>has started to malfunction .
>The error, TDS buffer length too large, is received when accessing database2
>for data to check dates and periodnumbers.
>
>The code looks like this:
>
>Set DB2RDOEnvironment = rdoEngine.rdoEnvironments(0)
>Set DB2RDOConnection = _
> XorRDOEnvironment.OpenConnection(dsName:=DB2ODBCSource, _
> Prompt:=rdDriverNoPrompt, _
> Connect:=ProRDOConnect)
>
>SQL = "SELECT DISTINCT currentyear "
>SQL = SQL & "FROM client "
>Set DB2Client = DB2RDOConnection.OpenResultset(SQL, rdOpenKeyset)
>
>SQL = "SELECT *, "
>SQL = SQL & "(SELECT MIN(periodbegin) "
>SQL = SQL & " FROM period "
>SQL = SQL & " WHERE yearno <= " & DB2Client!currentyear & " AND "
>SQL = SQL & " period > 0 AND locked = 0) AS currentperiod, "
>SQL = SQL & "(SELECT MAX(periodend) "
>SQL = SQL & " FROM period "
>SQL = SQL & " WHERE yearno <= " & DB2Client!currentyear & " AND "
>SQL = SQL & " period > 0 AND locked = 0) AS maxperiod "
>SQL = SQL & "FROM period "
>SQL = SQL & "WHERE yearno <= " & DB2Client!currentyear & " AND period >
0
>"
>SQL = SQL & "AND periodbegin <= '" & GLBookingDate & "' "
>SQL = SQL & "AND periodend >= '" & GLBookingDate & "' "
>Set DB2Period = DB2RDOConnection.OpenResultset(SQL, rdOpenKeyset)
>
>
>When it comes to query #2 it falls over. None of the accessed tables are
>large.
>
>Can anyone give me a hint of what to do??
>
>Thanks in advance
>Eine

Try this..

SQL = "SELECT *, "
SQL = SQL & "(SELECT MIN(PeriodMin.periodbegin) "
SQL = SQL & " FROM period as PeriodMin"
SQL = SQL & " WHERE PeriodMin.yearno <= " & DB2Client!currentyear & " AND
"
SQL = SQL & " PeriodMin.period > 0 AND PeriodMin.locked = 0) AS currentperiod,
"
SQL = SQL & "(SELECT MAX(PeriodMax.periodend) "
SQL = SQL & " FROM period as PeriodMax"
SQL = SQL & " WHERE PeriodMax.yearno <= " & DB2Client!currentyear & " AND
"
SQL = SQL & " PeriodMax.period > 0 AND PeriodMax.locked = 0) AS maxperiod
"
SQL = SQL & "FROM period "
SQL = SQL & "WHERE yearno <= " & DB2Client!currentyear & " AND period > 0
"
SQL = SQL & "AND periodbegin <= '" & GLBookingDate & "' "
SQL = SQL & "AND periodend >= '" & GLBookingDate & "' "
Set DB2Period = DB2RDOConnection.OpenResultset(SQL, rdOpenKeyset)

and it has to work, best of luck..

Dev