Q118413 - BUG: Error 513 on Update/Insert on Join When No Rows Affected

BUG: Error 513 on Update/Insert on Join When No Rows Affected

Q118413

The information in this article applies to:

Microsoft SQL Server version 4.2x


BUG#  NT:  844 (4.2)

SYMPTOMS

If a table has a column of a user-defined data type with a rule associated with that data type, and if this table only has a single row, attempting to UPDATE or INSERT INTO this table based on a table join with another table may cause:

Message 513, Level 16, State 1
A column insert or update conflicts with a rule imposed by a previous
CREATE RULE command. The command was aborted.

The above error will occur even if that single row does not satisfy the join condition.

WORKAROUND

Instead of binding the rule to the data type, bind it to the table column only.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2.

MORE INFORMATION

For example, the following script generates error 513 even if no row should be affected:


   sp_addtype number_t, int
   go
   create rule number_t_rule as @num >= 0
   go
   sp_bindrule number_t_rule, number_t

   create table x (a number_t, b number_t)
   go
   create table y (c number_t)
   go

   insert x values(12,0)
   insert y values(13)
   go

   update x set b = b-1
   from x, y
   where x.a = y.c
   go

   insert x
   select a, b-1
   from x, y
   where x.a = y.c
   go

NOTE: If a join is not involved or if the table x has zero or multiple rows, the problem will not happen.

Additional query words:

Keywords : kbprogramming
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2