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,

MikeIt 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|||post the actual code...

I think it's as simple as the number of columns in each is different|||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|||Just to rule out one kind of problem, can you switch to a UNION ALL to see what that does?

-PatP|||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|||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