DELETE Failed Because The Following SET Options Have Incorrect Settings: 'QUOTED_IDENTIFIER'

Sep 20, 2006

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 following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.

My trigger :
CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]
FOR DELETE
AS
BEGIN
DECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINC
bigint
SELECT @rows_affected = @@ROWCOUNT
IF @rows_affected = 0
RETURN -- No rows changed, exit trigger
BEGIN
DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFIER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFIER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC

END
CLOSE Miktar
DEALLOCATE Miktar

END

END

View 6 Replies


ADVERTISEMENT

UPDATE Failed Because The Following SET Options Have Incorrect Settings: 'ANSI_NULLS'

Oct 1, 2007


Hi, i have problem as subject says.
Db has table with 3 columns, ID, Key and Val. ID is primary key, Key has unique index and Val simple holds value in text format.
I have created DAL layer using .netTiers and CodeSmith. Generated procedures.sql has before every procedure set ANSI_NULLS to OFF.
When i read rows from table i print them on screen. When user changes value, that should also be updated in database.
When i select entity, its value is changed.
Here is code snipper.



Code Blockentity.Key = key;
entity.Value = value;
TransactionManager transactionManager = DataRepository.Provider.CreateTransaction();
try
{
transactionManager.BeginTransaction();
retVal = DataRepository.TestTableProvider.Update(entity);
transactionManager.Commit();
}
catch
{
transactionManager.Rollback();
throw;
}


I got an exception with message below:

UPDATE 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.

Also, class which hold previous code snipper supports caching, by using some kind of SqlCacheManager, which is above SqlCacheDependecy class. Database service broker is started by



Code Block
ALTER DATABASE <DB_NAME> SET ENABLE_BROKER


Server is SqlExpress 2005.
Application is in ASP.NET 2.0.




I also noticed next.
On first run, previous code passes without errors. On second run, update error appears.

This is log from sql server.



Code BlockQuery notification delivery could not send message on dialog '{822C7891-736E-DC11-836B-005056C00008}.'. Delivery failed for notification '<qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationhttp://schemas.microsoft.com/SQL/Notifications/QueryNotification">http://schemas.microsoft.com/SQL/Notifications/QueryNotification</A< A>>" id="1" type="change" source="database" info="restart" database_id="13" sid="0xE7C0751C9F7F6C4D9423096BBCC7FB69"><qn:Message>edd3e2dd-11ed-4d92-a0f4-5c674a90aecf;8b2095663cc6a9c297120e4c94d488555e97e54d</qn:Message></qn:QueryNotification>' because of the following error in service broker: 'The conversation handle "822C7891-736E-DC11-836B-005056C00008" is not found.'





Need fast answer.

Thanks in advance.

View 5 Replies View Related

INSERT Failed Because The Following SET Options Have Incorrect Settings: 'ARITHABORT'

Oct 23, 2007

Hi

I am currently running the following query from Query Analyser, I am connected to Server_A and inserting records into
Server_B.Database_B.dbo.MyTable from Server_A .Database_A.dbo.TableRef

Insert into Server_B.Database_B.dbo.MyTable(Field1,Field2,Field3)
Select Field1_Ref,

Field2_Ref,
Field3_Ref
from Server_A .Database_A.dbo.TableRef

However Server_B.Database_B.dbo.MyTable is referenced within an Indexed View and whenever I run this query I get the following error:


Msg 1934, Level 16, State 1, Line 1

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. 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.


I have tried setting ARITHABORT to ON & OFF within the Query and within the database properties but still recieve the same error.

Does anyone have any ideas on why this would not work?

Thanks

View 19 Replies View Related

Incorrect Settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.

Jul 20, 2005

Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'."error after creating a view.We wanted a composite unique constraint that ignored nulls, so we setup a view using the following script:/* --- start --- */BEGIN TRANSACTIONSET QUOTED_IDENTIFIER ONSET ARITHABORT ONSET NUMERIC_ROUNDABORT OFFSET CONCAT_NULL_YIELDS_NULL ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONCOMMITGOCREATE VIEW vw_MyViewWITH SCHEMABINDINGASSELECT Col1, Col2 FROM dbo.MyTable WHERECol2 IS NOT NULLGO/* --- end --- */and then added the constraint to the new view/* --- start --- */CREATE UNIQUE CLUSTERED INDEX AK_MyTable_Constraint1 ONvw_MyView(Col1, Col2)GO/* --- end --- */I thought we were doing fine, 'til we started running some DELETEstored procedures and got the above error. The error also citedARITHABORT as an incorrect setting until we ran this script:/* --- start --- */USE masterDECLARE @value intSELECT @value = value FROM syscurconfigsWHERE config = 1534SET @value = @value | 64EXEC sp_configure 'user options', @valueRECONFIGURE/* --- end --- */TIA to anyone kind enough to shed some light on this for me. Is theresomething we should have done differently in creating the view andindex? If not, what's the procedure for working through thesesettings errors?I've read through some other threads on this subject, but didn'treally find what I was looking for. Thanks again for any help. Wouldbe appreciated.-matt

View 3 Replies View Related

SET Options Have Incorrect Settings: 'ARITHABORT'

Jul 20, 2005

Hi,I am getting the following error when I run a stored procedure inwhich I am inserting/deleting data from a view that selects from aremote table.INSERT failed because the following SET options have incorrectsettings: 'ARITHABORT'The first statement in the stored procedure is 'set arithabort on'. Ialso ran 'set arithabort on' before creating the view.I read many articles ragarding the setting of arithabort. I set it forthe connection, for the database (alter database) and above all forthe server (sp_configure) but could not get around the above error.However, when I had made the setting using "sp_configure 'useroptions', 64" I was able to run the stored procedure successfully acouple of times. But, later when I had resest 'user options' to 0 andthen back to 64 just to reproduce the earlier error and confirm thatthe error does not come with the 'user options' to 64 setting I wasgetting the same error.Is there any way by which I can overcome the problem?Thanks,Iqbal

View 3 Replies View Related

INSERT Failed Because Of Incorrect SET Options

Jul 23, 2005

I am trying to insert a row into a table using a stored procedure and Iget the following error if I try this from QA:INSERT failed because the following SET options have incorrectsettings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.If I try to run this from Microsoft Access, I get a slightly differenterror:INSERT failed because the following SET options have incorrectsettings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.This is what I'm trying to run in QA:declare @P1 intset @P1=NULLexec stpAddNewDistributionMaster 142, 2, 'INTRODUCTION OF FILTERASSEMBLY', 0, 1, @P1 outputselect @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) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[fldDocumentSiteID] [int] NULL ,[fldActive] [bit] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblDistributionMaster] WITH NOCHECK ADDCONSTRAINT [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]GOCREATE INDEX [fldDistributionID] ON[dbo].[tblDistributionMaster]([fldDistributionID]) WITH FILLFACTOR =90 ON [PRIMARY]GOCREATE INDEX [fldDocumentID] ON[dbo].[tblDistributionMaster]([fldDocumentID]) WITH FILLFACTOR = 90 ON[PRIMARY]GOCREATE INDEX [fldDocumentSiteID] ON[dbo].[tblDistributionMaster]([fldDocumentSiteID]) WITH FILLFACTOR =90 ON [PRIMARY]GOCREATE INDEX [fldDocumentType] ON[dbo].[tblDistributionMaster]([fldDocumentType]) WITH FILLFACTOR = 90ON [PRIMARY]GO/****** The index created by the following statement is for internaluse 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 internaluse 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 internaluse 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 internaluse 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 internaluse 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 internaluse 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]) ')GOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE TRIGGER "tblDistributionMaster_UTrig" ONdbo.tblDistributionMaster FOR UPDATE ASSET NOCOUNT ON/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblJobs' */IF UPDATE(fldDistributionID)BEGINIF (SELECT COUNT(*) FROM deleted, tblJobs WHERE(deleted.fldDistributionID = tblJobs.fldDistributionID)) > 0BEGINRAISERROR 44446 'The record can''t be deleted orchanged. Since related records exist in table ''tblJobs'', referentialintegrity rules would be violated.'ROLLBACK TRANSACTIONENDENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE TRIGGER "tblDistributionMaster_DTrig" ONdbo.tblDistributionMaster FOR DELETE ASSET NOCOUNT ON/* * CASCADE DELETES TO 'tblJobs' */DELETE tblJobs FROM deleted, tblJobs WHERE deleted.fldDistributionID =tblJobs.fldDistributionIDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO===========================SPROC:CREATE PROCEDURE stpAddNewDistributionMaster@DocumentID int,@DocumentType int,@Title varchar(255),@SiteID int,@Active bit,@DistributionID int OUTPUTASINSERT INTO tblDistributionMaster(fldDocumentID,fldDocumentType,fldDocumentTitle,fldActive,fldDocumentSiteID)VALUES(@DocumentID,@DocumentType,@Title,@Active,@SiteID)SET @DistributionID = IDENT_CURRENT('tblDistributionMaster')GO==============================Thanks in advanceEdward

