A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.

A basic design question

When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION?

Then if there is a lookup table on the many side how should it be set?

I generally set up the PK-FK constraints and write my own DELETE statements rather than rely on CASCADE Delete. That way I always know the flow of how the data will/should be deleted.|||I was thinking that too. But I was also thinking about how SQL Server will overwrite my thinking if the cascade is not set properly.|||

Hi Jack,

I would suggest to set Update to CASCADE, because the change to the lookup table will be cascaded to the child table.

It does not make difference whether Insert is set to CASCADE or NO ACTION.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

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

4 processors, 8 Gigs RAM, 22 Seconds to process report?

I have a table report based on a single 50,000 row table. The report
doesn't do anything unusual, but there are 5 grouping levels. The
ExecutionLog table shows the report took < 1 second to pull the data,
but 22 seconds to process the report.
Given that my high end server can perform millions of calcs per
second, does anyone know why my report would take so long to process?
The error logs show nothing unusual.
Thanks,
BurtWhat format are you using? From the execution log, where is most of the time
spent, processing, rendering?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> I have a table report based on a single 50,000 row table. The report
> doesn't do anything unusual, but there are 5 grouping levels. The
> ExecutionLog table shows the report took < 1 second to pull the data,
> but 22 seconds to process the report.
> Given that my high end server can perform millions of calcs per
> second, does anyone know why my report would take so long to process?
> The error logs show nothing unusual.
> Thanks,
> Burt|||Hi Tudor,
The format is HTML. Both rendering and the data pull from SQL Server
take less than a second. The report processing takes 22 seconds.
If I use an AS OLAP cube as the data source, the data pull takes 10
seconds, but the report still takes ~22 seconds to process.
Any thoughts?
Thanks,
Burt
"Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> What format are you using? From the execution log, where is most of the time
> spent, processing, rendering?
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > I have a table report based on a single 50,000 row table. The report
> > doesn't do anything unusual, but there are 5 grouping levels. The
> > ExecutionLog table shows the report took < 1 second to pull the data,
> > but 22 seconds to process the report.
> >
> > Given that my high end server can perform millions of calcs per
> > second, does anyone know why my report would take so long to process?
> > The error logs show nothing unusual.
> >
> > Thanks,
> >
> > Burt|||Just to make sure, are you using filters in your report? Filters bring over
all the data (in your case 50,000 rows) prior to doing anything with the
data. Then it works on the data (doing the filtering, grouping etc).
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> Hi Tudor,
> The format is HTML. Both rendering and the data pull from SQL Server
> take less than a second. The report processing takes 22 seconds.
> If I use an AS OLAP cube as the data source, the data pull takes 10
> seconds, but the report still takes ~22 seconds to process.
> Any thoughts?
> Thanks,
> Burt
>
> "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > What format are you using? From the execution log, where is most of the
time
> > spent, processing, rendering?
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > I have a table report based on a single 50,000 row table. The report
> > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > but 22 seconds to process the report.
> > >
> > > Given that my high end server can perform millions of calcs per
> > > second, does anyone know why my report would take so long to process?
> > > The error logs show nothing unusual.
> > >
> > > Thanks,
> > >
> > > Burt|||Thanks, Bruce, but no, no filters. Some additional info:
-There are actually only 18K rows in the table, not 50K, and 6 groups,
not 5.
-A couple of guys on the RS dev team from MS were kind enough to take
a look at the rdl, but didn't spot anything unusual.
-I created a similar 6 group report in MS Access, which took only a
couple of seconds to create.
-I have a 600K zip file with the RDL, table DDL, and actual table data
if anyone wants to play with it.
Thanks,
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> Just to make sure, are you using filters in your report? Filters bring over
> all the data (in your case 50,000 rows) prior to doing anything with the
> data. Then it works on the data (doing the filtering, grouping etc).
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > Hi Tudor,
> >
> > The format is HTML. Both rendering and the data pull from SQL Server
> > take less than a second. The report processing takes 22 seconds.
> >
> > If I use an AS OLAP cube as the data source, the data pull takes 10
> > seconds, but the report still takes ~22 seconds to process.
> >
> > Any thoughts?
> >
> > Thanks,
> >
> > Burt
> >
> >
> > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > What format are you using? From the execution log, where is most of the
> time
> > > spent, processing, rendering?
> > >
> > > --
> > > Tudor Trufinescu
> > > Dev Lead
> > > Sql Server Reporting Services
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > I have a table report based on a single 50,000 row table. The report
> > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > ExecutionLog table shows the report took < 1 second to pull the data,
> > > > but 22 seconds to process the report.
> > > >
> > > > Given that my high end server can perform millions of calcs per
> > > > second, does anyone know why my report would take so long to process?
> > > > The error logs show nothing unusual.
> > > >
> > > > Thanks,
> > > >
> > > > Burt|||I don't think this would make a difference but how hard would it be to start
anew with the report. I have had issues where I have added a group, removed
it etc and everything did not remove from the rdl. Just grasping at straws
here. I would think MS would definitely be interested in what you have here.
Bruce L-C
"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0408250907.43140876@.posting.google.com...
> Thanks, Bruce, but no, no filters. Some additional info:
> -There are actually only 18K rows in the table, not 50K, and 6 groups,
> not 5.
> -A couple of guys on the RS dev team from MS were kind enough to take
> a look at the rdl, but didn't spot anything unusual.
> -I created a similar 6 group report in MS Access, which took only a
> couple of seconds to create.
> -I have a 600K zip file with the RDL, table DDL, and actual table data
> if anyone wants to play with it.
> Thanks,
> Burt
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > Just to make sure, are you using filters in your report? Filters bring
over
> > all the data (in your case 50,000 rows) prior to doing anything with the
> > data. Then it works on the data (doing the filtering, grouping etc).
> >
> > Bruce L-C
> >
> > "Burt" <burt_5920@.yahoo.com> wrote in message
> > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > Hi Tudor,
> > >
> > > The format is HTML. Both rendering and the data pull from SQL Server
> > > take less than a second. The report processing takes 22 seconds.
> > >
> > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > seconds, but the report still takes ~22 seconds to process.
> > >
> > > Any thoughts?
> > >
> > > Thanks,
> > >
> > > Burt
> > >
> > >
> > > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> > news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > What format are you using? From the execution log, where is most of
the
> > time
> > > > spent, processing, rendering?
> > > >
> > > > --
> > > > Tudor Trufinescu
> > > > Dev Lead
> > > > Sql Server Reporting Services
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > >
> > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > I have a table report based on a single 50,000 row table. The
report
> > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > ExecutionLog table shows the report took < 1 second to pull the
data,
> > > > > but 22 seconds to process the report.
> > > > >
> > > > > Given that my high end server can perform millions of calcs per
> > > > > second, does anyone know why my report would take so long to
process?
> > > > > The error logs show nothing unusual.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Burt|||Thanks, Bruce. I've actually created a few versions of the report with
similar results.
Per MS, RS is taking a (session) snapshot of the report and saving it
as they process the report, this is the most time consuming part of
this particular report.
Something tells me as their engine matures these reports will get
faster...
Burt
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<OjEnXBtiEHA.3972@.tk2msftngp13.phx.gbl>...
> I don't think this would make a difference but how hard would it be to start
> anew with the report. I have had issues where I have added a group, removed
> it etc and everything did not remove from the rdl. Just grasping at straws
> here. I would think MS would definitely be interested in what you have here.
> Bruce L-C
> "Burt" <burt_5920@.yahoo.com> wrote in message
> news:19e5f39f.0408250907.43140876@.posting.google.com...
> > Thanks, Bruce, but no, no filters. Some additional info:
> >
> > -There are actually only 18K rows in the table, not 50K, and 6 groups,
> > not 5.
> >
> > -A couple of guys on the RS dev team from MS were kind enough to take
> > a look at the rdl, but didn't spot anything unusual.
> >
> > -I created a similar 6 group report in MS Access, which took only a
> > couple of seconds to create.
> >
> > -I have a 600K zip file with the RDL, table DDL, and actual table data
> > if anyone wants to play with it.
> >
> > Thanks,
> >
> > Burt
> >
> >
> >
> >
> > "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:<O$OooBgiEHA.712@.TK2MSFTNGP09.phx.gbl>...
> > > Just to make sure, are you using filters in your report? Filters bring
> over
> > > all the data (in your case 50,000 rows) prior to doing anything with the
> > > data. Then it works on the data (doing the filtering, grouping etc).
> > >
> > > Bruce L-C
> > >
> > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > news:19e5f39f.0408240850.42e0d965@.posting.google.com...
> > > > Hi Tudor,
> > > >
> > > > The format is HTML. Both rendering and the data pull from SQL Server
> > > > take less than a second. The report processing takes 22 seconds.
> > > >
> > > > If I use an AS OLAP cube as the data source, the data pull takes 10
> > > > seconds, but the report still takes ~22 seconds to process.
> > > >
> > > > Any thoughts?
> > > >
> > > > Thanks,
> > > >
> > > > Burt
> > > >
> > > >
> > > > "Tudor Trufinescu \(MSFT\)" <tudortr@.ms.com> wrote in message
> news:<#KushWViEHA.1512@.TK2MSFTNGP10.phx.gbl>...
> > > > > What format are you using? From the execution log, where is most of
> the
> time
> > > > > spent, processing, rendering?
> > > > >
> > > > > --
> > > > > Tudor Trufinescu
> > > > > Dev Lead
> > > > > Sql Server Reporting Services
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > > >
> > > > >
> > > > > "Burt" <burt_5920@.yahoo.com> wrote in message
> > > > > news:19e5f39f.0408231024.4c58f3d@.posting.google.com...
> > > > > > I have a table report based on a single 50,000 row table. The
> report
> > > > > > doesn't do anything unusual, but there are 5 grouping levels. The
> > > > > > ExecutionLog table shows the report took < 1 second to pull the
> data,
> > > > > > but 22 seconds to process the report.
> > > > > >
> > > > > > Given that my high end server can perform millions of calcs per
> > > > > > second, does anyone know why my report would take so long to
> process?
> > > > > > The error logs show nothing unusual.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Burt