0

If anyone can assist with this issue, I’d be most grateful. I’ve been struggling with it for too long now, and can’t seem to resolve.

  • I have an SSDT project (VS2012, SQL2008) with stored procedures that reference a linked server.

  • The project builds successfully

  • When attempting to deploy, with either (localdb) or “regular” SQL Server, using either F5 or Schema Compare, deployment fails because the linked server isn’t defined in the target

  • After defining the linked server, the project still fails to deploy any stored procedure that references the linked server with:

    Error 200 SQL72014: .Net SqlClient Data Provider: Msg 7405, Level 16, State 1, Procedure usp_MemEligGetFullData, Line 157 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.

Adding SET ANSI_NULL ON and SET ANSI_WARNINGS ON to these stored procedures doesn’t help, nor does selecting the corresponding checkboxes in the database settings of the project properties.

This is a blocking issue for an entire team in an enterprise environment, so again, if anyone has any ideas about why this is happening, I would most appreciate it!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Lenni Lobel
  • 21
  • 1
  • 6

2 Answers2

4
  1. In Visual Studio,
  2. right click the stored procedure
  3. Click on properties
  4. Change ANSI Nulls to 'On'

Visual Studio

Shrage Smilowitz
  • 24,494
  • 2
  • 28
  • 32
  • When I change this to "ON", I see the warning message "Property Value is not valid" . Any thoughts on this ? – VPP Jan 20 '20 at 16:49
  • Did you select it from the list? or you typed it in manually? If you typed it in manually try to change it to On rather than ON – Shrage Smilowitz Apr 08 '21 at 14:36
0

Try creating the stored procedure through SSMS, but executing SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in a batch immediately preceding the create procedure statement, like so:

SET ANSI_NULLS ON 
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE usp_MemEligGetFullData AS /* stuff */

If that's successful, then check whether the script generated by SSDT also contains those set statements before the create procedure statement. If it doesn't, then ensure that you have the Script Database Options setting enabled. If it still doesn't, then in the worst case you could author a deployment contributor to modify the TSQL that gets generated.

Steven Green
  • 3,387
  • 14
  • 17