Data Masking in SQL Server 2005

Question

Good Day!

Need help. Hope you have time for this. I was getting an error message while trying to MASK a data in SQL Server 2005. I use UPDATE and REPLACE statements to MASK a data. It is working on my test database but when I tried it on live database I get an error message. Please see details below.

Syntax:

• UPDATE TABLENAME SET COLUMNNAME = REPLACE(COLUMNNAME, 'A', '[')

Error:

• Msg 512, Level 16, State 1, Procedure cmf_upd_trg, Line 10 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

Thank you very much!

Answer 1

The real suspect is that you are having a trigger on that table that is designed to handle a single update.

---

Thanks and regards, Rishabh K

Answer 2

Hi! Thank you for your reply. May i know what to do? Ü

Mark.

Answer 3

Have a look inside the trigger cmf_upd_trg code.

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

Thanks for all your responses. Let me try to look for that cmf_upd_trg. Ü

Answer 5

Hi! wasn't able to locate that cmf_upd_trg. Seems my SQL statement only allows me to update only one record. I'm getting this error message whenever I update 2 or more records.

Syntax: UPDATE TABLENAME SET COLUMNNAME = REPLACE(COLUMNNAME, 'A', '[')

Thank you very much! Ü

Answer 6

In SSMS first find the table in the Object Explorer, then click on the expand it and look under triggers to find the trigger that is written wrongly.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Hi everyone! I was able to locate cmf_upd_trg. I see a lot of codings their. Do I need to edit something there or would you know what to do next? Thank you very much. Ü

Answer 8

Post that trigger's code, we will be able to find the problem.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

Hi everyone!

Thank you for your time responding to my queries. I was able to update my database. I did not edit anthing in cmf_upd_trg code. I disable it then I execute my REPLACE funtion. 

UPDATE TABLENAME SET COLUMNNAME = REPLACE(COLUMNNAME, 'A', '[')

Again, thank you very much for all the support.

Sincerely,

Mark

Answer 10

If that trigger was there for a reason, the better solution would have been to correct that trigger's code. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Thank you for your advise. I will ask our Database Administrator about it. But for now I just disable it and enable it after I MASK everything. Thanks a lot! Ü

Mark