Tuesday, April 13, 2010 3:56 PM
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,
Wednesday, April 14, 2010 9:46 AMModerator
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.
Thursday, April 15, 2010 9:41 AM
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.