View 4 Replies View Related

Update Statistics Failed, Incorrect Set Options

Jul 20, 2005

I have tried many variations (after reviewing other posts) and can notresolve the following issue:RUNNING SQL MAINTENANCE----------------------------SET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET NUMERIC_ROUNDABORT OFFexec master..xp_sqlmaint '-D SBC -UpdOptiStats 10 -RebldIdx 10'--tried UpdOptiStats and RebldIdx separately with same resultsRECEIVE THE FOLLOWING MESSAGE------------------------------[Microsoft SQL-DMO (ODBC SQLState: 42000)]Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options haveincorrect settings: 'QUOTED_IDENTIFIER, ARITHABORTSERVER SETUP-------------------------------Windows 2000, Service Pack 4SQL Server 2000 Standard Edition, Service Pack 3Any help is greatly appreciated.

View 1 Replies View Related

SELECT Failed Because The Following SET Options Have Incorrect Settin

Aug 23, 2006

I am getting the following error message:
[-E-19:42] Message: SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. 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.

I am using the following set options before the sp is created:

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

I looked on the internet and most of solutions were related to indexed views or computed columns. But I am neither using any indexed views nor computed columns. Also the same sp is working fine in one environment but giving the above error in another SQL server. I am using SQL Server 2005 with SP1.

Please help me in finding the cause & the resolution for this issue.

View 9 Replies View Related

Incorrect Settings Arithabort

Dec 3, 2007

INSERT [DELETE] failed because the following SET options have incorrect settings: 'ARITHABORT'. 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.
On local dev machine with SQL Express DB everything works fine. Once moved to shared hosting environment (with adjustments to web.config), insert and delete stored procedures produce the above error.
Made sure that stored procedures SET ARITHABORT ON at the beginning and OFF at the end, without success. Even SET ARITHABORT ON at DB level without success.
Suggestions appreciated
 

View 2 Replies View Related

Transact SQL :: Update Inside Trigger Fails Because Of Incorrect Settings

Aug 11, 2015

I'm updating one column using trigger but i am getting below error .

UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

View 2 Replies View Related

SqlDataSource Insert, Update Delete Options Are Dimmed In Configure DataSource Control

Mar 23, 2008

I can select the tables and fields, but when I click on Advanced the Check Box to Create the Commands is not available. they are dimmed out.
Why is this happening, is it a setting that I have missed. Any help is appreciated.

View 1 Replies View Related

Report Builder Bug?: Semantic Query Execution Failed. Incorrect Syntax Near 'NULLAND'.

Sep 3, 2007

Hi

I get the following error when running a report in report builder using the Adventureworks sample model:
Semantic query execution failed. Incorrect syntax near 'NULLAND'.----------------------------Query execution failed for data set 'dataSet'.----------------------------An error has occurred during report processing.

Here is how to replicate the error:


Create a table report using the Adventure Works model.
Select Product entity, add Product Category and #Products to the table.
Edit formula for #Products to "COUNT(Products)" and add a filter on Products for "Discontinued Date is empty". (I want a count of products that have not been discontinued).
If you run the report now, it willl work as expected.
Add a report filter for "Product.Color is empty" (I only want to see products that don't have a color)
Run the report to get the above error.
While the above is a contrived example, I am getting the same error on a data model that I am developing for a customer.

Am I missing something, or is this a bug in Report Builder?

Thanks

View 2 Replies View Related

SET QUOTED_IDENTIFIER OFF

Jul 20, 2005

Hello!How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT andthen switch it ON straight afterwards. So I can insert strings likeO'Neil etc.It's my only design option at the moment. Everything works fine and asfar as I can see, it's only set to OFF for the currentconnection/session so no other users will run into problems at thesame time if they need it ON, right?Thanks Martin

View 4 Replies View Related

I Don't Understand Quoted_identifier

Mar 24, 2008

OK, so I know that following works if I want update/insert the value "joe's bar" into the table.


set SET QUOTED_IDENTIFIER on
update #temp set id=4 where name ='joe''s bar'


The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)

