locked
RDLC Error RRS feed

  • Question

  • User120353699 posted

    I know from other posts what this error is, but I am having a lot of trouble resolving it in my SQL Server 2008 database:

    An error has occurred during report processing.
    Exception has been thrown by the target of an invocation.
    Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

    The report is used for site documentation and contains 4 tables; the site table will only ever retreive a single site record, where the other 3 will retrieve multiple child records  from various related tables.  I've removed each  report table and its associated dataset in order to narrow down the offending recordset and found that it is the site table.  In an attempt to find the field causing the problem for the single record in question, I have verfied no fields are null that are not allowed to be null (there were none) but even then set all null fields to zero length strings anyway.  I have even used isnull(field) in the view.  Also in the view, instead of using temp data tables, i used inline queries as the field, for example:

    Select MyField, (Select ThatField From ThatTable Where blah = blah) From MyTable

    with no change to the error.

    The xsd dataset points to a view, the structure of  the view is:

    ALTER VIEW [dbo].[SiteData] AS
    SELECT     dtSite.ID as SiteID, CASE SiteManaged WHEN 0 THEN 'Unmanaged' WHEN 1 THEN 'Managed' WHEN 2 THEN 'Active Unmanaged' END AS SiteStatus, 
    	dtSdpType.SdpType, dtNetworkType.NetworkType, VTNSID, 
    	SiteCode, Site, Address1, Address2, Address3, City, dtState.State, 
                    ZIP, dtCountry.Country, dtRegion.Region, SitePOC, SitePOCTel, SitePOCEmail, 
    	AltPOC, AltPOCTel, AltPOCEmail, SiteMainTel, SiteFax, 
                    NetManTier, NetManCplxty, NetManMaintLvl, 
                    NetManPR1, NetManPR2, NetManPR3, NetManDevOther, NetmanPS1, NetmanPS2, 
                    NetManEncR1, NetManEncR2, NetManEncR3, Notes
    FROM       (Select * From Site With (NOLOCK)) dtSite LEFT OUTER JOIN
                    (Select SdpType, ID From ADetailsList With (NOLOCK)) dtSdpType ON dtSite.SiteSDPType_ID = dtSdpType.ID LEFT OUTER JOIN
                    (Select Region, ID From ADetailsList With (NOLOCK)) dtRegion ON dtSite.SiteRegion_ID = dtRegion.ID LEFT OUTER JOIN
                    (Select Country, ID From SiteCountry With (NOLOCK)) dtCountry ON dtSite.SiteCountry_ID = dtCountry.ID LEFT OUTER JOIN
                    (Select State, ID From SiteState With (NOLOCK)) dtState ON SiteState_ID = dtState.ID LEFT OUTER JOIN
    	(Select NetworkType, ID From ADetailsList With (NOLOCK)) dtNetworkType ON dtSite.NetworkType_ID = dtNetworkType.ID
    GO

    Here is the table structure and constraints of the table in question (the bold items are the only null values in the record I am having trouble with):

    CREATE TABLE [dbo].[Site](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[SiteCode] [nvarchar](50) NOT NULL,
    	[VTNSID] [nvarchar](10) NULL,
    	[Site] [nvarchar](50) NULL,
    	[Address1] [nvarchar](50) NULL,
    	[Address2] [nvarchar](50) NULL,
    	[Address3] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[ZIP] [nvarchar](50) NULL,
    	[CityCode] [nvarchar](50) NULL,
    	[SitePOC] [nvarchar](50) NULL,
    	[SitePOCTel] [nvarchar](50) NULL,
    	[SitePOCEmail] [nvarchar](50) NULL,
    	[AltPOC] [nvarchar](50) NULL,
    	[AltPOCTel] [nvarchar](50) NULL,
    	[AltPOCEmail] [nvarchar](50) NULL,
    	[SiteMainTel] [nvarchar](50) NULL,
    	[SiteFax] [nvarchar](50) NULL,
    	[Notes] [nvarchar](4000) NULL, 	[NetManCplxty] [int] NULL,
    	[NetManTier] [int] NULL,
    	[NetManMaintLvl] [int] NULL,
    	[NetManPR1] [bit] NULL,
    	[NetManPR2] [bit] NULL,
    	[NetManPR3] [bit] NULL,
    	[NetManDevOther] [bit] NULL,
    	[NetmanPS1] [bit] NULL,
    	[NetmanPS2] [bit] NULL,
    	[NetManEncR1] [bit] NULL,
    	[NetManEncR2] [bit] NULL,
    	[NetManEncR3] [bit] NULL,
    	[SiteManaged] [int] NOT NULL,
    	[SiteCountry_ID] [int] NOT NULL,
    	[SiteRegion_ID] [int] NOT NULL,
    	[SiteState_ID] [int] NOT NULL,
    	[SiteSDPType_ID] [int] NOT NULL,
    	[NetworkType_ID] [int] NOT NULL,
     CONSTRAINT [PK_Site] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManCplxty]  DEFAULT ((0)) FOR [NetManCplxty]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManTier]  DEFAULT ((0)) FOR [NetManTier]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManMaintLvl]  DEFAULT ((0)) FOR [NetManMaintLvl]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManPR1]  DEFAULT ((0)) FOR [NetManPR1]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManPR2]  DEFAULT ((0)) FOR [NetManPR2]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManPR3]  DEFAULT ((0)) FOR [NetManPR3]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManDevOther]  DEFAULT ((0)) FOR [NetManDevOther]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetmanPS1]  DEFAULT ((0)) FOR [NetmanPS1]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetmanPS2]  DEFAULT ((0)) FOR [NetmanPS2]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManEncR1]  DEFAULT ((0)) FOR [NetManEncR1]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManEncR2]  DEFAULT ((0)) FOR [NetManEncR2]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetManEncR3]  DEFAULT ((0)) FOR [NetManEncR3]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_SiteManaged]  DEFAULT ((0)) FOR [SiteManaged]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_SiteCountry_ID]  DEFAULT ((0)) FOR [SiteCountry_ID]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_SiteRegion_ID]  DEFAULT ((0)) FOR [SiteRegion_ID]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_SiteState_ID]  DEFAULT ((0)) FOR [SiteState_ID]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_SiteSDPType_ID]  DEFAULT ((0)) FOR [SiteSDPType_ID]
    GO
    
    ALTER TABLE [dbo].[Site] ADD  CONSTRAINT [DF_Site_NetworkType_ID]  DEFAULT ((0)) FOR [NetworkType_ID]
    GO
    
    


    Although I have over 2900 records in the underlying sql table, this appears to be the only site giveing me trouble.  I have looked for other sites with the same null values and found none, but even after updating the null fields with zero length strings, I'm having a spot of trouble.  Any ideas would be most welcome!

     

    thanks,

     

    ejo

    Thursday, May 3, 2012 6:23 PM

