ANSI_NULLS and ANSI_WARNINGS

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.

Answer 1

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.

Answer 2

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.