Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: Row Length Error with View

    I'm having this problem with SQL Server 2000...

    Sample query in view definition:

    SELECT somecol1 FROM sometable1
    UNION
    SELECT somecol2 FROM sometable2

    I'm returning more columns in the SELECT than I've put in the sample and all the datatypes match for each column. But...

    I get the row error when the view is run. If I run the first part in it's own results are returned. If I run the second on it's own I get results. I only get the error when they are UNIONed. There's no ordering or grouping.

    Why would each of them run individually but not UNIONed together?

    Also, if I remove ('' instead of table.column) one of the larger nvarchar columns from the first or second query, the UNIONed statement returns results just fine.

    Any help would be appreciated.

    Thanks,

    Mike

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is quite easy to create a result set row that is larger than the maximum table row size. One simple way to do this is using character concatenation operators to produce some of your columns.

    If you do that, you create a result set that can't participate in a UNION because the server can't store that result set into the intermediate table needed to create the final UNION result set.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    post the actual code...

    I think it's as simple as the number of columns in each is different
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Posts
    5
    It can't be a problem with the number of columns in the definition since the view gets created fine and in some cases, results are returned (for example, if I replace AppDB.dbo.Milestones.Description MilestoneDescription with '' MilestoneDescription). But, I do know that any one row in the AppDB.dbo.Milestones table will not exceed the row limit of 8096. And, not doing any concatenation in the select or other string manipulation.

    So, once again, I can run the first part on it's own without a problem, the second on it's own without a problem but it throws the error when the view is run or when I run the union as a straight statement.

    Also, I thought the row limit was only when trying to insert or update(?)...

    Thanks in advance.

    Mike


    Here's the actual code...

    CREATE view VW_PO_BUDGET as
    SELECT dbo.Programs.ProgramID, dbo.Programs.ProgramName, dbo.Steps.StepID, dbo.Steps.StepName, AppDB.dbo.Agreements.CoreProjectID,
    dbo.Projects.ProjectNumber, dbo.Projects.ProjectName, AppDB.dbo.ProjectExpenseBudget.ProjectVersion,
    AppDB.dbo.ProjectExpenseBudget.Amount, AppDB.dbo.ProjectExpenses.ExpenseCategoryID CategoryID,
    LU_CATEGORIES.Lookupvalue Category,
    AppDB.dbo.ProjectExpenses.SubExpenseID SubCategoryID, LU_SUBCATEGORIES.Lookupvalue SubCategory,
    AppDB.dbo.ProjectExpenses.ExpenseTypeID TypeID, LU_TYPES.Lookupvalue Type,
    AppDB.dbo.ProjectExpenses.ContributorID, AppDB.dbo.Organizations.OrganizationLegalName Contributor,
    AppDB.dbo.ProjectExpenses.Description ItemDescription, AppDB.dbo.Milestones.MilestoneID,
    AppDB.dbo.Milestones.MilestoneNumber, AppDB.dbo.Milestones.Title, AppDB.dbo.Milestones.Description MilestoneDescription,
    AppDB.dbo.Milestones.StartDate, AppDB.dbo.Milestones.EndDate, AppDB.dbo.Milestones.MandatoryMilestone,
    AppDB.dbo.ProjectExpenseBudget.DateCreated, AppDB.dbo.ProjectExpenseBudget.DateUpdated, AppDB.dbo.ProjectExpenseBudget.UpdatedBy, AppDB.dbo.ProjectExpenseBudget.CreatedBy
    , 'EXPENSE' EntryType, LU_CATEGORIES.LocaleID CategoryLocale, LU_SUBCATEGORIES.LocaleID SubCategoryLocale, LU_TYPES.LocaleID TypeLocale
    FROM AppDB.dbo.ProjectExpenseBudget INNER JOIN
    AppDB.dbo.ProjectExpenses ON AppDB.dbo.ProjectExpenseBudget.ExpenseID = AppDB.dbo.ProjectExpenses.ExpenseID AND
    AppDB.dbo.ProjectExpenseBudget.ProjectID = AppDB.dbo.ProjectExpenses.ProjectID AND
    AppDB.dbo.ProjectExpenseBudget.ProjectVersion = AppDB.dbo.ProjectExpenses.ProjectVersion AND
    AppDB.dbo.ProjectExpenseBudget.StepID = AppDB.dbo.ProjectExpenses.StepID INNER JOIN
    AppDB.dbo.Milestones ON AppDB.dbo.ProjectExpenseBudget.MilestoneID = AppDB.dbo.Milestones.MilestoneID AND
    AppDB.dbo.ProjectExpenses.ProjectID = AppDB.dbo.Milestones.ProjectID AND
    AppDB.dbo.ProjectExpenses.ProjectVersion = AppDB.dbo.Milestones.ProjectVersion AND
    AppDB.dbo.ProjectExpenses.StepID = AppDB.dbo.Milestones.StepID INNER JOIN
    AppDB.dbo.Agreements ON AppDB.dbo.ProjectExpenseBudget.ProjectID = AppDB.dbo.Agreements.ProjectID AND
    AppDB.dbo.ProjectExpenseBudget.ProjectVersion = AppDB.dbo.Agreements.ProjectVersion AND
    AppDB.dbo.ProjectExpenseBudget.StepID = AppDB.dbo.Agreements.StepID INNER JOIN
    dbo.Programs ON AppDB.dbo.Agreements.ProgramId = dbo.Programs.ProgramID INNER JOIN
    dbo.Steps ON dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Steps.StepType = 'Award' AND
    dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Programs.ProgramID = dbo.Steps.ProgramID AND
    AppDB.dbo.ProjectExpenses.StepID = dbo.Steps.StepID INNER JOIN
    dbo.Projects ON AppDB.dbo.Agreements.CoreProjectId = dbo.Projects.ProjectID AND dbo.Programs.ProgramID = dbo.Projects.ProgramID
    LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_CATEGORIES ON LU_CATEGORIES.lookupvalueid = AppDB.dbo.ProjectExpenses.ExpenseCategoryID
    and LU_CATEGORIES.lookupname = 'ExpenseAccounts'
    LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_SUBCATEGORIES ON LU_SUBCATEGORIES.lookupvalueid = AppDB.dbo.ProjectExpenses.SubExpenseID
    and LU_SUBCATEGORIES.lookupname = 'SubExpenses'
    LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_TYPES ON LU_TYPES.lookupvalueid = AppDB.dbo.ProjectExpenses.ExpenseTypeID
    and LU_TYPES.lookupname = 'ExpenseTypes'
    LEFT OUTER JOIN AppDB.dbo.Organizations ON AppDB.dbo.ProjectExpenses.ContributorID = AppDB.dbo.Organizations.StakeholderID
    union
    SELECT dbo.Programs.ProgramID, dbo.Programs.ProgramName, dbo.Steps.StepID, dbo.Steps.StepName, AppDB.dbo.Agreements.CoreProjectID,
    dbo.Projects.ProjectNumber, dbo.Projects.ProjectName, AppDB.dbo.ProjectFundingBudget.ProjectVersion,
    AppDB.dbo.ProjectFundingBudget.Amount, AppDB.dbo.FundingSources.FundingSourceCategoryID CategoryID,
    LU_CATEGORIES.Lookupvalue Category,
    NULL SubCategoryID, NULL SubCategory,
    AppDB.dbo.FundingSources.FundingTypeID TypeID, LU_TYPES.Lookupvalue Type,
    AppDB.dbo.FundingSources.ContributorID, AppDB.dbo.Organizations.OrganizationLegalName Contributor,
    AppDB.dbo.FundingSources.Description ItemDescription, AppDB.dbo.Milestones.MilestoneID,
    AppDB.dbo.Milestones.MilestoneNumber, AppDB.dbo.Milestones.Title, AppDB.dbo.Milestones.Description MilestoneDescription,
    AppDB.dbo.Milestones.StartDate, AppDB.dbo.Milestones.EndDate, AppDB.dbo.Milestones.MandatoryMilestone,
    AppDB.dbo.ProjectFundingBudget.DateCreated, AppDB.dbo.ProjectFundingBudget.DateUpdated, AppDB.dbo.ProjectFundingBudget.UpdatedBy, AppDB.dbo.ProjectFundingBudget.CreatedBy
    , 'FUNDING' as EntryType, LU_CATEGORIES.LocaleID CategoryLocale, NULL SubCategoryLocale, LU_TYPES.LocaleID TypeLocale
    FROM AppDB.dbo.ProjectFundingBudget INNER JOIN
    AppDB.dbo.FundingSources ON AppDB.dbo.ProjectFundingBudget.FundingSourceID = AppDB.dbo.FundingSources.FundingSourceID AND
    AppDB.dbo.ProjectFundingBudget.ProjectID = AppDB.dbo.FundingSources.ProjectID AND
    AppDB.dbo.ProjectFundingBudget.ProjectVersion = AppDB.dbo.FundingSources.ProjectVersion AND
    AppDB.dbo.ProjectFundingBudget.StepID = AppDB.dbo.FundingSources.StepID INNER JOIN
    AppDB.dbo.Milestones ON AppDB.dbo.ProjectFundingBudget.MilestoneID = AppDB.dbo.Milestones.MilestoneID AND
    AppDB.dbo.FundingSources.ProjectID = AppDB.dbo.Milestones.ProjectID AND
    AppDB.dbo.FundingSources.ProjectVersion = AppDB.dbo.Milestones.ProjectVersion AND
    AppDB.dbo.FundingSources.StepID = AppDB.dbo.Milestones.StepID INNER JOIN
    AppDB.dbo.Agreements ON AppDB.dbo.ProjectFundingBudget.ProjectID = AppDB.dbo.Agreements.ProjectID AND
    AppDB.dbo.ProjectFundingBudget.ProjectVersion = AppDB.dbo.Agreements.ProjectVersion AND
    AppDB.dbo.ProjectFundingBudget.StepID = AppDB.dbo.Agreements.StepID INNER JOIN
    dbo.Programs ON AppDB.dbo.Agreements.ProgramId = dbo.Programs.ProgramID INNER JOIN
    dbo.Steps ON dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Steps.StepType = 'Award' AND
    dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Programs.ProgramID = dbo.Steps.ProgramID AND
    AppDB.dbo.FundingSources.StepID = dbo.Steps.StepID INNER JOIN
    dbo.Projects ON AppDB.dbo.Agreements.CoreProjectId = dbo.Projects.ProjectID AND dbo.Programs.ProgramID = dbo.Projects.ProgramID
    LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_CATEGORIES ON LU_CATEGORIES.lookupvalueid = AppDB.dbo.FundingSources.FundingSourceCategoryID
    and LU_CATEGORIES.lookupname = 'Funds'
    LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_TYPES ON LU_TYPES.lookupvalueid = AppDB.dbo.FundingSources.FundingTypeID
    and LU_TYPES.lookupname = 'FundTypes'
    LEFT OUTER JOIN AppDB.dbo.Organizations ON AppDB.dbo.FundingSources.ContributorID = AppDB.dbo.Organizations.StakeholderID

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to rule out one kind of problem, can you switch to a UNION ALL to see what that does?

    -PatP

  6. #6
    Join Date
    Oct 2003
    Posts
    5
    UNION ALL seemed to do if I run the query without an ORDER BY. Any way to get around that? Still not understanding why this error is coming from a SELECT. Didn't think there was that limitation when querying. Does that mean that any SELECT that I put together must have a row length of less than 8096? That's pretty limiting if that is the case.

    Thanks,

    Mike

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This gets a little complicated to explain in terms of what the code is actually doing, but the short answer comes from the Relational Algebra that ought to be the cornerstone of any relational database... A view ought to express what should be shown (which rows should appear in the result set), but not how it should be shown (sequencing, formatting, etc.). Until a view is materialized into a result set, an order is logically irrelevant.

    The fine folks at Sybase allowed views to specify an order, and Microsoft has carried on that functionality at the syntactic level even though some of the "inner workings" of the engine don't support it very well. From a logical perspective, they shouldn't allow you to specify an order for a view, but since they do permit it, they really ought to do it 100% (or not at all).

    -PatP

Posting Permissions

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