INSERT failed because of incorrect SET options

[teddysnips]

I am trying to insert a row into a table using a stored procedure and I
get the following error if I try this from QA:

INSERT failed because the following SET options have incorrect
settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER'.

If I try to run this from Microsoft Access, I get a slightly different
error:

INSERT failed because the following SET options have incorrect
settings: 'ANSI_NULLS., QUOTED_IDENTIFI ER, ARITHABORT'.

This is what I'm trying to run in QA:

declare @P1 int
set @P1=NULL
exec stpAddNewDistri butionMaster 142, 2, 'INTRODUCTION OF FILTER
ASSEMBLY', 0, 1, @P1 output
select @P1

=============== ============

Here are the relevant definitions:

TABLE:
CREATE TABLE [dbo].[tblDistribution Master] (
[fldDistribution ID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDocumentID] [int] NULL ,
[fldDocumentType] [int] NULL ,
[fldDocumentTitl e] [varchar] (255) COLLATE
SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldDocumentSite ID] [int] NULL ,
[fldActive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDistribution Master] WITH NOCHECK ADD
CONSTRAINT [DF__Temporary__ fldDo__2739D489] DEFAULT (0) FOR
[fldDocumentID],
CONSTRAINT [DF__Temporary__ fldDo__282DF8C2] DEFAULT (0) FOR
[fldDocumentType],
CONSTRAINT [DF__Temporary__ fldDo__29221CFB] DEFAULT (0) FOR
[fldDocumentSite ID],
CONSTRAINT [DF__Temporary__ fldAc__2A164134] DEFAULT (1) FOR
[fldActive],
CONSTRAINT [aaaaatblDistrib utionMaster_PK] PRIMARY KEY NONCLUSTERED
(
[fldDistribution ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldDistribution ID] ON
[dbo].[tblDistribution Master]([fldDistribution ID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO

CREATE INDEX [fldDocumentID] ON
[dbo].[tblDistribution Master]([fldDocumentID]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [fldDocumentSite ID] ON
[dbo].[tblDistribution Master]([fldDocumentSite ID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO

CREATE INDEX [fldDocumentType] ON
[dbo].[tblDistribution Master]([fldDocumentType]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_1 A_3A] ON
[dbo].[tblDistribution Master] ([fldDistribution ID], [fldDocumentType])
')
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_3 A_1A] ON
[dbo].[tblDistribution Master] ([fldDocumentType], [fldDistribution ID])
')
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_2 A_1A] ON
[dbo].[tblDistribution Master] ([fldDocumentID], [fldDistribution ID]) ')
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_3 A_2A] ON
[dbo].[tblDistribution Master] ([fldDocumentType], [fldDocumentID]) ')
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_2 A_3A] ON
[dbo].[tblDistribution Master] ([fldDocumentID], [fldDocumentType]) ')
GO

/****** The index created by the following statement is for internal
use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftver sion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_1 A_2A_3A] ON
[dbo].[tblDistribution Master] ([fldDistribution ID], [fldDocumentID],
[fldDocumentType]) ')
GO

SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER "tblDistributio nMaster_UTrig" ON
dbo.tblDistribu tionMaster FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblJobs' */
IF UPDATE(fldDistr ibutionID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblJobs WHERE
(deleted.fldDis tributionID = tblJobs.fldDist ributionID)) > 0
BEGIN
RAISERROR 44446 'The record can''t be deleted or
changed. Since related records exist in table ''tblJobs'', referential
integrity rules would be violated.'
ROLLBACK TRANSACTION
END
END
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER "tblDistributio nMaster_DTrig" ON
dbo.tblDistribu tionMaster FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'tblJobs' */
DELETE tblJobs FROM deleted, tblJobs WHERE deleted.fldDist ributionID =
tblJobs.fldDist ributionID
GO
SET QUOTED_IDENTIFI ER OFF
GO
SET ANSI_NULLS ON
GO

=============== ============
SPROC:

CREATE PROCEDURE stpAddNewDistri butionMaster

@DocumentID int,
@DocumentType int,
@Title varchar(255),
@SiteID int,
@Active bit,
@DistributionID int OUTPUT

AS

INSERT INTO tblDistribution Master
(fldDocumentID,
fldDocumentType ,
fldDocumentTitl e,
fldActive,
fldDocumentSite ID)
VALUES
(@DocumentID,
@DocumentType,
@Title,
@Active,
@SiteID)

SET @DistributionID = IDENT_CURRENT(' tblDistribution Master')
GO

=============== ===============

Thanks in advance

Edward

[Simon Hayes]

That error can come up if you're inserting into a table with an indexed
view on it - could that be the case here? Also, since ANSI_NULLS and
QUOTED_IDENTIFI ER use the settings when the procedure was created, you
could recreate the proc with both those settings ON, to see if that
fixes it.

Simon

[Will]

Simon Hayes wrote:

That error can come up if you're inserting into a table with an indexed
view on it - could that be the case here? Also, since ANSI_NULLS and
QUOTED_IDENTIFI ER use the settings when the procedure was created, you
could recreate the proc with both those settings ON, to see if that
fixes it.

The only view is this;

CREATE VIEW [dbo].[_hypmv_1] WITH SCHEMABINDING AS SELECT
[dbo].[tbldistribution detail].[fldarchived] as _hypmv_1_col_1,
[dbo].[tbldistribution detail].[fldcompanyid] as _hypmv_1_col_2,
[dbo].[tbldistribution detail].[fldnocdroms] as _hypmv_1_col_3,
[dbo].[tbldistribution detail].[fldnopaper] as _hypmv_1_col_4,
[dbo].[tbldistribution detail].[fldrevisionnumb er] as _hypmv_1_col_5,
[dbo].[tbldistribution detail].[flddocumentnumb er] as _hypmv_1_col_6,
[dbo].[tblcompany].[fldcompanyname] as _hypmv_1_col_7,
[dbo].[tbldistribution master].[flddocumentid] as _hypmv_1_col_8,
[dbo].[tbldistribution master].[flddocumenttype] as _hypmv_1_col_9,
[dbo].[tbldistribution master].[flddistribution id] as _hypmv_1_col_10 ,
count_big(*) as _hypmv_1_col_11 FROM [dbo].[tbldistribution master],
[dbo].[tblcompany], [dbo].[tbldistribution detail] WHERE (
[dbo].[tbldistribution master].[flddistribution id] =
[dbo].[tbldistribution detail].[flddistribution id] ) AND (
[dbo].[tbldistribution detail].[fldcompanyid] =
[dbo].[tblcompany].[fldcompanyid] ) GROUP BY
[dbo].[tbldistribution detail].[fldarchived],
[dbo].[tbldistribution detail].[fldcompanyid],
[dbo].[tbldistribution detail].[fldnocdroms],
[dbo].[tbldistribution detail].[fldnopaper],
[dbo].[tbldistribution detail].[fldrevisionnumb er],
[dbo].[tbldistribution detail].[flddocumentnumb er],
[dbo].[tblcompany].[fldcompanyname],
[dbo].[tbldistribution master].[flddocumentid],
[dbo].[tbldistribution master].[flddocumenttype],
[dbo].[tbldistribution master].[flddistribution id]

which was created by the Index Tuning Wizard. Can I drop this view (I
don't reference it anywhere, and there are no objects that depend on
it)?

Thanks

Edward

[teddysnips]

Simon Hayes wrote:

That error can come up if you're inserting into a table with an indexed
view on it - could that be the case here? Also, since ANSI_NULLS and
QUOTED_IDENTIFI ER use the settings when the procedure was created, you
could recreate the proc with both those settings ON, to see if that
fixes it.

Simon

I dropped the View and all works fine now.

Thanks

Edward

[Simon Hayes]

Yes - I'm not familiar with the Index Tuning Wizard, but WITH
SCHEMABINDING and COUNT_BIG() are both used in indexed views, so it
looks as if this view was intended to support indexing, even if it
isn't right now.

Simon