Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: Create View, varied results depending on row size

    This is the same issue as deinfed in http://www.dbforums.com/t987543.html

    I've done some additional testing and got it down to the below

    SQL2000 DB
    Linked Server to DB2 using client access odbc, and MS OLE DB for ODBC

    Varied results depending on the row size of the views.

    See the below examples.

    CREATE VIEW BLHDR_TEST
    AS
    SELECT STNST
    FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR

    STNST CHAR(25)

    TOTAL ROW WIDTH (25)

    SELECT * FROM BLHDR_TEST

    Returns 20971 rows * 25 = 524,725


    CREATE VIEW BLHDR_TEST
    AS
    SELECT STNSHTNAM, STNST
    FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR

    STNSHTNAM CHAR(12)
    STNST CHAR(25)

    TOTAL ROW WIDTH (37)


    SELECT * FROM BLHDR_TEST

    Returns 14169 rows * 37 = 524,253


    CREATE VIEW BLHDR_TEST
    AS
    SELECT STNADD1, STNSHTNAM, STNST
    FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR

    STNADD1 CHAR(40)
    STNSHTNAM CHAR(12)
    STNST CHAR(25)

    TOTAL ROW WIDTH (77)

    SELECT * FROM BLHDR_TEST

    Returns 6808 rows * 77 = 524,216


    CREATE VIEW BLHDR_TEST
    AS
    SELECT STNCTY,STNADD1, STNSHTNAM, STNST
    FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR

    STNCTY CHAR(25)
    STNADD1 CHAR(40)
    STNSHTNAM CHAR(12)
    STNST CHAR(25)

    TOTAL ROW WIDTH (102)

    SELECT * FROM BLHDR_TEST

    Returns 5140 rows * 102 = 524,280



    Test #1 Returns 20971 rows * 25 = 524,725
    Test #2 Returns 14169 rows * 37 = 524,253
    Test #3 Returns 6808 rows * 77 = 524,216
    Test #4 Returns 5140 rows * 102 = 524,280

    With the similarity of the total byte count returned, I would assume that a buffer or something is being overrun, is this a configuration parameter possibly.


    If I perform the select against the linked table as such

    Select * (for fields list)
    From LURCH_PARADB.S102D4LM.PARADB.BLHDR

    I get all rows returned. The issue only exist when accessing the views that are created against the linked server.


    Any thoughts or ideas are appreciated.

  2. #2
    Join Date
    Jan 2004
    Posts
    22

    Continued testing

    I duplicated the setup on 2000 server, sql2000 and all functions as expected.

    It's related to 2003 server.

    I'm hitting a 512k limit in the result set returned from the views.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •