Inserting via a view

Hello All, I have an strange issue regarding inserts via a view. Below are
the environemnts and the code. Both environments are identical however,
service packs a different.
Environment 1: This environmnet the insert works
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Windows 2k sp4
Environment 2: This envirnoment the insert fails
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
Here is the insert statment;
INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
R.RestrictionType,
R.SecAssocType,
R.RestrictionName,
R.CanOverride,
D.TranCodeBits,
D.MinCompRestPercent,
D.MaxCompRestPercent,
D.IsAggregate,
D.RoundOption,
D.RoundFactor,
D.MinShares,
D.MinValue,
D.CompType)
VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+000
,
0.000000000000000e+000, 0)
Here is the code to the view
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER VIEW vMoxyStandardRestriction AS
SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
R.RestrictionName, R.CanOverride,
D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggregate,
D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
FROM MoxyRestriction R, MoxyRestDef D
WHERE R.RestrictionID = D.RestrictionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThis is the error message in the environment which does not work.
Server: Msg 4405, Level 16, State 2, Line 1
View or function 'vMoxyStandardRestriction' is not updatable because the
modification affects multiple base tables.
"FredG" wrote:

> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+0
00,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggreg
ate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||Hi Fred
Your error messages seems pretty explicit. You cannot insert into a view
that is based on a join. If you could, your single insert would have to put
rows into both the underlying tables, and according to BOL:
[If you insert into a view] .. the modifications made by the INSERT
statement cannot affect more than one of the base tables referenced in the
FROM clause of the view. For example, an INSERT into a multitable view must
use a column_list that references only columns from one base table.
If you are not getting the message for one of your servers, that is the
weirdness. Perhaps you have an INSTEAD OF TRIGGER on the view on that
server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:B1BE7DA5-30D1-41F7-8C63-E213DA147DFA@.microsoft.com...
> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000,
> 0.000000000000000e+000,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>