locked
ANSI_NULLS and ANSI_WARNINGS RRS feed

  • Question

  • Hello,

    Our team is trying to get off the ground with VSDB and we have hit a bit of a snag with the deployment scripts.  An SP is being created that uses a LinkedServer and we are getting the error:

    "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

    I have read: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/03cab0c7-9e2b-459a-8ffd-21616ac5c465/ but it does not seem to solve the problem.  The guy basically worked around the issue.

    Now if I set ANSI_WARNINGS in the deployment script, it all works!  I cannot for the life of me find in VSDB how to get this code emitted automatically.  I am hoping someone more knowledgeable than I might be able to help.

    Thanks in advance,

    Gareth.

    Tuesday, April 13, 2010 3:56 PM

Answers

  • Edwer, thank you for your response.  However the error is happening on creation not execution.  I think keeping the SETs inside the SP would only ever get executed on the SPs execution not its creation.  However, this is only my opinion, I am happy to be corrected if this statement is incorrect.

    However, I did manage to solve our developers problem :)

    Somehow (they say they never changed these settings) the tick boxes for ANSI_NULLS and ANSI_WARNINGS in the database deployment settings window had become unset.  Once these were ticked the database deployed without issue.

    So thanks for the help it is appreciated.

    Cheers,

    Gareth.

    • Marked as answer by Gareth CPW Thursday, April 15, 2010 9:41 AM
    • Edited by Gareth CPW Wednesday, September 1, 2010 8:35 AM spelling
    Thursday, April 15, 2010 9:41 AM

All replies

  • Hello,

    You can add the corresponding T-SQL in your stored procedure instead of deployment script.


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, April 14, 2010 9:46 AM
    Moderator
  • Edwer, thank you for your response.  However the error is happening on creation not execution.  I think keeping the SETs inside the SP would only ever get executed on the SPs execution not its creation.  However, this is only my opinion, I am happy to be corrected if this statement is incorrect.

    However, I did manage to solve our developers problem :)

    Somehow (they say they never changed these settings) the tick boxes for ANSI_NULLS and ANSI_WARNINGS in the database deployment settings window had become unset.  Once these were ticked the database deployed without issue.

    So thanks for the help it is appreciated.

    Cheers,

    Gareth.

    • Marked as answer by Gareth CPW Thursday, April 15, 2010 9:41 AM
    • Edited by Gareth CPW Wednesday, September 1, 2010 8:35 AM spelling
    Thursday, April 15, 2010 9:41 AM