All replies

  • User627829463 posted

    sometimes xsd dataset auto create some Unique value for some fields. make sure only the Primary key mark as Unique = true, all other should be false.

    Thursday, May 3, 2012 7:37 PM
  • User120353699 posted

    The primary key is the only item marked as you suggest with all other fields being false.  No change in the error.

    Any idea how to determine what field is throwing the error in the rdlc?  I've not had much luck in finding information on how to trace that like a person might do with the debugger.

    Tuesday, May 8, 2012 5:28 PM
  • User120353699 posted

    Hoping someone out there can offer another idea as to how to fix this issue.

    I have run all the associated views with the specific parent record in question and nothing complains.

    I can see all the data in the forms of my web application, parent and child.

    I can run all the xsd datasets from dataset designer and see all the data, parent and child (the unique index issue already suggested has been checked).

    The only complaint recieved is the error in the report processing (see original post) and its error has little left to be desired in terms of providing much more assistance than "you have an error".

     

    The documentation produced by the report is due in 3 days and is a mandatory item--is there ANYONE with another idea?

     

    Thanks,

    ejo

    Friday, May 11, 2012 3:04 PM
  • User120353699 posted

    After a lot of eye strain, I finally discovered the data point causing the error, but I am entirely at a loss to explain it.

    The data point in the table (as shown in the op) is this:

    [SiteManaged] [int] NOT NULL

    It takes a value of 0, 1 or 2 in the web form that is used to give its value.  The view that uses the information does so with this bit of sql (also as shown in the op):

     CASE SiteManaged WHEN 0 THEN 'Unmanaged' WHEN 1 THEN 'Managed' WHEN 2 THEN 'Active Unmanaged' END AS SiteStatus

    Of the nearly 3000 records in this table, the 24 which have the value of 2 in SiteManaged all bomb this report.  The odd thing is that this field isn't even used in this particular rdlc report.  It is part of the data set info contained in the xml markup of the rdlc file:

    <DataSet Name="Reports_SiteData">
         <Fields>
     ...
            <Field Name="SiteStatus">
              <DataField>SiteStatus</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
     ...
          </Fields>
     ...
    </DataSet> 

    Can anyone explain why the legitimate value well within the range for an integer value would cause an error in the report for a field that isn't even used in the rdlc itself (i.e., no code, etc), particularly since it isn't even passing the integer value, but the string that the view has translated it to?

     

    thanks again,

     

    ejo

    Monday, May 14, 2012 5:03 PM