SQL Server Performance

Error while running DELETE statement

Discussion in 'General DBA Questions' started by akothand, Jul 1, 2004.

  1. akothand New Member

    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
  2. akothand New Member

    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
  3. derrickleggett New Member

    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.
  4. AlvinoPeru New Member

    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!!!!!!!!!!!

Share This Page