Microsoft KB Archive/118413

{|
 * width="100%"|

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