8K/page limit

Hi there,
Does the 8K/page (row) limit also involve views? We're trying to insert a
record into a view (via nested instead of insert triggers) and the query
results in:
Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line 1
String or binary data would be truncated.
The statement has been terminated.
The view is made up of tables of which none are exceeding the 8K limiet. The
query does not pass [n][var]char values bigger than defined on table level.
Any clues?
Thanks,
SLE"SLE" <info@.NOSPAM.dataworx.be> wrote...
> Does the 8K/page (row) limit also involve views? We're trying to insert a
> record into a view (via nested instead of insert triggers) and the query
> results in:
> Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD, Line
1
> String or binary data would be truncated.
> The statement has been terminated.
> The view is made up of tables of which none are exceeding the 8K limiet.
The
> query does not pass [n][var]char values bigger than defined on table
level.
>
FYI:
Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
insert (and all columns are populated correctly) but we don't feel quite
comfortable yet :-|
I've checked books online: there are no aggregate functions involved, nor
binary or varbinary data, nor distributed queries.
Any thoughts?
Thanks,
SLE|||The error indicates that you _are_ passing character values that are larger
than the columns they are stored in. If the row size was the problem you
would get a different error message. Are you not accidentally passing a char
to a varchar column? I assume you have checked your code already, but the
only explanation I can give at this moment is that you have missed
something. If you're sure you haven't can you please post your code so we
can have a look through it?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:OqGhJqOrDHA.2688@.TK2MSFTNGP09.phx.gbl...
> "SLE" <info@.NOSPAM.dataworx.be> wrote...
> >
> > Does the 8K/page (row) limit also involve views? We're trying to insert
a
> > record into a view (via nested instead of insert triggers) and the query
> > results in:
> >
> > Server: Msg 8152, Level 16, State 9, Procedure CIM_LogicalDevice_UPD,
Line
> 1
> > String or binary data would be truncated.
> > The statement has been terminated.
> >
> > The view is made up of tables of which none are exceeding the 8K limiet.
> The
> > query does not pass [n][var]char values bigger than defined on table
> level.
> >
> FYI:
> Preceding the T-SQL with a "SET ANSI_WARNINGS OFF" results in a succesful
> insert (and all columns are populated correctly) but we don't feel quite
> comfortable yet :-|
> I've checked books online: there are no aggregate functions involved, nor
> binary or varbinary data, nor distributed queries.
> Any thoughts?
> Thanks,
> SLE
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> The error indicates that you _are_ passing character values that are
larger
> than the columns they are stored in. If the row size was the problem you
> would get a different error message. Are you not accidentally passing a
char
> to a varchar column? I assume you have checked your code already, but the
> only explanation I can give at this moment is that you have missed
> something. If you're sure you haven't can you please post your code so we
> can have a look through it?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Okay, a bit lengthy, but here we go. From Profiler:
SET ANSI_WARNINGS OFF
exec sp_executesql
N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
[DriveIntegrity], [FileSystemFlags],
[FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
[MediaLoaded], [MediaType],
[MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
[SCSIPort], [SCSITargetId],
[Size], [TransferRate], [VolumeName], [VolumeSerialNumber], [Capabilities],
[CapabilityDescriptions],
[ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
[MaxMediaSize], [DefaultBlockSize],
[MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
[Security], [LastCleaned],
[MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
[MountCount], [TimeOfLastMount],
[TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning], [UnitsUsed],
[PowerManagementCapabilities],
[OtherIdentifyingInfo], [IdentifyingDescriptions], [AdditionalAvailability],
[SystemCreationClassName],
[SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
[Availability], [StatusInfo],
[LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
[TotalPowerOnHours], [MaxQuiesceTime],
[EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
[OperationalStatus],
[StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
[Description], [ElementName],
[LastModifiedBy], [ToArchive], [TimeOfCreation])
VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
@.FileSystemFlagsExValue, @.IdValue,
@.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
@.MfrAssignedRevisionLevelValue,
@.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
@.SCSITargetIdValue,
@.SizeValue, @.TransferRateValue, @.VolumeNameValue, @.VolumeSerialNumberValue,
@.CapabilitiesValue,
@.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
@.CompressionMethodValue,
@.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
@.MaxBlockSizeValue,
@.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
@.LastCleanedValue, @.MaxAccessTimeValue,
@.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
@.MountCountValue, @.TimeOfLastMountValue,
@.TotalMountTimeValue, @.UnitsDescriptionValue, @.MaxUnitsBeforeCleaningValue,
@.UnitsUsedValue,
@.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
@.IdentifyingDescriptionsValue,
@.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
@.SystemNameValue, @.CreationClassNameValue,
@.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
@.LastErrorCodeValue, @.ErrorDescriptionValue,
0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
@.EnabledStateValue,
@.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
@.OperationalStatusValue,
@.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
@.DescriptionValue,
@.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
int,@.FileSystemFlagsExValue bigint,
@.IdValue varchar(2),@.ManufacturerValue
varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue varchar(6),
@.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
varchar(8000),@.SCSIBusValue bigint,
@.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
int,@.SizeValue bigint,
@.TransferRateValue float,@.VolumeNameValue
varchar(8),@.VolumeSerialNumberValue varchar(8),
@.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
varchar(8000),@.ErrorMethodologyValue varchar(8000),
@.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
bigint,@.MaxMediaSizeValue bigint,
@.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
bigint,@.MediaIsLockedValue bigint,
@.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
bigint,@.UncompressedDataRateValue bigint,
@.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
bigint,@.TimeOfLastMountValue datetime,
@.TotalMountTimeValue bigint,@.UnitsDescriptionValue
varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
@.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
@.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
varchar(8000),
@.SystemCreationClassNameValue varchar(20),@.SystemNameValue
varchar(6),@.CreationClassNameValue varchar(16),
@.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
int,@.LastErrorCodeValue bigint,
@.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
bigint,@.TotalPowerOnHoursValue bigint,
@.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
varchar(8000),
@.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
varchar(8000),
@.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
varchar(2),
@.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
varchar(8000),
@.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
@.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue = 0,
@.FileSystemFlagsExValue = 0,
@.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
@.MaximumComponentLengthValue = 110,
@.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
@.RevisionLevelValue = '',
@.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
@.SCSITargetIdValue = 0,
@.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
@.VolumeNameValue = 'BTS2004B',
@.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
@.CapabilityDescriptionsValue = NULL,
@.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
@.NumberOfMediaSupportedValue = 0,
@.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue = 0,
@.MinBlockSizeValue = 0,
@.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue = NULL,
@.MaxAccessTimeValue = NULL,
@.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue =NULL, @.MountCountValue = NULL,
@.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
@.UnitsDescriptionValue = NULL,
@.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
@.PowerManagementCapabilitiesValue = NULL,
@.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
@.AdditionalAvailabilityValue = NULL,
@.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue ='WS9843',
@.CreationClassNameValue = 'Win32_CDROMDrive',
@.DeviceIDValue ='IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
0',
@.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
@.ErrorDescriptionValue = '',
@.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
@.MaxQuiesceTimeValue = NULL,
@.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
@.RequestedStateValue = NULL,
@.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
@.StatusDescriptionsValue = NULL,
@.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK', @.CaptionValue
= 'HL-DT-ST DVD-ROM GDR8161B',
@.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
@.LastModifiedByValue = 'DOSIM000\SIDLECS',
@.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
And these are the affected tables contained within the view (from bottom to
top):
CREATE TABLE [dbo].[Win32_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DriveIntegrity] [bit] NULL ,
[FileSystemFlags] [int] NULL ,
[FileSystemFlagsEx] [bigint] NULL ,
[Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaximumComponentLength] [bigint] NULL ,
[MediaLoaded] [bit] NULL ,
[MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
NULL ,
[RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[SCSIBus] [bigint] NULL ,
[SCSILogicalUnit] [int] NULL ,
[SCSIPort] [int] NULL ,
[SCSITargetId] [int] NULL ,
[Size] [bigint] NULL ,
[TransferRate] [float] NULL ,
[VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_CDROMDrive] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[NumberOfMediaSupported] [bigint] NULL ,
[MaxMediaSize] [bigint] NULL ,
[DefaultBlockSize] [bigint] NULL ,
[MaxBlockSize] [bigint] NULL ,
[MinBlockSize] [bigint] NULL ,
[NeedsCleaning] [bit] NULL ,
[MediaIsLocked] [bit] NULL ,
[Security] [int] NULL ,
[LastCleaned] [datetime] NULL ,
[MaxAccessTime] [bigint] NULL ,
[UncompressedDataRate] [bigint] NULL ,
[LoadTime] [bigint] NULL ,
[UnloadTime] [bigint] NULL ,
[MountCount] [bigint] NULL ,
[TimeOfLastMount] [datetime] NULL ,
[TotalMountTime] [bigint] NULL ,
[UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[MaxUnitsBeforeCleaning] [bigint] NULL ,
[UnitsUsed] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalDevice] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[PowerManagementCapabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL ,
[AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
[SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL
,
[SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[PowerManagementSupported] [bit] NULL ,
[Availability] [int] NULL ,
[StatusInfo] [int] NULL ,
[LastErrorCode] [bigint] NULL ,
[ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ErrorCleared] [bit] NULL ,
[PowerOnHours] [bigint] NULL ,
[TotalPowerOnHours] [bigint] NULL ,
[MaxQuiesceTime] [bigint] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[EnabledState] [int] NULL ,
[OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[RequestedState] [int] NULL ,
[EnabledDefault] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CIM_LogicalElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[InstallDate] [datetime] NULL ,
[Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
[GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
[Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
[ToArchive] [bit] NULL ,
[TimeOfCreation] [datetime] NULL ,
[TimeOfModification] [datetime] NOT NULL
) ON [PRIMARY]|||Can you also post the definition of and trigger on
DATAVIEW_Win32_CDROMDrive?
--
Jacco Schalkwijk
SQL Server MVP
"SLE" <info@.NOSPAM.dataworx.be> wrote in message
news:%23J0t3FQrDHA.648@.TK2MSFTNGP11.phx.gbl...
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> > The error indicates that you _are_ passing character values that are
> larger
> > than the columns they are stored in. If the row size was the problem you
> > would get a different error message. Are you not accidentally passing a
> char
> > to a varchar column? I assume you have checked your code already, but
the
> > only explanation I can give at this moment is that you have missed
> > something. If you're sure you haven't can you please post your code so
we
> > can have a look through it?
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> Okay, a bit lengthy, but here we go. From Profiler:
> SET ANSI_WARNINGS OFF
> exec sp_executesql
> N'INSERT INTO [DATAVIEW_Win32_CDROMDrive] ([GUID], [Drive],
> [DriveIntegrity], [FileSystemFlags],
> [FileSystemFlagsEx], [Id], [Manufacturer], [MaximumComponentLength],
> [MediaLoaded], [MediaType],
> [MfrAssignedRevisionLevel], [RevisionLevel], [SCSIBus], [SCSILogicalUnit],
> [SCSIPort], [SCSITargetId],
> [Size], [TransferRate], [VolumeName], [VolumeSerialNumber],
[Capabilities],
> [CapabilityDescriptions],
> [ErrorMethodology], [CompressionMethod], [NumberOfMediaSupported],
> [MaxMediaSize], [DefaultBlockSize],
> [MaxBlockSize], [MinBlockSize], [NeedsCleaning], [MediaIsLocked],
> [Security], [LastCleaned],
> [MaxAccessTime], [UncompressedDataRate], [LoadTime], [UnloadTime],
> [MountCount], [TimeOfLastMount],
> [TotalMountTime], [UnitsDescription], [MaxUnitsBeforeCleaning],
[UnitsUsed],
> [PowerManagementCapabilities],
> [OtherIdentifyingInfo], [IdentifyingDescriptions],
[AdditionalAvailability],
> [SystemCreationClassName],
> [SystemName], [CreationClassName], [DeviceID], [PowerManagementSupported],
> [Availability], [StatusInfo],
> [LastErrorCode], [ErrorDescription], [ErrorCleared], [PowerOnHours],
> [TotalPowerOnHours], [MaxQuiesceTime],
> [EnabledState], [OtherEnabledState], [RequestedState], [EnabledDefault],
> [OperationalStatus],
> [StatusDescriptions], [InstallDate], [Name], [Status], [Caption],
> [Description], [ElementName],
> [LastModifiedBy], [ToArchive], [TimeOfCreation])
> VALUES (@.GUIDValue, @.DriveValue, 1, @.FileSystemFlagsValue,
> @.FileSystemFlagsExValue, @.IdValue,
> @.ManufacturerValue, @.MaximumComponentLengthValue, 1, @.MediaTypeValue,
> @.MfrAssignedRevisionLevelValue,
> @.RevisionLevelValue, @.SCSIBusValue, @.SCSILogicalUnitValue, @.SCSIPortValue,
> @.SCSITargetIdValue,
> @.SizeValue, @.TransferRateValue, @.VolumeNameValue,
@.VolumeSerialNumberValue,
> @.CapabilitiesValue,
> @.CapabilityDescriptionsValue, @.ErrorMethodologyValue,
> @.CompressionMethodValue,
> @.NumberOfMediaSupportedValue, @.MaxMediaSizeValue, @.DefaultBlockSizeValue,
> @.MaxBlockSizeValue,
> @.MinBlockSizeValue, 0, @.MediaIsLockedValue, @.SecurityValue,
> @.LastCleanedValue, @.MaxAccessTimeValue,
> @.UncompressedDataRateValue, @.LoadTimeValue, @.UnloadTimeValue,
> @.MountCountValue, @.TimeOfLastMountValue,
> @.TotalMountTimeValue, @.UnitsDescriptionValue,
@.MaxUnitsBeforeCleaningValue,
> @.UnitsUsedValue,
> @.PowerManagementCapabilitiesValue, @.OtherIdentifyingInfoValue,
> @.IdentifyingDescriptionsValue,
> @.AdditionalAvailabilityValue, @.SystemCreationClassNameValue,
> @.SystemNameValue, @.CreationClassNameValue,
> @.DeviceIDValue, 0, @.AvailabilityValue, @.StatusInfoValue,
> @.LastErrorCodeValue, @.ErrorDescriptionValue,
> 0, @.PowerOnHoursValue, @.TotalPowerOnHoursValue, @.MaxQuiesceTimeValue,
> @.EnabledStateValue,
> @.OtherEnabledStateValue, @.RequestedStateValue, @.EnabledDefaultValue,
> @.OperationalStatusValue,
> @.StatusDescriptionsValue, null, @.NameValue, @.StatusValue, @.CaptionValue,
> @.DescriptionValue,
> @.ElementNameValue, @.LastModifiedByValue, 0, @.TimeOfCreationValue)',
> N'@.GUIDValue varchar(13),@.DriveValue varchar(2),@.FileSystemFlagsValue
> int,@.FileSystemFlagsExValue bigint,
> @.IdValue varchar(2),@.ManufacturerValue
> varchar(24),@.MaximumComponentLengthValue bigint,@.MediaTypeValue
varchar(6),
> @.MfrAssignedRevisionLevelValue varchar(8000),@.RevisionLevelValue
> varchar(8000),@.SCSIBusValue bigint,
> @.SCSILogicalUnitValue int,@.SCSIPortValue int,@.SCSITargetIdValue
> int,@.SizeValue bigint,
> @.TransferRateValue float,@.VolumeNameValue
> varchar(8),@.VolumeSerialNumberValue varchar(8),
> @.CapabilitiesValue varchar(3),@.CapabilityDescriptionsValue
> varchar(8000),@.ErrorMethodologyValue varchar(8000),
> @.CompressionMethodValue varchar(8000),@.NumberOfMediaSupportedValue
> bigint,@.MaxMediaSizeValue bigint,
> @.DefaultBlockSizeValue bigint,@.MaxBlockSizeValue bigint,@.MinBlockSizeValue
> bigint,@.MediaIsLockedValue bigint,
> @.SecurityValue int,@.LastCleanedValue datetime,@.MaxAccessTimeValue
> bigint,@.UncompressedDataRateValue bigint,
> @.LoadTimeValue bigint,@.UnloadTimeValue bigint,@.MountCountValue
> bigint,@.TimeOfLastMountValue datetime,
> @.TotalMountTimeValue bigint,@.UnitsDescriptionValue
> varchar(8000),@.MaxUnitsBeforeCleaningValue bigint,
> @.UnitsUsedValue bigint,@.PowerManagementCapabilitiesValue
> varchar(8000),@.OtherIdentifyingInfoValue varchar(8000),
> @.IdentifyingDescriptionsValue varchar(8000),@.AdditionalAvailabilityValue
> varchar(8000),
> @.SystemCreationClassNameValue varchar(20),@.SystemNameValue
> varchar(6),@.CreationClassNameValue varchar(16),
> @.DeviceIDValue varchar(76),@.AvailabilityValue int,@.StatusInfoValue
> int,@.LastErrorCodeValue bigint,
> @.ErrorDescriptionValue varchar(8000),@.PowerOnHoursValue
> bigint,@.TotalPowerOnHoursValue bigint,
> @.MaxQuiesceTimeValue bigint,@.EnabledStateValue int,@.OtherEnabledStateValue
> varchar(8000),
> @.RequestedStateValue int,@.EnabledDefaultValue int,@.OperationalStatusValue
> varchar(8000),
> @.StatusDescriptionsValue varchar(8000),@.NameValue varchar(25),@.StatusValue
> varchar(2),
> @.CaptionValue varchar(25),@.DescriptionValue varchar(12),@.ElementNameValue
> varchar(8000),
> @.LastModifiedByValue varchar(16),@.TimeOfCreationValue datetime',
> @.GUIDValue = '\_]+#)85E0)FE', @.DriveValue = 'D:', @.FileSystemFlagsValue =0,
> @.FileSystemFlagsExValue = 0,
> @.IdValue = 'D:', @.ManufacturerValue = '(Standard CD-ROM drives)',
> @.MaximumComponentLengthValue = 110,
> @.MediaTypeValue = 'CD-ROM', @.MfrAssignedRevisionLevelValue = '',
> @.RevisionLevelValue = '',
> @.SCSIBusValue = 0, @.SCSILogicalUnitValue = 0, @.SCSIPortValue = 1,
> @.SCSITargetIdValue = 0,
> @.SizeValue = 272316416, @.TransferRateValue = 1.348837209302326e+016,
> @.VolumeNameValue = 'BTS2004B',
> @.VolumeSerialNumberValue = '6B057E49', @.CapabilitiesValue = '3,7',
> @.CapabilityDescriptionsValue = NULL,
> @.ErrorMethodologyValue = '', @.CompressionMethodValue = '',
> @.NumberOfMediaSupportedValue = 0,
> @.MaxMediaSizeValue = 0, @.DefaultBlockSizeValue = 0, @.MaxBlockSizeValue =0,
> @.MinBlockSizeValue = 0,
> @.MediaIsLockedValue = NULL, @.SecurityValue = NULL, @.LastCleanedValue =NULL,
> @.MaxAccessTimeValue = NULL,
> @.UncompressedDataRateValue = NULL, @.LoadTimeValue = NULL, @.UnloadTimeValue
=> NULL, @.MountCountValue = NULL,
> @.TimeOfLastMountValue = NULL, @.TotalMountTimeValue = NULL,
> @.UnitsDescriptionValue = NULL,
> @.MaxUnitsBeforeCleaningValue = NULL, @.UnitsUsedValue = NULL,
> @.PowerManagementCapabilitiesValue = NULL,
> @.OtherIdentifyingInfoValue = NULL, @.IdentifyingDescriptionsValue = NULL,
> @.AdditionalAvailabilityValue = NULL,
> @.SystemCreationClassNameValue = 'Win32_ComputerSystem', @.SystemNameValue => 'WS9843',
> @.CreationClassNameValue = 'Win32_CDROMDrive',
> @.DeviceIDValue =>
'IDE\CDROMHL-DT-ST_DVD-ROM_GDR8161B_______________0037____\5&27FFD2F6&0&0.0.
> 0',
> @.AvailabilityValue = 3, @.StatusInfoValue = 0, @.LastErrorCodeValue = 0,
> @.ErrorDescriptionValue = '',
> @.PowerOnHoursValue = NULL, @.TotalPowerOnHoursValue = NULL,
> @.MaxQuiesceTimeValue = NULL,
> @.EnabledStateValue = NULL, @.OtherEnabledStateValue = NULL,
> @.RequestedStateValue = NULL,
> @.EnabledDefaultValue = NULL, @.OperationalStatusValue = NULL,
> @.StatusDescriptionsValue = NULL,
> @.NameValue = 'HL-DT-ST DVD-ROM GDR8161B', @.StatusValue = 'OK',
@.CaptionValue
> = 'HL-DT-ST DVD-ROM GDR8161B',
> @.DescriptionValue = 'CD-ROM Drive', @.ElementNameValue = NULL,
> @.LastModifiedByValue = 'DOSIM000\SIDLECS',
> @.TimeOfCreationValue = 'Nov 17 2003 9:02:09:680AM'
> And these are the affected tables contained within the view (from bottom
to
> top):
> CREATE TABLE [dbo].[Win32_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Drive] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [DriveIntegrity] [bit] NULL ,
> [FileSystemFlags] [int] NULL ,
> [FileSystemFlagsEx] [bigint] NULL ,
> [Id] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [Manufacturer] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaximumComponentLength] [bigint] NULL ,
> [MediaLoaded] [bit] NULL ,
> [MediaType] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MfrAssignedRevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS
> NULL ,
> [RevisionLevel] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [SCSIBus] [bigint] NULL ,
> [SCSILogicalUnit] [int] NULL ,
> [SCSIPort] [int] NULL ,
> [SCSITargetId] [int] NULL ,
> [Size] [bigint] NULL ,
> [TransferRate] [float] NULL ,
> [VolumeName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [VolumeSerialNumber] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_CDROMDrive] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_MediaAccessDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Capabilities] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [CapabilityDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [ErrorMethodology] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [CompressionMethod] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [NumberOfMediaSupported] [bigint] NULL ,
> [MaxMediaSize] [bigint] NULL ,
> [DefaultBlockSize] [bigint] NULL ,
> [MaxBlockSize] [bigint] NULL ,
> [MinBlockSize] [bigint] NULL ,
> [NeedsCleaning] [bit] NULL ,
> [MediaIsLocked] [bit] NULL ,
> [Security] [int] NULL ,
> [LastCleaned] [datetime] NULL ,
> [MaxAccessTime] [bigint] NULL ,
> [UncompressedDataRate] [bigint] NULL ,
> [LoadTime] [bigint] NULL ,
> [UnloadTime] [bigint] NULL ,
> [MountCount] [bigint] NULL ,
> [TimeOfLastMount] [datetime] NULL ,
> [TotalMountTime] [bigint] NULL ,
> [UnitsDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [MaxUnitsBeforeCleaning] [bigint] NULL ,
> [UnitsUsed] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalDevice] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [PowerManagementCapabilities] [varchar] (1024) COLLATE
Latin1_General_CI_AS
> NULL ,
> [OtherIdentifyingInfo] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL
,
> [IdentifyingDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS
> NULL ,
> [AdditionalAvailability] [varchar] (1024) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemCreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS
NULL
> ,
> [SystemName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [CreationClassName] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [DeviceID] [varchar] (256) COLLATE Latin1_General_CI_AS NULL ,
> [PowerManagementSupported] [bit] NULL ,
> [Availability] [int] NULL ,
> [StatusInfo] [int] NULL ,
> [LastErrorCode] [bigint] NULL ,
> [ErrorDescription] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ErrorCleared] [bit] NULL ,
> [PowerOnHours] [bigint] NULL ,
> [TotalPowerOnHours] [bigint] NULL ,
> [MaxQuiesceTime] [bigint] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_EnabledLogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [EnabledState] [int] NULL ,
> [OtherEnabledState] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [RequestedState] [int] NULL ,
> [EnabledDefault] [int] NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CIM_LogicalElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedSystemElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [OperationalStatus] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [StatusDescriptions] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [InstallDate] [datetime] NULL ,
> [Name] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [Status] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[CHGMGMT_CIM_ManagedElement] (
> [GUID] [varchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
> [Caption] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [ElementName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
> [LastModifiedBy] [varchar] (1024) COLLATE Latin1_General_CI_AS NULL ,
> [ToArchive] [bit] NULL ,
> [TimeOfCreation] [datetime] NULL ,
> [TimeOfModification] [datetime] NOT NULL
> ) ON [PRIMARY]
>|||"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote...
> Can you also post the definition of and trigger on
> DATAVIEW_Win32_CDROMDrive?
>
Here it is, this is the "bottom" trigger (triggers on views are nested
(cascaded) all the way up to the top level tabel):
create trigger [DATAVIEW_Win32_CDROMDrive-InsertTrigger] on
[DATAVIEW_Win32_CDROMDrive] instead of INSERT as
INSERT INTO DATAVIEW_CIM_CDROMDrive ("GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation") SELECT "GUID", "Capabilities",
"CapabilityDescriptions", "ErrorMethodology", "CompressionMethod",
"NumberOfMediaSupported", "MaxMediaSize", "DefaultBlockSize",
"MaxBlockSize", "MinBlockSize", "NeedsCleaning", "MediaIsLocked",
"Security", "LastCleaned", "MaxAccessTime", "UncompressedDataRate",
"LoadTime", "UnloadTime", "MountCount", "TimeOfLastMount", "TotalMountTime",
"UnitsDescription", "MaxUnitsBeforeCleaning", "UnitsUsed",
"PowerManagementCapabilities", "OtherIdentifyingInfo",
"IdentifyingDescriptions", "AdditionalAvailability",
"SystemCreationClassName", "SystemName", "CreationClassName", "DeviceID",
"PowerManagementSupported", "Availability", "StatusInfo", "LastErrorCode",
"ErrorDescription", "ErrorCleared", "PowerOnHours", "TotalPowerOnHours",
"MaxQuiesceTime", "EnabledState", "OtherEnabledState", "RequestedState",
"EnabledDefault", "OperationalStatus", "StatusDescriptions", "InstallDate",
"Name", "Status", "Caption", "Description", "ElementName", "LastModifiedBy",
"ToArchive", "TimeOfCreation" FROM inserted
INSERT INTO Win32_CDROMDrive ("GUID", "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber") select guid, "Drive", "DriveIntegrity",
"FileSystemFlags", "FileSystemFlagsEx", "Id", "Manufacturer",
"MaximumComponentLength", "MediaLoaded", "MediaType",
"MfrAssignedRevisionLevel", "RevisionLevel", "SCSIBus", "SCSILogicalUnit",
"SCSIPort", "SCSITargetId", "Size", "TransferRate", "VolumeName",
"VolumeSerialNumber" from inserted
Thanks,
SLE

8000 char Row limit

is the 8000 char row limit only an issue in standard
edition? If I upgrade to enterprise edition will I not
have this problem? Thanks.
It has nothing to do with the edition; it is a storage / engine limitation.
What exactly are you trying to do? Are you aware of the reasons for the
limit?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.
|||The actual row limit is 8060, and the max char/varchar column size is 8000
bytes.
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.
|||This sql server is running our Microsoft CRM database. I
am trying to create some custom attributes. When I try to
create one it says that I am at the 8000 char row limit.
I can not delete any attributes. Is there not a way
around this? Thanks for your replys.

>--Original Message--
>It has nothing to do with the edition; it is a storage /
engine limitation.
>What exactly are you trying to do? Are you aware of the
reasons for the
>limit?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
>news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
>
>.
>
|||Is there any way around the 8000 byte limit?

>--Original Message--
>The actual row limit is 8060, and the max char/varchar
column size is 8000
>bytes.
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
>news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
>
>.
>
|||The error message is just a warning. You can go ahead and create a table
that is wider, just don't expect to use it all. For example:
CREATE TABLE dbo.blat
(
bar1 VARCHAR(5000),
bar2 VARCHAR(5000)
)
GO
Warning: The table 'blat' has been created but its maximum row size (10025)
exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a
row in this table will fail if the resulting row length exceeds 8060 bytes.
As the message states, the table was still created. However, populating it
with data will be more challenging. These work fine:
INSERT blat SELECT 'a', 'b'
INSERT blat SELECT REPLICATE('a', 5000), 'b'
INSERT blat SELECT 'a', REPLICATE('b', 5000)
But when you try and send more than 8060 bytes, you will have problems, e.g.
INSERT blat SELECT
REPLICATE('a', 5000), REPLICATE('b', 5000)
You get:
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 10013 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
What I recommend is storing any larger attributes in a separate table and
use relationships for data integrity.
Maybe you can show the code that represents "when I try to create one" and
the ACTUAL error message you receive, not your casual interpretation...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189ae01c420b2$c7d24410$a601280a@.phx.gbl...
> This sql server is running our Microsoft CRM database. I
> am trying to create some custom attributes. When I try to
> create one it says that I am at the 8000 char row limit.
> I can not delete any attributes. Is there not a way
> around this? Thanks for your replys.
|||I am not trying to manually create this table. The table
is created upon the installation of CRM and I am trying to
add an attribute to it within microsoft CRM. In CRM i get
the following error:
An error occurred during the addition of the new field.
The addition failed. For more information, see the event
log.
Event log error:
1.
dmLog: Failed to add new Picklist attribute (CFPcatagory)
to Account entity.
2.
dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
bytes.
I dont know if you are familiar with CRM deployment
manager, there is a section to create mappings, which
might be the same as the relationships as your talking
about. It will not let me modify any existing attribute,
even the ones that I have created. I cant delete an
attribute either.
Thanks again for your help.

>--Original Message--
>The error message is just a warning. You can go ahead
and create a table
>that is wider, just don't expect to use it all. For
example:
>CREATE TABLE dbo.blat
>(
> bar1 VARCHAR(5000),
> bar2 VARCHAR(5000)
>)
>GO
>Warning: The table 'blat' has been created but its
maximum row size (10025)
>exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a
>row in this table will fail if the resulting row length
exceeds 8060 bytes.
>As the message states, the table was still created.
However, populating it
>with data will be more challenging. These work fine:
>INSERT blat SELECT 'a', 'b'
>INSERT blat SELECT REPLICATE('a', 5000), 'b'
>INSERT blat SELECT 'a', REPLICATE('b', 5000)
>But when you try and send more than 8060 bytes, you will
have problems, e.g.
>INSERT blat SELECT
> REPLICATE('a', 5000), REPLICATE('b', 5000)
>You get:
>Server: Msg 511, Level 16, State 1, Line 1
>Cannot create a row of size 10013 which is greater than
the allowable
>maximum of 8060.
>The statement has been terminated.
>What I recommend is storing any larger attributes in a
separate table and
>use relationships for data integrity.
>Maybe you can show the code that represents "when I try
to create one" and
>the ACTUAL error message you receive, not your casual
interpretation...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"billy dodson" <billy@.pmicromart.com> wrote in message
>news:189ae01c420b2$c7d24410$a601280a@.phx.gbl...
I
to
>
>.
>
|||This limitation will be removed in Yukon. You will be able to do the
following (for example):
create table t1 (c1 varchar (5000), c2 varchar (5000))
go
insert into t1 values (replicate ('a', 5000), replicate ('b', 5000))
go
with no problems.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx.gbl...
> Is there any way around the 8000 byte limit?
> column size is 8000
|||Billy,
As stated, it is only a warning. How much of the 8060 char limit is being
used by the unmodified table?
If you have 16 bytes left, you can assign the new attribute to a text or
ntext column, and that will fit. Of course, you then have to deal with the
issues that arise from using text columns.
You can create an attribute table of nothing more than an identity or guid
column and the attribute and then just store the identity (4 bytes) or
rowguid (16 bytes) in your table.
You might also run statistics on the actual size of data being stored vs the
field size for all fields in the table. Knowing this will give you an idea
of how much space you can "safely" assign to the new attribute. The caveat
here is that you will have to check the length of everything on inserts and
updates.
Regards,
John
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx.gbl...[color=darkblue]
> Is there any way around the 8000 byte limit?
> column size is 8000
|||sounds like one for the CRM group if there is one - being unable to remove
user-defined attributes sounds like a design flaw
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1bad601c420b8$297548c0$a101280a@.phx.gbl...[vbcol=seagreen]
> I am not trying to manually create this table. The table
> is created upon the installation of CRM and I am trying to
> add an attribute to it within microsoft CRM. In CRM i get
> the following error:
> An error occurred during the addition of the new field.
> The addition failed. For more information, see the event
> log.
> Event log error:
> 1.
> dmLog: Failed to add new Picklist attribute (CFPcatagory)
> to Account entity.
> 2.
> dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
> B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
> bytes.
>
> I dont know if you are familiar with CRM deployment
> manager, there is a section to create mappings, which
> might be the same as the relationships as your talking
> about. It will not let me modify any existing attribute,
> even the ones that I have created. I cant delete an
> attribute either.
> Thanks again for your help.
>
> and create a table
> example:
> maximum row size (10025)
> INSERT or UPDATE of a
> exceeds 8060 bytes.
> However, populating it
> have problems, e.g.
> the allowable
> separate table and
> to create one" and
> interpretation...
> I
> to

8000 char Row limit

is the 8000 char row limit only an issue in standard
edition? If I upgrade to enterprise edition will I not
have this problem? Thanks.It has nothing to do with the edition; it is a storage / engine limitation.
What exactly are you trying to do? Are you aware of the reasons for the
limit?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.|||The actual row limit is 8060, and the max char/varchar column size is 8000
bytes.
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.|||This sql server is running our Microsoft CRM database. I
am trying to create some custom attributes. When I try to
create one it says that I am at the 8000 char row limit.
I can not delete any attributes. Is there not a way
around this? Thanks for your replys.
>--Original Message--
>It has nothing to do with the edition; it is a storage /
engine limitation.
>What exactly are you trying to do? Are you aware of the
reasons for the
>limit?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
>news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
>> is the 8000 char row limit only an issue in standard
>> edition? If I upgrade to enterprise edition will I not
>> have this problem? Thanks.
>
>.
>|||Is there any way around the 8000 byte limit?
>--Original Message--
>The actual row limit is 8060, and the max char/varchar
column size is 8000
>bytes.
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
>news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
>> is the 8000 char row limit only an issue in standard
>> edition? If I upgrade to enterprise edition will I not
>> have this problem? Thanks.
>
>.
>|||The error message is just a warning. You can go ahead and create a table
that is wider, just don't expect to use it all. For example:
CREATE TABLE dbo.blat
(
bar1 VARCHAR(5000),
bar2 VARCHAR(5000)
)
GO
Warning: The table 'blat' has been created but its maximum row size (10025)
exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a
row in this table will fail if the resulting row length exceeds 8060 bytes.
As the message states, the table was still created. However, populating it
with data will be more challenging. These work fine:
INSERT blat SELECT 'a', 'b'
INSERT blat SELECT REPLICATE('a', 5000), 'b'
INSERT blat SELECT 'a', REPLICATE('b', 5000)
But when you try and send more than 8060 bytes, you will have problems, e.g.
INSERT blat SELECT
REPLICATE('a', 5000), REPLICATE('b', 5000)
You get:
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 10013 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
What I recommend is storing any larger attributes in a separate table and
use relationships for data integrity.
Maybe you can show the code that represents "when I try to create one" and
the ACTUAL error message you receive, not your casual interpretation...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189ae01c420b2$c7d24410$a601280a@.phx.gbl...
> This sql server is running our Microsoft CRM database. I
> am trying to create some custom attributes. When I try to
> create one it says that I am at the 8000 char row limit.
> I can not delete any attributes. Is there not a way
> around this? Thanks for your replys.|||I am not trying to manually create this table. The table
is created upon the installation of CRM and I am trying to
add an attribute to it within microsoft CRM. In CRM i get
the following error:
An error occurred during the addition of the new field.
The addition failed. For more information, see the event
log.
Event log error:
1.
dmLog: Failed to add new Picklist attribute (CFPcatagory)
to Account entity.
2.
dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
bytes.
I dont know if you are familiar with CRM deployment
manager, there is a section to create mappings, which
might be the same as the relationships as your talking
about. It will not let me modify any existing attribute,
even the ones that I have created. I cant delete an
attribute either.
Thanks again for your help.
>--Original Message--
>The error message is just a warning. You can go ahead
and create a table
>that is wider, just don't expect to use it all. For
example:
>CREATE TABLE dbo.blat
>(
> bar1 VARCHAR(5000),
> bar2 VARCHAR(5000)
>)
>GO
>Warning: The table 'blat' has been created but its
maximum row size (10025)
>exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a
>row in this table will fail if the resulting row length
exceeds 8060 bytes.
>As the message states, the table was still created.
However, populating it
>with data will be more challenging. These work fine:
>INSERT blat SELECT 'a', 'b'
>INSERT blat SELECT REPLICATE('a', 5000), 'b'
>INSERT blat SELECT 'a', REPLICATE('b', 5000)
>But when you try and send more than 8060 bytes, you will
have problems, e.g.
>INSERT blat SELECT
> REPLICATE('a', 5000), REPLICATE('b', 5000)
>You get:
>Server: Msg 511, Level 16, State 1, Line 1
>Cannot create a row of size 10013 which is greater than
the allowable
>maximum of 8060.
>The statement has been terminated.
>What I recommend is storing any larger attributes in a
separate table and
>use relationships for data integrity.
>Maybe you can show the code that represents "when I try
to create one" and
>the ACTUAL error message you receive, not your casual
interpretation...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"billy dodson" <billy@.pmicromart.com> wrote in message
>news:189ae01c420b2$c7d24410$a601280a@.phx.gbl...
>> This sql server is running our Microsoft CRM database.
I
>> am trying to create some custom attributes. When I try
to
>> create one it says that I am at the 8000 char row limit.
>> I can not delete any attributes. Is there not a way
>> around this? Thanks for your replys.
>
>.
>|||This limitation will be removed in Yukon. You will be able to do the
following (for example):
create table t1 (c1 varchar (5000), c2 varchar (5000))
go
insert into t1 values (replicate ('a', 5000), replicate ('b', 5000))
go
with no problems.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx.gbl...
> Is there any way around the 8000 byte limit?
> >--Original Message--
> >The actual row limit is 8060, and the max char/varchar
> column size is 8000
> >bytes.
> >"Billy Dodson" <billy@.pmicromart.com> wrote in message
> >news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> >> is the 8000 char row limit only an issue in standard
> >> edition? If I upgrade to enterprise edition will I not
> >> have this problem? Thanks.
> >
> >
> >.
> >|||Billy,
As stated, it is only a warning. How much of the 8060 char limit is being
used by the unmodified table?
If you have 16 bytes left, you can assign the new attribute to a text or
ntext column, and that will fit. Of course, you then have to deal with the
issues that arise from using text columns.
You can create an attribute table of nothing more than an identity or guid
column and the attribute and then just store the identity (4 bytes) or
rowguid (16 bytes) in your table.
You might also run statistics on the actual size of data being stored vs the
field size for all fields in the table. Knowing this will give you an idea
of how much space you can "safely" assign to the new attribute. The caveat
here is that you will have to check the length of everything on inserts and
updates.
Regards,
John
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx.gbl...
> Is there any way around the 8000 byte limit?
> >--Original Message--
> >The actual row limit is 8060, and the max char/varchar
> column size is 8000
> >bytes.
> >"Billy Dodson" <billy@.pmicromart.com> wrote in message
> >news:1b64b01c420a7$4331a210$a301280a@.phx.gbl...
> >> is the 8000 char row limit only an issue in standard
> >> edition? If I upgrade to enterprise edition will I not
> >> have this problem? Thanks.
> >
> >
> >.
> >|||sounds like one for the CRM group if there is one - being unable to remove
user-defined attributes sounds like a design flaw
--
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1bad601c420b8$297548c0$a101280a@.phx.gbl...
> I am not trying to manually create this table. The table
> is created upon the installation of CRM and I am trying to
> add an attribute to it within microsoft CRM. In CRM i get
> the following error:
> An error occurred during the addition of the new field.
> The addition failed. For more information, see the event
> log.
> Event log error:
> 1.
> dmLog: Failed to add new Picklist attribute (CFPcatagory)
> to Account entity.
> 2.
> dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
> B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
> bytes.
>
> I dont know if you are familiar with CRM deployment
> manager, there is a section to create mappings, which
> might be the same as the relationships as your talking
> about. It will not let me modify any existing attribute,
> even the ones that I have created. I cant delete an
> attribute either.
> Thanks again for your help.
>
> >--Original Message--
> >The error message is just a warning. You can go ahead
> and create a table
> >that is wider, just don't expect to use it all. For
> example:
> >
> >CREATE TABLE dbo.blat
> >(
> > bar1 VARCHAR(5000),
> > bar2 VARCHAR(5000)
> >)
> >GO
> >
> >Warning: The table 'blat' has been created but its
> maximum row size (10025)
> >exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a
> >row in this table will fail if the resulting row length
> exceeds 8060 bytes.
> >
> >As the message states, the table was still created.
> However, populating it
> >with data will be more challenging. These work fine:
> >
> >INSERT blat SELECT 'a', 'b'
> >INSERT blat SELECT REPLICATE('a', 5000), 'b'
> >INSERT blat SELECT 'a', REPLICATE('b', 5000)
> >
> >But when you try and send more than 8060 bytes, you will
> have problems, e.g.
> >
> >INSERT blat SELECT
> > REPLICATE('a', 5000), REPLICATE('b', 5000)
> >
> >You get:
> >
> >Server: Msg 511, Level 16, State 1, Line 1
> >Cannot create a row of size 10013 which is greater than
> the allowable
> >maximum of 8060.
> >The statement has been terminated.
> >
> >What I recommend is storing any larger attributes in a
> separate table and
> >use relationships for data integrity.
> >
> >Maybe you can show the code that represents "when I try
> to create one" and
> >the ACTUAL error message you receive, not your casual
> interpretation...
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"billy dodson" <billy@.pmicromart.com> wrote in message
> >news:189ae01c420b2$c7d24410$a601280a@.phx.gbl...
> >> This sql server is running our Microsoft CRM database.
> I
> >> am trying to create some custom attributes. When I try
> to
> >> create one it says that I am at the 8000 char row limit.
> >> I can not delete any attributes. Is there not a way
> >> around this? Thanks for your replys.
> >
> >
> >.
> >

8000 char Row limit

is the 8000 char row limit only an issue in standard
edition? If I upgrade to enterprise edition will I not
have this problem? Thanks.It has nothing to do with the edition; it is a storage / engine limitation.
What exactly are you trying to do? Are you aware of the reasons for the
limit?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx
.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.|||The actual row limit is 8060, and the max char/varchar column size is 8000
bytes.
"Billy Dodson" <billy@.pmicromart.com> wrote in message
news:1b64b01c420a7$4331a210$a301280a@.phx
.gbl...
> is the 8000 char row limit only an issue in standard
> edition? If I upgrade to enterprise edition will I not
> have this problem? Thanks.|||This sql server is running our Microsoft CRM database. I
am trying to create some custom attributes. When I try to
create one it says that I am at the 8000 char row limit.
I can not delete any attributes. Is there not a way
around this? Thanks for your replys.

>--Original Message--
>It has nothing to do with the edition; it is a storage /
engine limitation.
>What exactly are you trying to do? Are you aware of the
reasons for the
>limit?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
> news:1b64b01c420a7$4331a210$a301280a@.phx
.gbl...
>
>.
>|||Is there any way around the 8000 byte limit?

>--Original Message--
>The actual row limit is 8060, and the max char/varchar
column size is 8000
>bytes.
>"Billy Dodson" <billy@.pmicromart.com> wrote in message
> news:1b64b01c420a7$4331a210$a301280a@.phx
.gbl...
>
>.
>|||The error message is just a warning. You can go ahead and create a table
that is wider, just don't expect to use it all. For example:
CREATE TABLE dbo.blat
(
bar1 VARCHAR(5000),
bar2 VARCHAR(5000)
)
GO
Warning: The table 'blat' has been created but its maximum row size (10025)
exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a
row in this table will fail if the resulting row length exceeds 8060 bytes.
As the message states, the table was still created. However, populating it
with data will be more challenging. These work fine:
INSERT blat SELECT 'a', 'b'
INSERT blat SELECT REPLICATE('a', 5000), 'b'
INSERT blat SELECT 'a', REPLICATE('b', 5000)
But when you try and send more than 8060 bytes, you will have problems, e.g.
INSERT blat SELECT
REPLICATE('a', 5000), REPLICATE('b', 5000)
You get:
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 10013 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
What I recommend is storing any larger attributes in a separate table and
use relationships for data integrity.
Maybe you can show the code that represents "when I try to create one" and
the ACTUAL error message you receive, not your casual interpretation...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189ae01c420b2$c7d24410$a601280a@.phx
.gbl...
> This sql server is running our Microsoft CRM database. I
> am trying to create some custom attributes. When I try to
> create one it says that I am at the 8000 char row limit.
> I can not delete any attributes. Is there not a way
> around this? Thanks for your replys.|||I am not trying to manually create this table. The table
is created upon the installation of CRM and I am trying to
add an attribute to it within microsoft CRM. In CRM i get
the following error:
An error occurred during the addition of the new field.
The addition failed. For more information, see the event
log.
Event log error:
1.
dmLog: Failed to add new Picklist attribute (CFPcatagory)
to Account entity.
2.
dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
bytes.
I dont know if you are familiar with CRM deployment
manager, there is a section to create mappings, which
might be the same as the relationships as your talking
about. It will not let me modify any existing attribute,
even the ones that I have created. I cant delete an
attribute either.
Thanks again for your help.

>--Original Message--
>The error message is just a warning. You can go ahead
and create a table
>that is wider, just don't expect to use it all. For
example:
>CREATE TABLE dbo.blat
>(
> bar1 VARCHAR(5000),
> bar2 VARCHAR(5000)
> )
>GO
>Warning: The table 'blat' has been created but its
maximum row size (10025)
>exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a
>row in this table will fail if the resulting row length
exceeds 8060 bytes.
>As the message states, the table was still created.
However, populating it
>with data will be more challenging. These work fine:
>INSERT blat SELECT 'a', 'b'
>INSERT blat SELECT REPLICATE('a', 5000), 'b'
>INSERT blat SELECT 'a', REPLICATE('b', 5000)
>But when you try and send more than 8060 bytes, you will
have problems, e.g.
>INSERT blat SELECT
> REPLICATE('a', 5000), REPLICATE('b', 5000)
>You get:
>Server: Msg 511, Level 16, State 1, Line 1
>Cannot create a row of size 10013 which is greater than
the allowable
>maximum of 8060.
>The statement has been terminated.
>What I recommend is storing any larger attributes in a
separate table and
>use relationships for data integrity.
>Maybe you can show the code that represents "when I try
to create one" and
>the ACTUAL error message you receive, not your casual
interpretation...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"billy dodson" <billy@.pmicromart.com> wrote in message
> news:189ae01c420b2$c7d24410$a601280a@.phx
.gbl...
I
to
>
>.
>|||This limitation will be removed in Yukon. You will be able to do the
following (for example):
create table t1 (c1 varchar (5000), c2 varchar (5000))
go
insert into t1 values (replicate ('a', 5000), replicate ('b', 5000))
go
with no problems.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx
.gbl...
> Is there any way around the 8000 byte limit?
>
> column size is 8000|||Billy,
As stated, it is only a warning. How much of the 8060 char limit is being
used by the unmodified table?
If you have 16 bytes left, you can assign the new attribute to a text or
ntext column, and that will fit. Of course, you then have to deal with the
issues that arise from using text columns.
You can create an attribute table of nothing more than an identity or guid
column and the attribute and then just store the identity (4 bytes) or
rowguid (16 bytes) in your table.
You might also run statistics on the actual size of data being stored vs the
field size for all fields in the table. Knowing this will give you an idea
of how much space you can "safely" assign to the new attribute. The caveat
here is that you will have to check the length of everything on inserts and
updates.
Regards,
John
"billy dodson" <billy@.pmicromart.com> wrote in message
news:189af01c420b2$e74a99f0$a601280a@.phx
.gbl...
> Is there any way around the 8000 byte limit?
>
> column size is 8000|||sounds like one for the CRM group if there is one - being unable to remove
user-defined attributes sounds like a design flaw
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:1bad601c420b8$297548c0$a101280a@.phx
.gbl...
> I am not trying to manually create this table. The table
> is created upon the installation of CRM and I am trying to
> add an attribute to it within microsoft CRM. In CRM i get
> the following error:
> An error occurred during the addition of the new field.
> The addition failed. For more information, see the event
> log.
> Event log error:
> 1.
> dmLog: Failed to add new Picklist attribute (CFPcatagory)
> to Account entity.
> 2.
> dmLog: New size of the attribute ({53B8A5DA-324C-4926-9F57-
> B21C02C1E8C7}) exceeds the SQL Server row limit of 8000
> bytes.
>
> I dont know if you are familiar with CRM deployment
> manager, there is a section to create mappings, which
> might be the same as the relationships as your talking
> about. It will not let me modify any existing attribute,
> even the ones that I have created. I cant delete an
> attribute either.
> Thanks again for your help.
>
> and create a table
> example:
> maximum row size (10025)
> INSERT or UPDATE of a
> exceeds 8060 bytes.
> However, populating it
> have problems, e.g.
> the allowable
> separate table and
> to create one" and
> interpretation...
> I
> to

64-bit Server (clustered) 128 GB ram - 155 GB used

It seems the Server itself shows 155GB used, even though one Instance has 91 GB limit and the other 31 GB limit?

The server only has 128 GB of RAM with a 30+ GB Virtual Swap File.

What do I check to see if there is a problem? What perfmon do I use?

We are testing on this server and the CPUs are only at 5-10% max. Some testing brought the CPUs to 80-90%.

There are no errors.

Thanks,

Thomas

If you have two instances on one server, they will use all the memory up to the Max memory limit that you set for SQL Server in each instance.

You also need to make sure that you have given the "Lock Pages in Memory" right to the SQL Server service account. My blog post has more details:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

If you are concerned about memory pressure, buffer cache hit ratio and page life expectancy, and memory grants pending are three places to start looking.

|||

Where are you getting the 155GB value from? Can you try DBCC memorystatus on both instances and paste the results?

Thanks, Ron D.

64-bit Server (clustered) 128 GB ram - 155 GB used

It seems the Server itself shows 155GB used, even though one Instance has 91 GB limit and the other 31 GB limit?

The server only has 128 GB of RAM with a 30+ GB Virtual Swap File.

What do I check to see if there is a problem? What perfmon do I use?

We are testing on this server and the CPUs are only at 5-10% max. Some testing brought the CPUs to 80-90%.

There are no errors.

Thanks,

Thomas

If you have two instances on one server, they will use all the memory up to the Max memory limit that you set for SQL Server in each instance.

You also need to make sure that you have given the "Lock Pages in Memory" right to the SQL Server service account. My blog post has more details:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

If you are concerned about memory pressure, buffer cache hit ratio and page life expectancy, and memory grants pending are three places to start looking.

|||

Where are you getting the 155GB value from? Can you try DBCC memorystatus on both instances and paste the results?

Thanks, Ron D.

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.
There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

64000 dimension members limit?

I get an error populating a dimension that says that there are over 64000
members.
The dimension only has one level.
Is that a limit, bug or am I doing something wrong?
Thanks in advance,
Philip
Analysis Services has a 64K limit on the number of children that any one
parent can have. Since you have only one level, there is only one parent
(i.e. the ALL member); thus you are running into this problem. You can
either add a level which makes sense to your structure, for example taking
the first 3 characters of the customer name and using it as an intermediate
level, or selecting another field which makes sense, e.g. adding State to
the level above Customer, or you can turn on the Analysis Services automatic
grouping level feature where AS will automatically create a level for you.
BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip McComish" <philvmc@.yahoo.com.br> wrote in message
news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
> I get an error populating a dimension that says that there are over 64000
> members.
> The dimension only has one level.
> Is that a limit, bug or am I doing something wrong?
> Thanks in advance,
> Philip
>
|||Dave,
Thanks for the tips. They solved my problem.
Good news about Yukon.
Best regards,
Philip
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escreveu na mensagem
news:exZFrPpJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Analysis Services has a 64K limit on the number of children that any one
> parent can have. Since you have only one level, there is only one parent
> (i.e. the ALL member); thus you are running into this problem. You can
> either add a level which makes sense to your structure, for example taking
> the first 3 characters of the customer name and using it as an
> intermediate
> level, or selecting another field which makes sense, e.g. adding State to
> the level above Customer, or you can turn on the Analysis Services
> automatic
> grouping level feature where AS will automatically create a level for you.
> BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philip McComish" <philvmc@.yahoo.com.br> wrote in message
> news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
>

64000 dimension members limit?

I get an error populating a dimension that says that there are over 64000
members.
The dimension only has one level.
Is that a limit, bug or am I doing something wrong?
Thanks in advance,
PhilipAnalysis Services has a 64K limit on the number of children that any one
parent can have. Since you have only one level, there is only one parent
(i.e. the ALL member); thus you are running into this problem. You can
either add a level which makes sense to your structure, for example taking
the first 3 characters of the customer name and using it as an intermediate
level, or selecting another field which makes sense, e.g. adding State to
the level above Customer, or you can turn on the Analysis Services automatic
grouping level feature where AS will automatically create a level for you.
BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip McComish" <philvmc@.yahoo.com.br> wrote in message
news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
> I get an error populating a dimension that says that there are over 64000
> members.
> The dimension only has one level.
> Is that a limit, bug or am I doing something wrong?
> Thanks in advance,
> Philip
>|||Dave,
Thanks for the tips. They solved my problem.
Good news about Yukon.
Best regards,
Philip
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escreveu na mensag
em
news:exZFrPpJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Analysis Services has a 64K limit on the number of children that any one
> parent can have. Since you have only one level, there is only one parent
> (i.e. the ALL member); thus you are running into this problem. You can
> either add a level which makes sense to your structure, for example taking
> the first 3 characters of the customer name and using it as an
> intermediate
> level, or selecting another field which makes sense, e.g. adding State to
> the level above Customer, or you can turn on the Analysis Services
> automatic
> grouping level feature where AS will automatically create a level for you.
> BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philip McComish" <philvmc@.yahoo.com.br> wrote in message
> news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
>

5-workload limit

Hi, does anyone has practical problem when exceeding the
5-workload limit of MSDE. Please tell me about it what
errors you get or how slow will performance degrade.
Maybe how you solve this. Tnx!
Hi Aldrin,
You won't get any errors when you exceed the 5-workload limit. When you
exceed this limit it will be logged in the SQL Server error log though. The
message in the error log will state that there were more than 8 concurrent
workloads, as 3 workloads have been reserved for the system to use.
Performance will drop of when you have more than 5 concurrent workloads, but
it will not be dramatically until you reach about 20 concurrent workloads as
far as I know. Of course it might be different on your system, so the best
thing you can do is test your own system with the maximum number of
workloads you expect to find out if you still get the performance you need.
You can find more information about the workload governor in the following
Knowledge Base article:
http://msdn.microsoft.com/library/?u...asp?frame=true
Jacco Schalkwijk
SQL Server MVP
"Aldrin" <aldrin@.wanadoo.nl> wrote in message
news:1bb5f01c42130$c45925a0$a301280a@.phx.gbl...
> Hi, does anyone has practical problem when exceeding the
> 5-workload limit of MSDE. Please tell me about it what
> errors you get or how slow will performance degrade.
> Maybe how you solve this. Tnx!
|||Thank you Jacco,
Now we're more sure to start with MSDE as backend (with
Delphi as frontend)! Not afraid anymore that everything
will stop or so. We will test this off course as you
wrote.
Greetings,
Aldrin

>--Original Message--
>Hi Aldrin,
>You won't get any errors when you exceed the 5-workload
limit. When you
>exceed this limit it will be logged in the SQL Server
error log though. The
>message in the error log will state that there were more
than 8 concurrent
>workloads, as 3 workloads have been reserved for the
system to use.
>Performance will drop of when you have more than 5
concurrent workloads, but
>it will not be dramatically until you reach about 20
concurrent workloads as
>far as I know. Of course it might be different on your
system, so the best
>thing you can do is test your own system with the
maximum number of
>workloads you expect to find out if you still get the
performance you need.
>You can find more information about the workload
governor in the following
>Knowledge Base article:
>
>http://msdn.microsoft.com/library/?url=/library/en-
us/architec/8_ar_sa2_0ciq.asp?frame=true[color=darkblue]
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Aldrin" <aldrin@.wanadoo.nl> wrote in message
>news:1bb5f01c42130$c45925a0$a301280a@.phx.gbl...
the
>
>.
>

4GB string store limit: which file extensions?

The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?

I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.

Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1

4GB string store limit: which file extensions?

The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?

I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.

Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1

4GB size limtation on SQL server express

Hi,

I would like to know what problems will occur when I try to create a database that exceed the size limit of 4GB ?

Thanks

Moving to the SQL Server Express forum.|||SQL Server will provide an error message and the attempt to create a database exceeding 4GB will fail.|||

hi,

as Arnie already pointed out, SQLExpress will throw an exception..

this exception is little misleading as it reports
Server: Msg 1105, Level 17, State 2, Line 6
Could not allocate space for object 'a' in database 'test' because the 'PRIMARY' filegroup is full.

I do actually think another exception should be raised, something like
Err 1827: CREATE/ALTER database failed becouse the resulting cumulative database size would exceed your licensed limit of xxxx mb per database.

but this is another story
regards

4gb limit

Hi There

I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.

However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.

So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?

Thanx

Hi,

you have to backup your log file.

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY -- modell "simple"

BACKUP LOG <DB_NAME> -- modell "full"

or shrink it without backup:

BACKUP LOG <DB_NAME> WITH NO_LOG

|||

I cannot do this, DB is in simple recovery. It is one big transaction that causes this, i cannot do this in the middle of a transaction.

Thanx

|||

There is not a size limit for log files.

From: http://msdn2.microsoft.com/en-us/library/ms345154.aspx

"The 4 GB database size limit applies only to data files and not to log files."

Did you check the computer application log file for errors?
The SQL Server log file?

|||

Thanx Arnie

It just is so strange that it happended exactly when the log file hit 4Gb?

But thanx for the feedback

4gb limit

Hi There

I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.

However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.

So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?

Thanx

Hi,

you have to backup your log file.

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY -- modell "simple"

BACKUP LOG <DB_NAME> -- modell "full"

or shrink it without backup:

BACKUP LOG <DB_NAME> WITH NO_LOG

|||

I cannot do this, DB is in simple recovery. It is one big transaction that causes this, i cannot do this in the middle of a transaction.

Thanx

|||

There is not a size limit for log files.

From: http://msdn2.microsoft.com/en-us/library/ms345154.aspx

"The 4 GB database size limit applies only to data files and not to log files."

Did you check the computer application log file for errors?
The SQL Server log file?

|||

Thanx Arnie

It just is so strange that it happended exactly when the log file hit 4Gb?

But thanx for the feedback

4294967296 Tuple limit and 32 bit Architecture

We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:

Example

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}

Error

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.

Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?

Hi prakal,

the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.

Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.

You would have your query done on the 32bit box as well.

|||

I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

Or, if you had all of these attributes as part of a hierarchy you could just do

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

|||

A recent thread on this forum linked to a Microsoft Support article that might be relevant:

Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"

SYMPTOMS

When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

This problem may occur if the following conditions are true:

? The user account is a member of a role that has dimension security set on a measure. ? The complex MDX query contains the NON EMPTY clause.

Back to the top

CAUSE

This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.

|||

Darren,

Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".

Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.

|||

Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.

Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {

NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])

* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])

} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

4294967296 Tuple limit and 32 bit Architecture

We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:

Example

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}

Error

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.

Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?

This post may help. You may want to follow up on the Analysis Services forum.

|||

Thanks for your reply Teo. I have already refered to this post and unfortunately I'm unable to use the Exists function in this context due to certain limitations in our application.

I have also posted the same issue on the Analysis services forum.

Thanks again for your help.

4294967296 Tuple limit and 32 bit Architecture

We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:

Example

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}

Error

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.

Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?

Hi prakal,

the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.

Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.

You would have your query done on the 32bit box as well.

|||

I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

Or, if you had all of these attributes as part of a hierarchy you could just do

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}

|||

A recent thread on this forum linked to a Microsoft Support article that might be relevant:

Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"

SYMPTOMS

When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

This problem may occur if the following conditions are true:

? The user account is a member of a role that has dimension security set on a measure. ? The complex MDX query contains the NON EMPTY clause.

Back to the top

CAUSE

This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.

|||

Darren,

Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".

Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.

|||

Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.

Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.

WITH

MEMBER [Date].[DateID].[M1] AS

AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})

SET [M2] AS

({[dimension7].[attribute7].[value7]})

SELECT {[Measures].[count]} ON COLUMNS,

NON EMPTY {

NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])

* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])

} ON ROWS

FROM MyCube

WHERE {([Date].[DateID].[M1])}