By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,276 Members | 1,406 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,276 IT Pros & Developers. It's quick & easy.

INSERT failed because of incorrect SET options

P: n/a
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_IDENTIFIER'.

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_IDENTIFIER, ARITHABORT'.

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

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

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

Here are the relevant definitions:

TABLE:
CREATE TABLE [dbo].[tblDistributionMaster] (
[fldDistributionID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDocumentID] [int] NULL ,
[fldDocumentType] [int] NULL ,
[fldDocumentTitle] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDocumentSiteID] [int] NULL ,
[fldActive] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDistributionMaster] 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
[fldDocumentSiteID],
CONSTRAINT [DF__Temporary__fldAc__2A164134] DEFAULT (1) FOR
[fldActive],
CONSTRAINT [aaaaatblDistributionMaster_PK] PRIMARY KEY NONCLUSTERED
(
[fldDistributionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldDistributionID] ON
[dbo].[tblDistributionMaster]([fldDistributionID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO

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

CREATE INDEX [fldDocumentSiteID] ON
[dbo].[tblDistributionMaster]([fldDocumentSiteID]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO

CREATE INDEX [fldDocumentType] ON
[dbo].[tblDistributionMaster]([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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_1A_3A] ON
[dbo].[tblDistributionMaster] ([fldDistributionID], [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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_3A_1A] ON
[dbo].[tblDistributionMaster] ([fldDocumentType], [fldDistributionID])
')
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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_2A_1A] ON
[dbo].[tblDistributionMaster] ([fldDocumentID], [fldDistributionID]) ')
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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_3A_2A] ON
[dbo].[tblDistributionMaster] ([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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_2A_3A] ON
[dbo].[tblDistributionMaster] ([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 (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_37575172_1A_2A_3A] ON
[dbo].[tblDistributionMaster] ([fldDistributionID], [fldDocumentID],
[fldDocumentType]) ')
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER "tblDistributionMaster_UTrig" ON
dbo.tblDistributionMaster FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblJobs' */
IF UPDATE(fldDistributionID)
BEGIN
IF (SELECT COUNT(*) FROM deleted, tblJobs WHERE
(deleted.fldDistributionID = tblJobs.fldDistributionID)) > 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_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER "tblDistributionMaster_DTrig" ON
dbo.tblDistributionMaster FOR DELETE AS
SET NOCOUNT ON
/* * CASCADE DELETES TO 'tblJobs' */
DELETE tblJobs FROM deleted, tblJobs WHERE deleted.fldDistributionID =
tblJobs.fldDistributionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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

CREATE PROCEDURE stpAddNewDistributionMaster

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

AS

INSERT INTO tblDistributionMaster
(fldDocumentID,
fldDocumentType,
fldDocumentTitle,
fldActive,
fldDocumentSiteID)
VALUES
(@DocumentID,
@DocumentType,
@Title,
@Active,
@SiteID)

SET @DistributionID = IDENT_CURRENT('tblDistributionMaster')
GO

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

Thanks in advance

Edward

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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_IDENTIFIER 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

Jul 23 '05 #2

P: n/a


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_IDENTIFIER 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].[tbldistributiondetail].[fldarchived] as _hypmv_1_col_1,
[dbo].[tbldistributiondetail].[fldcompanyid] as _hypmv_1_col_2,
[dbo].[tbldistributiondetail].[fldnocdroms] as _hypmv_1_col_3,
[dbo].[tbldistributiondetail].[fldnopaper] as _hypmv_1_col_4,
[dbo].[tbldistributiondetail].[fldrevisionnumber] as _hypmv_1_col_5,
[dbo].[tbldistributiondetail].[flddocumentnumber] as _hypmv_1_col_6,
[dbo].[tblcompany].[fldcompanyname] as _hypmv_1_col_7,
[dbo].[tbldistributionmaster].[flddocumentid] as _hypmv_1_col_8,
[dbo].[tbldistributionmaster].[flddocumenttype] as _hypmv_1_col_9,
[dbo].[tbldistributionmaster].[flddistributionid] as _hypmv_1_col_10,
count_big(*) as _hypmv_1_col_11 FROM [dbo].[tbldistributionmaster],
[dbo].[tblcompany], [dbo].[tbldistributiondetail] WHERE (
[dbo].[tbldistributionmaster].[flddistributionid] =
[dbo].[tbldistributiondetail].[flddistributionid] ) AND (
[dbo].[tbldistributiondetail].[fldcompanyid] =
[dbo].[tblcompany].[fldcompanyid] ) GROUP BY
[dbo].[tbldistributiondetail].[fldarchived],
[dbo].[tbldistributiondetail].[fldcompanyid],
[dbo].[tbldistributiondetail].[fldnocdroms],
[dbo].[tbldistributiondetail].[fldnopaper],
[dbo].[tbldistributiondetail].[fldrevisionnumber],
[dbo].[tbldistributiondetail].[flddocumentnumber],
[dbo].[tblcompany].[fldcompanyname],
[dbo].[tbldistributionmaster].[flddocumentid],
[dbo].[tbldistributionmaster].[flddocumenttype],
[dbo].[tbldistributionmaster].[flddistributionid]

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

Jul 23 '05 #3

P: n/a


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_IDENTIFIER 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

Jul 23 '05 #4

P: n/a
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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.