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 akothand@indymacbank.com "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 akothand@indymacbank.com "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 derrickleggett@hotmail.com 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!!!!!!!!!!!
