473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

INSERT failed because of incorrect SET options

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
4 7003
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


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


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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Matt Rink | last post by:
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'." error after creating a view. We wanted a composite unique constraint that ignored nulls, so we set up a view using the...
1
by: Steve Foster | last post by:
I have tried many variations (after reviewing other posts) and can not resolve the following issue: RUNNING SQL MAINTENANCE ---------------------------- SET ARITHABORT ON SET...
7
by: Bercin Ates via SQLMonster.com | last post by:
I?m getting an error when I execute a stored procedure which is try to insert a row to a table. The error is: Server: Msg 1934, Level 16, State 1, Procedure SRV_SP_IS_EMRI_SATIRI_EKLE, Line 32...
6
by: pk | last post by:
Sorry for the piece-by-piece nature of this post, I moved it from a dormant group to this one and it was 3 separate posts in the other group. Anyway... I'm trying to bulk insert a text file of...
9
by: anachronic_individual | last post by:
Hi all, Is there a standard library function to insert an array of characters at a particular point in a text stream without overwriting the existing content, such that the following data in...
6
by: ilo | last post by:
When I want to delete a data from a table that this tabl has a trigger and this trigger reached another tables to delete the data in cursor I have this messeage: DELETE failed because the...
13
lee123
by: lee123 | last post by:
I have a form I have been working on and now I am almost done with it but there is just one thing I need to finish it that I can figure out. Well I have a questionnaire form with 50 questions and I...
2
by: Arun Srinivasan | last post by:
I need to speed up the inserts through informatica, and I came across insert buf option with db2 packages for import statements and how we can use that option to do the same in java programs. Are...
8
by: Red | last post by:
If auto-format is turned off in VS2008, there is apparently no way to indent a line. Under Tools->Options->Text Editor->C#->Formatting, there are three checkboxes. Unchecking those seems to cause...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.