November 30, 2011 at 7:13 am
I get the error listed below when performing a SELECT INTO.
I never got an error on a SELECT INTO.:unsure:
Msg 1934, Level 16, State 1, Procedure Backup_Tables, Line 11
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2011 at 7:21 am
Is there any Indexed View defined on the table? Following issue is related to 'ARITHABORT' setting. It might be applicable to 'ANSI_NULLS' as well.
PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View
November 30, 2011 at 7:21 am
I tried creating a Backup Table with the Intent of Performing a INSERT INTO and I get the same error.
Please note that I did not create the original table.
CREATE TABLE [dbo].[tblIncentiveBak](
[ID] [int] NOT NULL,
[Group] [nvarchar](255) NULL,
[Bill to #] [nvarchar](255) NULL,
[Company] [nvarchar](255) NULL,
[PHONE] [nvarchar](255) NULL,
[FAX] [nvarchar](255) NULL,
[nvarchar](255) NULL
) ON [PRIMARY]
GO
I'm going to change the Lengths and Set each column to NOT NULL but I need to find out what is causing this.
Msg 1934, Level 16, State 1, Procedure Backup_Tables, Line 11
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2011 at 7:49 am
Dev (11/30/2011)
Is there any Indexed View defined on the table? Following issue is related to 'ARITHABORT' setting. It might be applicable to 'ANSI_NULLS' as well.PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View
I had to disable a DDL Database Trigger and it worked.
This is the script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Backup_Tables]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE--, DROP_TABLE
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [Backup_Tables] ON DATABASE
GO
ENABLE TRIGGER [Backup_Tables] ON DATABASE
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2011 at 7:54 am
Why did you disable the trigger? Altering it with matching ANSI_NULLS setting should be fine.
Will you not loose trigger functionality, if you disable it?
November 30, 2011 at 10:51 am
Dev (11/30/2011)
Why did you disable the trigger? Altering it with matching ANSI_NULLS setting should be fine.Will you not loose trigger functionality, if you disable it?
I deleted all of my triggers, they were causing errors when creating, altering and dropping tables.:sick:
You can get this information from the DMV's.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2011 at 5:19 pm
@dev all of thie information that I was gathering from the Database Triggers is stored in a DMV.
Do you know which one?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2011 at 6:58 am
Welsh Corgi (11/30/2011)
@Dev all of thie information that I was gathering from the Database Triggers is stored in a DMV.Do you know which one?
Thanks.
I didn't get you... 🙁
December 1, 2011 at 7:55 am
There is a Dynamic Management View named that I believe contains the words Information Schema that tracks changes to objects. It has everything that I was tracking in my DB Trigger with the exception of the Login.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply