Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ghanshyam007
Starting Member
6 Posts |
 Posted - 2011-01-19 : 08:57:34
|
| Hi all,I am facing a problem with set concat_null_yield_null optionI 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, ProjectsdataInside 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. LHow to solve the problem Any help will be highly appreciated.. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-19 : 09:52:09
|
| It is presumably because you are concatenating with a null value.trycoalesce(tbl.col,'') + coalesce(tbl1.col1,'')==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Need help SET CONCAT_NULL_YIELDS_NULL Issue