SQL Server Performance Forum – Threads Archive

Error while running DELETE statement

Hi All,
Am trying to execute a Delete statement through a sQL Job. The job fails with a message :
DELETE Failed because the following SET options have incorrect settings ‘QUOTED_IDENTIFIER’ The problematic statement is :
delete from PS_IM_HR_ACCTG_HST WHERE
DATEDIFF(MONTH,RUN_DT,GETDATE())>=6 I have tried altering this setting to ON and OFF..still the same error.
Please let me know your opinion on this critical issue. Thanks
Anand
Thanks,
Anand.K
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

---

I attached a set statement before the DEL statement in that Job-step. as below : SET quoted_identifer ON
<the complete delete statement> It WORKED.
My confusion is, a similar delete statement (infact the exact conditions,except the Table name) is already present and executing fine in a job. Why is this delete job expecting this Quoted-identifier setting?
Will be great if someone can enlighten me of this logic behind.
thanks
Thanks,
Anand.K
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

---

With the quoted identifier on, look at your data and see if you have single quotes in there anywhere. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Hello FriendI’ve just registered me, ’cause you help, it works for me alsoWell, i applied in the ms_foreachtable statement, something like thishope this will be useful for someone =D++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++DECLARE @VS_Cadena2 VARCHAR(1000)SET @VS_Cadena2 = ‘set QUOTED_IDENTIFIER on DELETE FROM ?’ EXEC sp_MSforeachtable @Command1 = @VS_Cadena2, @whereand = " and o.type = ‘U’ and o.name in (SELECT NAME FROM #TMP_TABLAS_TEMP WHERE ID = 2)" ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++cheers!!!!!!!!!!!

---

]]>