应用程序执行时获取ANSI_NULLS和ANSI_WARNINGS错误的存储proc
我有一个存储过程，它在通过应用程序执行时得到以下错误：
异构查询要求为连接设置ANSI_NULLS和ANSI_WARNINGS选项。这确保了一致的查询语义。启用这些选项，然后重新发出查询。
此存储过程中包含一个SELECT语句，该语句连接到链接的服务器表以返回结果。如果您在SMS中直接执行此存储过程，则没有任何问题。如果我通过ODBC连接通过脚本执行存储过程，那么就没有问题了。生成错误的唯一方法是当应用程序尝试执行错误时。
查看应用程序时，没有错误。事实上，它看起来运行得很成功，只是没有结果。我们能够识别此错误的唯一方法是通过运行SQL跟踪。
跟踪显示，没有一个proc实际上正在运行，相反，它只是立即错误地运行该错误。跟踪还表明它没有以不同的方式执行(例如exec sp_executesql或类似的东西)。我可以看到存储的proc开始执行，然后得到错误，然后停止。
我对此进行了大量的研究，答案似乎很简单，但没有什么效果。
我尝试将SET ANSI_NULLS ON & SET ANSI_WARNINGS ON添加到存储的proc中，甚至在其中的不同位置移动它。但永远也无法让它成功
另外，试着调整数据库级别的设置-
ALTER DATABASE [NGDevl] SET ANSI_NULL_DEFAULT OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_NULLS OFF WITH NO_WAIT
GO
ALTER DATABASE [NGDevl] SET ANSI_WARNINGS OFF WITH NO_WAIT
GO但是没有工作，即使在重新启动SQL实例之后也是如此。
我唯一的想法是让应用程序执行带有设置部分的proc，就像--
SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'但不幸的是，我们无法控制应用程序来尝试这一点。
此外，重新创建存储过程，以便将链接服务器中的所有所需数据从临时表中取出，使其成为本地的，然后加入到其中，但问题相同。
你们有什么想法或者什么可以试试的吗。这个错误看起来应该很简单才能修复，只是不确定我错过了什么。
回答 2
Database Administration用户
发布于 2015-01-06 18:13:07
这个问题是特定于ANSI_WARNINGS的，因为ANSI_NULLS的值是在创建存储过程时使用存储过程的定义存储的。只管跑：
SELECT * FROM sys.sql_modules;要查看ANSI_NULLS和ANSI_WARNINGS之间的区别，请运行以下命令：
-- Check current values (SSMS defaults to ON for both unless changed in
-- Tools -> Options -> Query Execution -> SQL Server -> ANSI
SELECT SESSIONPROPERTY('ANSI_NULLS') AS [AnsiNulls],
SESSIONPROPERTY('ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1 1
SET ANSI_NULLS OFF;
SET ANSI_WARNINGS OFF;
SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 0 0
EXEC('
CREATE PROCEDURE ##SessionSettings
AS
SELECT SESSIONPROPERTY(N''ANSI_NULLS'') AS [AnsiNulls],
SESSIONPROPERTY(N''ANSI_WARNINGS'') AS [AnsiWarnings];
');
EXEC ##SessionSettings;
-- returns: 0 0 (as expected)
SET ANSI_NULLS ON;
SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1 0 (ANSI_NULLS is back ON for the session)
EXEC ##SessionSettings;
-- returns: 0 0 (ANSI_NULLS is still OFF due to being saved with the proc definition)
SET ANSI_WARNINGS ON;
SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1 1 (ANSI_WARNINGS is back ON for the session)
EXEC ##SessionSettings;
-- returns: 0 1 (ANSI_WARNINGS is now ON due to NOT being saved with the proc definition)将SET命令放入存储过程中为时已晚；它需要在proc的执行程序之前设置。所以你在正确的轨道上想要做：
SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'但是，您不能更改应用程序代码及其调用proc的方式。但是，你可以改变程序本身。因此，您可以将当前的proc重命名为sp_storedprocReal，并创建一个名为sp_storedproc的新存储过程，它只是一个包装器，可以执行以下操作：
CREATE PROCEDURE sp_storedproc
(
@Param1 INT,
@Param2 INT,
@Param3 VARCHAR(50),
@Param4 VARCHAR(50)
)
AS
SET ANSI_WARNINGS ON;
EXEC sp_storedprocReal @Param1, @Param2, @Param3, @Param4;在复制所述错误后，我已经测试了上面的包装程序，它确实解决了这个问题。
Database Administration用户
发布于 2014-11-11 23:04:02
造成这种行为的原因是，默认情况下，不同的DAL驱动程序设置不同的连接选项，当应用程序没有正确地初始化自己的连接时，您就无能为力了。
对于您的情况，我能想到的唯一解决方案是将查询包装到动态SQL中，并设置其中所需的所有选项：
create procedure dbo.TestAnsiOnLink
with execute as owner as
declare @sql nvarchar(max);
set @sql = N'set ansi_nulls, ansi_warnings on;
select top 100 *
from [LinkSrv].[LinkDB].dbo.RemoteTable s
inner join [LocalDB].dbo.LocalTable t on t.RecordID = s.RecordID;';
exec (@sql);
return;
go这种方法的主要问题是动态SQL在调用方凭据下执行，因此如果用户没有足够的特权直接查询链接，则必须添加with execute as owner (或其他一些具有足够权限的)子句。有时候可能不太理想，但是.它仍然比存储过程不起作用好得多。
https://dba.stackexchange.com/questions/82416
复制相似问题
腾讯云开发者
