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