View 4 Replies View Related

Question On QUOTED_IDENTIFIER

Jul 21, 2006

I have a very strange problem, it only happen to one SQL Server, otherSQL Server seems to be fineI got the following error when trying to run a sp against one of theSQL Server:SELECT failed because the following SET options have incorrectsettings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct foruse with indexed views and/or indexes on computed columns and/or querynotifications and/or xml data type methods.If I put the SET QUOTED_IDENTIFIER OFF In the beginning of the sp,everything works fine, but the questions is why should I do that? andwhy it only happen to only one SQLServer ? The database option onQUOTED_IDENTIFIER are off on all SQLServer. (I am using SQL2005)Thanks in advance.JohnEnclose is the statement that create the databaseif db_id('testdb') is not nulldrop database [testdb]gobeginUSE [master]CREATE DATABASE [testdb] ON PRIMARY( NAME = N'testdb',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb.mdf' ,SIZE = 8192KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'testdb_log',FILENAME = N'C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLDATA estdb_log.ldf' ,SIZE = 29504KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASEXEC dbo.sp_dbcmptlevel @dbname=N'testdb',@new_cmptlevel=90ALTER DATABASE [testdb] SET ANSI_NULL_DEFAULT OFFALTER DATABASE [testdb] SET ANSI_NULLS OFFALTER DATABASE [testdb] SET ANSI_PADDING OFFALTER DATABASE [testdb] SET ANSI_WARNINGS OFFALTER DATABASE [testdb] SET ARITHABORT OFFALTER DATABASE [testdb] SET AUTO_CLOSE OFFALTER DATABASE [testdb] SET AUTO_CREATE_STATISTICS ONALTER DATABASE [testdb] SET AUTO_SHRINK OFFALTER DATABASE [testdb] SET AUTO_UPDATE_STATISTICS ONALTER DATABASE [testdb] SET CURSOR_CLOSE_ON_COMMIT OFFALTER DATABASE [testdb] SET CURSOR_DEFAULT GLOBALALTER DATABASE [testdb] SET CONCAT_NULL_YIELDS_NULL OFFALTER DATABASE [testdb] SET NUMERIC_ROUNDABORT OFFALTER DATABASE [testdb] SET QUOTED_IDENTIFIER OFFALTER DATABASE [testdb] SET RECURSIVE_TRIGGERS OFFALTER DATABASE [testdb] SET ENABLE_BROKERALTER DATABASE [testdb]SETAUTO_UPDATE_STATISTICS_ASYNC ONALTER DATABASE [testdb]SETDATE_CORRELATION_OPTIMIZATION OFFALTER DATABASE [testdb] SET TRUSTWORTHY OFFALTER DATABASE [testdb] SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE [testdb] SET PARAMETERIZATION SIMPLEALTER DATABASE [testdb] SET READ_WRITEALTER DATABASE [testdb] SET RECOVERY FULLALTER DATABASE [testdb] SET MULTI_USERALTER DATABASE [testdb] SET PAGE_VERIFY CHECKSUMALTER DATABASE [testdb] SET DB_CHAINING OFFend

View 4 Replies View Related

QUOTED_IDENTIFIER && ANSI_NULLS

Dec 2, 2007

does anyone know how to keep QA from adding the lines setting thesetwo options on and off along with blank lines at the beginning and endof every object you edit? i have searched quite a bit on this buthaven't been able to come up with anything.

View 1 Replies View Related

SET QUOTED_IDENTIFIER Question

Jul 13, 2007

When I execute a SET QUOTED_IDENTIFIER statement, does it only apply to the current batch or current connection? I noticed it doesn't change the "Quoted Identifiers Enabled" setting in the Options page of the Database Properties in Management Studio.



Thanks,



-Dave

View 5 Replies View Related

Delete Command Failed

Nov 10, 1999

Hi

I am using SQL 6.5. I have a master table which is referenced by more than 35 tables. When I try to
delete a record from the table, i am getting this message

Msg 431, Level 16, State 0
Unable to bind foreign key constraint. Too many tables involved in query.

SQL 6.5 maximum 31 foreign key references. I was wondering is there any way to delete those
records. (i don't want to drop the foreign constraints)

SQL 7.0 supports maximum 63 foreign key references.

Thanks in advance

Moorthy.

View 1 Replies View Related

Find Which SPs Have Quoted_identifier Set Wrong

Jul 31, 2006

Our client's application software requires all stored procedures tohave quoted_identifier set a certain way. I've tripped over this afew times and promptly fixed it, but this morning, I had totroubleshoot a case where someone else tripped over it. In such asituation, how can I identify which SP(s) have it set the wrong way?(No huge rush, the someone-else has already fixed the recent case)SQL 2K, SP3

View 2 Replies View Related

XQuery Requires QUOTED_IDENTIFIER

Dec 13, 2005

I am working for a client that would has XML data being passed into a Service Broker queue.

View 8 Replies View Related

Set QUOTED_IDENTIFIER And ANSI_NULLS On All Objects...

Sep 6, 2006

Is there any way via T-SQL to run through a database and ensure that QUOTED_IDENTIFIER and ANSI_NULLS is set for all stored procs and functions etc on a database without having to recreate every proc / fn ?

The reason this is an issue is I'm trying to take advantage of indexed views to get a performance increase in an application - but not all of the procs/fn's have historically been created with ANSI_NULLS and QUOTED_IDENTIFIER set - so any proc that's not set correctly will fail once the indexed view has been created.

I could I suppose, script out all the procs / functions and do a bit of search and replace to ensure that these are set correctly by recreating all procs and functions - but I'm trying to avoid doing that as I've over 500 databases to upgrade - a metadata hack may be preferable.


Thoughts or comments or possible alternative approaches are welcome...

View 3 Replies View Related

Is It Possible To Audit Failed Insert, Update And Delete Statements?

Oct 25, 2004

Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?

They also want us to track schema changes. Is this possible?

Thanks, Dave

View 5 Replies View Related

SQL CE Error 28562 - Failed To Create The Delete Message To Send To The Server

May 13, 2004

Hi when I am performing a merge replication between my PDA (SQL CE) and SQL I get the above message. There is no information about it anywhere.

Any ideas?

Thanks in advance.

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.

May 27, 2008

This is the error it gives me for my code and then it calls out line 102.  Line 102 is my  buildDD(sql, ddlPernames)  When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one.  Could it not like something in my sql select statement.  thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
 
End Sub

View 2 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Copy And Delete Table With Foreign Key References(...,...) On Delete Cascade?

Oct 23, 2004

Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View 1 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh


View 6 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

NT Settings For SQL 7.0

Feb 24, 2000

View 1 Replies View Related

Awe Settings

May 6, 2002

I am running AWE on SQL 2000 (the server has 8 gig of RAM). I have the SQL Server set to use a max of 6 gig. The server hung unexpectedly (SQL showed no errors or signs). Has anyone had any problems with using AWE, and or any suggestions or changes I might need to do?
Thanks

View 1 Replies View Related

Installation Settings

Jul 26, 2000

Hi,
Can i check installation settings for SQL Server 6.5 ... like what sort order id. has been choosen etc. ? by any query ?

Thanks

View 1 Replies View Related

Collation Settings

Jul 16, 2002

New SQL2000 installation into which I've copied data from multiple SQL7 installations. Some of the databases I've copied over from a SQL7 server with code page 850, sort order/collation 42, using the detach-copy mdf/ldf-attach procedure outlined elsewhere on this site (thank you). Other databases have been DTS'd from another SQL7 server with code page 850, sort order/collation 44.

In the end I want all databases to be in the default Latin1_General_CI_AI collation I've chosen for my installation.

I'm off to immerse myself in collation papers and alter database documentation from BOL, but was hoping someone could enlighten me in a less painful way.

Thanks,

Al

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved