January 19, 2011 at 7:12 am
Hi all,
I am facing a problem with set concat_null_yield_null option
I am having a trigger, which is update, a null value field in a table with a string at the time of inserts into a table and update it with the string if value exists already.
Scenario :-
Table: Projects, Projectsdata
Inside the trigger whenever a value is inserted into projects table the corresponding data has been inserted into Projectsdata table. And if data has updated in Projects table corresponding data has been updated in Projects data table. I have set option concat_null_yield_null off inside the trigger.
So when record is inserted data has been populated in projectsdata table and if it is updated the following error is coming: -
“UPDATE failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.Unable to Insert recored to WI_ProjectUsers :”
And when I make it as concat_null_yield_null on or commented the code
-- concat_null_yield_null off
inside the trigger, the value is not inserted in the projectsdata table it is coming as null. L
How to solve the problem
Any help will be highly appreciated..
January 19, 2011 at 7:47 am
Any one of the values that you are concatenating might be null.
put a isnull check of that value.
isnull(null_value,'')
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
January 19, 2011 at 10:31 pm
DDL:
USE [PalladiumShare]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WI_Projects](
[ProjectID] [bigint] IDENTITY(11111,1) NOT NULL,
[ProjectCode] [nvarchar](50) NULL,
[ProjectLocation] [nvarchar](255) NULL,
[ProjectType] [nvarchar](200) NULL,
[ProjectName] [nvarchar](200) NULL,
[ProjectDescription] [nvarchar](max) NULL,
[ProjectStartDate] [datetime] NULL,
[ProjectEndDate] [datetime] NULL,
[ProjectTotalHours] [float] NULL,
[ProjectStatusID] [bigint] NOT NULL,
[CompanyID] [bigint] NOT NULL,
[ClientProjectSponsor] [nvarchar](200) NULL,
[ClientProjectLead] [nvarchar](200) NULL,
[Situation] [nvarchar](max) NULL,
[Solution] [nvarchar](max) NULL,
[Benefits] [nvarchar](max) NULL,
[Lessons] [nvarchar](max) NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_WI_Projects_IsActive] DEFAULT ((1)),
[IsArchived] [bit] NOT NULL CONSTRAINT [DF_WI_Projects_IsArchived] DEFAULT ((0)),
[CreatedDateTime] [datetime] NOT NULL CONSTRAINT [DF_WI_Projects_CreatedDateTime] DEFAULT (getdate()),
[CreatedById] [bigint] NOT NULL,
[CreatedByUserName] [nvarchar](255) NOT NULL,
[LastModifiedDateTime] [datetime] NOT NULL CONSTRAINT [DF_WI_Projects_LastModifiedDateTime] DEFAULT (getdate()),
[LastModifiedById] [bigint] NOT NULL,
[LastModifiedByUserName] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_WI_Projects] PRIMARY KEY CLUSTERED
(
[ProjectID] 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].[WI_Projects] WITH CHECK ADD CONSTRAINT [FK_WI_Project_LKP_Generic_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[LKP_Generic] ([LKPID])
GO
ALTER TABLE [dbo].[WI_Projects] CHECK CONSTRAINT [FK_WI_Project_LKP_Generic_ProjectStatus]
GO
ALTER TABLE [dbo].[WI_Projects] WITH CHECK ADD CONSTRAINT [FK_WI_Project_WI_Companies] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[WI_Companies] ([CompanyID])
GO
ALTER TABLE [dbo].[WI_Projects] CHECK CONSTRAINT [FK_WI_Project_WI_Companies]
--------------------------------------
USE [PalladiumShare]
GO
/****** Object: Table [dbo].[WI_ProjectsData] Script Date: 01/20/2011 10:42:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WI_ProjectsData](
[ProjectId] [bigint] NOT NULL,
[ProjectCode] [nvarchar](50) NULL,
[ProjectLocation] [nvarchar](255) NULL,
[ProjectType] [nvarchar](200) NULL,
[ProjectName] [nvarchar](200) NULL,
[ProjectDescription] [nvarchar](max) NULL,
[ProjectStartDate] [nvarchar](100) NULL,
[ProjectEndDate] [nvarchar](100) NULL,
[ProjectTotalHours] [nvarchar](100) NULL,
[ProjectStatusID] [nvarchar](100) NULL,
[ProjectStatus] [nvarchar](100) NULL,
[CompanyID] [nvarchar](100) NULL,
[CompanyName] [nvarchar](100) NULL,
[ClientProjectSponsor] [nvarchar](200) NULL,
[ClientProjectLead] [nvarchar](200) NULL,
[Situation] [nvarchar](max) NULL,
[Solution] [nvarchar](max) NULL,
[Benefits] [nvarchar](max) NULL,
[Lessons] [nvarchar](max) NULL,
[UserIds] [nvarchar](1000) NULL,
[Users] [nvarchar](1000) NULL,
[RoleIds] [nvarchar](1000) NULL,
[Roles] [nvarchar](1000) NULL,
[ArtifactIds] [nvarchar](1000) NULL,
[LinkURLs] [nvarchar](1000) NULL,
[LinkTexts] [nvarchar](1000) NULL,
[TechnologyIds] [nvarchar](1000) NULL,
[Technologies] [nvarchar](1000) NULL,
[RegionIds] [nvarchar](1000) NULL,
[Regions] [nvarchar](1000) NULL,
[PartnerIds] [nvarchar](1000) NULL,
[Partners] [nvarchar](1000) NULL,
[FunctionalAreaIds] [nvarchar](1000) NULL,
[FunctionalAreas] [nvarchar](1000) NULL,
[PracticeAreaIds] [nvarchar](1000) NULL,
[PracticeAreas] [nvarchar](1000) NULL,
[SolutionTypeIds] [nvarchar](1000) NULL,
[SolutionTypes] [nvarchar](1000) NULL,
[IndustryIds] [nvarchar](1000) NULL,
[Industries] [nvarchar](1000) NULL,
[IsArchived] [bit] NULL,
[CreatedDateTime] [datetime] NULL,
[CreatedById] [bigint] NULL,
[CreatedByUserName] [nvarchar](255) NULL,
[LastModifiedDateTime] [datetime] NULL,
[LastModifiedById] [bigint] NULL,
[LastModifiedByUserName] [nvarchar](255) NULL,
CONSTRAINT [PK_WI_Projectsdata] PRIMARY KEY CLUSTERED
(
[ProjectId] 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].[WI_ProjectsData] WITH CHECK ADD CONSTRAINT [FK_WI_Projectdata_WI_Projects] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[WI_Projects] ([ProjectID])
GO
ALTER TABLE [dbo].[WI_ProjectsData] CHECK CONSTRAINT [FK_WI_Projectdata_WI_Projects]
---------------------------
USE [PalladiumShare]
GO
/****** Object: Table [dbo].[LKP_Generic] Script Date: 01/20/2011 10:54:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LKP_Generic](
[LKPID] [bigint] IDENTITY(11111,1) NOT NULL,
[Feature] [nvarchar](25) NOT NULL,
[FieldName] [nvarchar](25) NOT NULL,
[DisplayValue] [nvarchar](255) NOT NULL,
[Abbrevation] [nchar](10) NULL,
[ParentLKPID] [bigint] NULL,
[IsVisible] [bit] NOT NULL CONSTRAINT [DF_LKP_Generic_IsVisible] DEFAULT ((1)),
[IsArchived] [bit] NOT NULL CONSTRAINT [DF_LKP_Generic_IsArchived] DEFAULT ((0)),
[CreatedDateTime] [datetime] NOT NULL CONSTRAINT [DF_LKP_Generic_CreatedDateTime] DEFAULT (getdate()),
[CreatedById] [bigint] NOT NULL,
[CreatedByUserName] [nvarchar](255) NOT NULL,
[LastModifiedDateTime] [datetime] NOT NULL CONSTRAINT [DF_LKP_Generic_LastModifiedDateTime] DEFAULT (getdate()),
[LastModifiedById] [bigint] NOT NULL,
[LastModifiedByUserName] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_LKP_Generic] PRIMARY KEY CLUSTERED
(
[LKPID] 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]
------------------------------------------------
USE [PalladiumShare]
GO
/****** Object: Table [dbo].[Temp_WI_ProjectMapping] Script Date: 01/20/2011 10:55:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp_WI_ProjectMapping](
[ProjectId] [bigint] NULL,
[OldProjectId] [bigint] NULL,
[FieldName] [nvarchar](255) NULL,
[OldFieldName] [nvarchar](255) NULL,
[FieldValue] [bigint] NULL,
[OldFieldValue] [bigint] NULL,
[IsActive] [bit] NULL,
[IsArchived] [bit] NULL,
[CreatedDateTime] [datetime] NULL,
[CreatedById] [bigint] NULL,
[OldCreatedById] [bigint] NULL,
[CreatedByUserName] [nvarchar](255) NULL,
[LastModifiedDateTime] [datetime] NULL,
[LastModifiedById] [bigint] NULL,
[LastModifiedByUserName] [nvarchar](255) NULL
) ON [PRIMARY]
Sample data:
LKP_Generic
LKPIDFeatureFieldNameDisplayValueAbbrevationParentLKPID
11111WINSINDEXIndustriesUnknownNULLNULL
11112WINSINDEXIndustriesBusiness ServicesNULLNULL
12006WINSINDEXFunctional AreasStrategic PlanningNULLNULL
12007WINSINDEXFunctional AreasMerchandise PlanningNULLNULL
12008WINSINDEXFunctional AreasStore OperationsNULLNULL
12009WINSINDEXFunctional AreasSales/MarketingNULLNULL
13007WINSINDEXPractice AreasBusiness IntelligenceNULLNULL
13008WINSINDEXPractice AreasPerformance ManagementNULLNULL
13009WINSINDEXPractice AreasUnknownNULLNULL
14001WINSINDEXProject RolesBusiness Development ExecutiveNULLNULL
14002WINSINDEXProject RolesAccount ManagerNULLNULL
14003WINSINDEXProject RolesPractice LeadNULLNULL
14004WINSINDEXProject RolesSubject Matter ExpertNULLNULL
ProjectMapping:
ProjectIdFieldNameFieldValueIsActiveIsArchived
11111Solution Types1500410
11111Technologies1604810
11111Technologies1605510
11112Functional Areas1200210
11112Industries1117410
11116Technologies1600210
11116Technologies1600310
11116Technologies1602910
11117Functional Areas1200210
11117Industries1114110
11117Partners1148510
Here the case is in Projects table data realted to projects are saved as you can see in the ddl.
And another 2 tables are there LKP_Generic, ProjectMapping
Where in LKP_Generic all the lookups are saved like industries, functional areas,practice areas, projectroles etc.
And in ProjectMapping as per the corrosponding project, lookupids are saved. Because for a single project multiple Lookup values may exist that’s why I make it a different table to reduce the redundancy.
Idea behind making a projectsdata table is, for a single project all the related data should be saved. Like project with all industries and project with all technologies,
All the lookups are saved as commaseprated values as per projectid. I want to make a freetext search on this table that’s why I make another table otherwise I can make it a view. To perform freetext search on a view has to be schema binded but if you generate a commaseprated list it will be a sub query. Which will not allow to perform schemabinding option in a view.(if you have any other idea do share.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply