Hi,

I have attached SQL 2000 database to Microsoft SQL Server Beta Edition Ver
9.00.1116 and kept Compatibility Level to SQL Server 2000 (80).

While updating a table I am getting error for a specific record

"Creating or altering table 'FakeWorkTable' failed because the minimum row
size would be 8123, including 29 bytes of internal overhead. This exceeds the
maximum allowable table row size of 8094 bytes."

This error is coming from a trigger written on that table. When I am
calculating the length of the table using sp_help, the combined length of all
columns is 7995.This was working fine in SQL Server 2000. Can you please
provide me a solution/workaround on this ?

Thanks,

Can you post create statements for the trigger and table?

MC

[quoted text, click to view]

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbl_PM_ProjectRevision]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [tbl_PM_ProjectRevision]
GO

CREATE TABLE [tbl_PM_ProjectRevision] (
[ProjectCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_PM_ProjectRevision_Type] DEFAULT ('Time & Material'),
[CustomerID] [int] NULL ,
[TotalBid] [money] NULL ,
[ExpectedDuration] [int] NULL ,
[ExpectedStartDate] [datetime] NULL ,
[ExpectedEndDate] [datetime] NULL ,
[TotalInstallments] [int] NULL ,
[Risks] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PercentageComplete] [float] NULL ,
[TotalBilled] [money] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_TotalBilled] DEFAULT (0),
[TotalReceived] [money] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_TotalReceived] DEFAULT (0),
[BillingCycleType] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL CONSTRAINT [DF_tbl_PM_ProjectRevision_BillingCycleType] DEFAULT
('Weekly'),
[EstimatedEfforts] [float] NULL ,
[ActualEfforts] [float] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_ActualEfforts] DEFAULT (0),
[BilledEfforts] [float] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_BilledEfforts] DEFAULT (0),
[TotalHoursToBeBilled] [float] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_TotalHoursToBeBilled] DEFAULT (0),
[ActualStartDate] [datetime] NULL ,
[ActualEndDate] [datetime] NULL ,
[TotalInternalCost] [money] NULL ,
[TotalExternalCost] [money] NULL ,
[CustomerFeedback] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProjectManager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Over] [bit] NOT NULL CONSTRAINT [DF_tbl_PM_ProjectRevision_Over] DEFAULT
(0),
[Billable] [bit] NOT NULL ,
[Rate] [money] NULL ,
[MSProjectFileName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[BankID] [int] NULL ,
[RTSTemplateID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PurchaseOrderDate] [datetime] NULL ,
[PurchaseOrderNumber] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[GLAccountCode] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultCrossRefNo] [int] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_DefaultCrossRefNo] DEFAULT (0),
[RTSProjectID] [int] NULL ,
[SalesLocationID] [int] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_SalesLocationID] DEFAULT (0),
[LocationID] [int] NULL CONSTRAINT [DF_tbl_PM_ProjectRevision_LocationID]
DEFAULT (0),
[SalesPersonID] [int] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_SalesPersonID] DEFAULT (0),
[SalesCommission] [real] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_SalesCommission] DEFAULT (0),
[DepartmentID] [int] NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_DepartmentID] DEFAULT (0),
[TimeSheetAuthenticatedBy] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_TimeSheetAuthenticatedBy] DEFAULT ('Internal'),
[InvoiceGenerationType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF_tbl_PM_ProjectRevision_InvoiceGenerationType] DEFAULT
('Summary'),
[InvoiceInWhichFormat] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL CONSTRAINT [DF_tbl_PM_ProjectRevision_InvoiceInWhichFormat] DEFAULT
('H'),
[TimeSheetAuthenticatorID] [int] NULL ,
[ProjectType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TasksUploadFlag] [bit] NOT NULL CONSTRAINT
[DF_tbl_PM_ProjectRevision_TasksUploadFlag] DEFAULT (0),
[LOC] [int] NULL ,
[FunctionPoint] [int] NULL ,
[ProjectDirectory] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Originalfilename] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProjectGroupID] [int] NULL ,
[IBHistoryOn] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__IBHis__5709AE93] DEFAULT (1),
[ProjectStatus] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__Creat__65039386] DEFAULT (getdate()),
[ProjectTypeID] [int] NULL ,
[GeneratedProjectProjectID] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ProgramCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNumberForAC] [float] NULL ,
[TechnologyID] [int] NULL ,
[DomainID] [int] NULL ,
[LifeCycleID] [int] NULL ,
[InternalOrExternal] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ARAFP] [int] NULL ,
[ADFP] [int] NULL ,
[ACFP] [int] NULL ,
[PercentCalculation] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL CONSTRAINT [DF__tbl_PM_ProjectRevision__Perce__2BCBE181] DEFAULT ('M'),
[HaveSubTaskTypes] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__HaveS__1EFBF648] DEFAULT (0),
[ShareIBwithinProjectGroup] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__Share__0ABFF371] DEFAULT (0),
[BillingCurrencyID] [int] NULL ,
[InvoiceAddressOfCustomer] [varchar] (500) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceToAttentionOf] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[PurchaseOrderValue] [float] NULL ,
[AgeingInterval] [int] NULL ,
[SizeUnitID] [int] NULL ,
[DefectRateThresholdPerc] [float] NULL ,
[SPIThreshold] [float] NULL ,
[EPIThreshold] [float] NULL ,
[IssueLayoutType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__tbl_PM_ProjectRevision__Issue__40C7C9BE] DEFAULT ('R'),
[LayoutID] [int] NULL ,
[SendResponsiblePersonMail] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__SendR__6CE6431E] DEFAULT (0),
[AssignIssueToResponsiblePerson] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__Assig__47559713] DEFAULT (0),
[EnforceConstraints] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__Enfor__12E430D9] DEFAULT (0),
[ApplyEffortDistribution] [bit] NOT NULL CONSTRAINT
[DF__tbl_PM_ProjectRevision__Apply__722D4884] DEFAULT (0),
[ProjectStatusID] [int] NULL ,
[ContractStatusID] [int] NULL ,
[WOStatusID] [int] NULL ,
[JobCategoryID] [int] NULL ,
[EstimationTechID] [int] NULL ,
[ProjectSizeUnitID] [int] NULL ,
[FundedBy] [int] NULL ,
[BillableNo] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerSegmentationId] [int] NULL ,
[ContractType] [int] NULL ,
[ProposalNo] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GroupId] [int] NULL ,
[OfficeID] [int] NULL ,
[BusinessGroupID] [int] NULL ,
[CustomerAddress] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerContactPerson] [nvarchar] (100) COLLATE

Here is the script for the trigger. Even if the trigger is commented out, the
error occurs for a specific record.


I will post the script of the table in another reply to your post, as there
is a constraint on the amount of text that I can put in one single post.

CREATE TRIGGER trg_Upd_tbl_PM_ProjectRevision ON
[dbo].[tbl_PM_ProjectRevision]
INSTEAD OF UPDATE
AS
RETURN
/* BEGIN
/* Update only latest revision */


DECLARE
@ProjectCode NVARCHAR(100),
@ProjectName NVARCHAR(100),
@Type NVARCHAR(100),
@CustomerID INT,
@TotalBid MONEY,
@ExpectedDuration INT,
@ExpectedStartDate DATETIME,
@ExpectedEndDate DATETIME,
@TotalInstallments INT,
@Risks VARCHAR(500),
@PercentageComplete FLOAT,
@TotalBilled MONEY,
@TotalReceived MONEY,
@BillingCycleType NVARCHAR(200),
@EstimatedEfforts FLOAT,
@ActualEfforts FLOAT,
@BilledEfforts FLOAT,
@TotalHoursToBeBilled FLOAT,
@ActualStartDate DATETIME,
@ActualEndDate DATETIME,
@TotalInternalCost MONEY,
@TotalExternalCost MONEY,
@CustomerFeedback VARCHAR(5),
@Description VARCHAR(2000),
@ProjectManager NVARCHAR(100),
@Over BIT,
@Billable BIT,
@Rate MONEY,
@MSProjectFileName NVARCHAR(510),
@BankID INT,
@RTSTemplateID CHAR(10),
@PurchaseOrderDate DATETIME,
@PurchaseOrderNumber NVARCHAR(40),
@GLAccountCode NVARCHAR(32),
@DefaultCrossRefNo INT,
@RTSProjectID INT,
@SalesLocationID INT,
@LocationID INT,
@SalesPersonID INT,
@SalesCommission REAL,
@DepartmentID INT,
@TimeSheetAuthenticatedBy VARCHAR(50),
@InvoiceGenerationType VARCHAR(50),
@InvoiceInWhichFormat VARCHAR(50),
@TimeSheetAuthenticatorID INT,
@ProjectType VARCHAR(50),
@TasksUploadFlag BIT,
@LOC INT,
@FunctionPoint INT,
@ProjectDirectory VARCHAR(5),
@Originalfilename NVARCHAR(100),
@ProjectGroupID INT,
@IBHistoryOn BIT,
@ProjectStatus VARCHAR(100),
@CreatedBy VARCHAR(50),
@CreatedDate DATETIME,
@ProjectTypeID INT,
@GeneratedProjectProjectID VARCHAR(100),
@ProgramCode VARCHAR(50),
@SerialNumberForAC FLOAT,
@TechnologyID INT,
@DomainID INT,
@LifeCycleID INT,
@InternalOrExternal VARCHAR(20),
@ARAFP INT,
@ADFP INT,
@ACFP INT,
@PercentCalculation VARCHAR(3),
@HaveSubTaskTypes BIT,
@ShareIBwithinProjectGroup BIT,
@BillingCurrencyID INT,
@InvoiceAddressOfCustomer VARCHAR(500),
@InvoiceToAttentionOf VARCHAR(100),
@PurchaseOrderValue FLOAT,
@AgeingInterval INT,
@SizeUnitID INT,
@DefectRateThresholdPerc FLOAT,
@SPIThreshold FLOAT,
@EPIThreshold FLOAT,
@IssueLayoutType CHAR(1),
@LayoutID INT,
@SendResponsiblePersonMail BIT,
@AssignIssueToResponsiblePerson BIT,
@EnforceConstraints BIT,
@ApplyEffortDistribution BIT,
@ProjectStatusID INT,
@ContractStatusID INT,
@WOStatusID INT,
@JobCategoryID INT,
@EstimationTechID INT,
@ProjectSizeUnitID INT,
@FundedBy INT,
@BillableNo NVARCHAR(20),
@CustomerSegmentationId INT,
@ContractType INT,
@ProposalNo NVARCHAR(60),
@GroupId INT,
@ResourcePoolID INT, --Added by AbhijitD on 22-Aug-2005
@OfficeID INT,
@BusinessGroupID INT,
@CustomerAddress NVARCHAR(400),
@CustomerContactPerson NVARCHAR(200),
@AuthorizationPONumber NVARCHAR(70),
@ReferenceNo NVARCHAR(60),
@ReferenceDate DATETIME,
@Contract BIT,
@ContractValue MONEY,
@ValidityDate DATETIME,
@ContractPreparedBy VARCHAR(50),
@PersonResponsibleForInvoice INT,
@GlobalProject BIT,
@ModifiedBy NVARCHAR(100),
@ModifiedOn DATETIME,
@MSPUpdate_Percentage BIT,
@ServicesSegmentationID INT,
@WODate DATETIME,
@ContractRequired NVARCHAR(50),
@ProjectAddress NVARCHAR(300),
@ProjectCity NVARCHAR(100),
@ProjectState NVARCHAR(100),
@ProjectCountry INT,
@ProjectPIN NVARCHAR(100),
@RevisionNo INT,
@InvoiceApplicable BIT,
@ShortJobTitle NVARCHAR(20),
@ProposalJobNo NVARCHAR(20),
@ProposalCost FLOAT,
@BaseCurrency INT,
@BaseCurrencyLCV FLOAT,
@OnSiteOrOffShore VARCHAR(20),
@ProjectOrProduct VARCHAR(20),
@BrandID INT,
@CompanyID INT,
@CostCenterID INT,
@CustomerAddressID INT,
@RFIApproverID INT,
@ClientLocationID INT,
@PMIID INT,
@PSSP BIT,
@ProjectSize INT,
@PSPLType INT,
@MSPWorkflowApplicable BIT,
@CreateProjectGroup BIT,
@OverrideDAHrsValidation BIT,
@IsBackDating BIT,
@BackDatingEmployeeID INT,
@IsForwardDating BIT,
@ResourceGroupID INT,
@ResponsiblePersonForIssue INT,
@PracticeGroupID INT,
@CustomFieldText1 VARCHAR(100),
@CustomFieldText2 VARCHAR(100),
@CustomFieldText3 VARCHAR(100),
@CustomFieldText4 VARCHAR(100),
@CustomFieldText5 VARCHAR(100),
@CustomFieldNumeric1 FLOAT,
@CustomFieldNumeric2 FLOAT,
@CustomFieldNumeric3 FLOAT,
@CustomFieldNumeric4 FLOAT,
@CustomFieldNumeric5 FLOAT,
@CustomFieldDate1 DATETIME,
@CustomFieldDate2 DATETIME,
@CustomFieldDate3 DATETIME,
@CustomFieldDate4 DATETIME,
@CustomFieldDate5 DATETIME,
@ProjectID INT,
@RevisionNumber INT,
@IsRevision BIT,
@BaselineStatus CHAR(1),
@SentForApprovalBy VARCHAR(50),
@PMSProjectCode varchar(50) ,
@PSProjectCode varchar(50),
@PSContractID varchar(50),
@Prime bit ,
@ParentProjectID int ,
@CostingTypeID int ,
@SCSProjectID varchar(50),
@DUID int
DECLARE @intProjectDuration INT

-- SELECT TOP 1 @ProjectID = ProjectID FROM INSERTED

DECLARE curProjects CURSOR FOR
SELECT DISTINCT ProjectID FROM INSERTED

OPEN curProjects

FETCH NEXT FROM curProjects INTO @ProjectID

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RevisionNumber = MAX(ISNULL(RevisionNumber, 0)) FROM INSERTED
WHERE ProjectID = @ProjectID

SELECT TOP 1
@ProjectCode = ProjectCode,
@ProjectName = ProjectName,
@Type = Type,
@CustomerID = CustomerID,
@TotalBid = TotalBid,
@ExpectedDuration = ExpectedDuration,
@ExpectedStartDate = ExpectedStartDate,
@ExpectedEndDate = ExpectedEndDate,
@TotalInstallments = TotalInstallments,
@Risks = Risks,
@PercentageComplete = PercentageComplete,
@TotalBilled = TotalBilled,
@TotalReceived = TotalReceived,
@BillingCycleType = BillingCycleType,
@EstimatedEfforts = EstimatedEfforts,
@ActualEfforts = ActualEfforts,
@BilledEfforts = BilledEfforts,
@TotalHoursToBeBilled = TotalHoursToBeBilled,
@ActualStartDate = ActualStartDate,
@ActualEndDate = ActualEndDate,
@TotalInternalCost = TotalInternalCost,
@TotalExternalCost = TotalExternalCost,
@CustomerFeedback = CustomerFeedback,
@Description = [Description],
@ProjectManager = ProjectManager,
@Over = [Over],
@Billable = Billable,
@Rate = Rate,

<B>
Another observation :
If the update operation is done on a single row it works fine , but when it
is two rows it gives that error.
</B>
- Prakash


[quoted text, click to view]