Hello people,
Good night!
In today's post I will talk about a not very common error that occurs in SQL Server when trying to execute queries using Linked Server or cross-server statements (Ex: OPENROWSET, OPENQUERY, etc.) and SQL Server returns the following message:
Msg 7405, Level 16, State 1, Line 45
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This is kept consistent query semantics. Enable these options and then reissue your query.
The first time I saw this error was when my colleague Henrique Mauri, the guy who's been giving me more blog ideas lately, tried create a sequence and use it in a scalar function which was called from a stored procedure within an application written in C # and came across this error message.
This problem occurs especially when you manually disable the ANSI_NULLS and ANSI_WARNINGS statements before using a linked server in SQL Server. Let's understand why this happens and how to solve it.
Why use ANSI_NULLS and ANSI_WARNINGS?
First of all, I need to make it clear that the issue reported in this post only occurs when at least one of these two settings is OFF. I already talked about both in my post SQL Server SET Commands. The default of SQL Server is that they are ON, but many people disable it to avoid alerts when erroneously using some queries in the database.
Usage examples:
I strongly recommend that you do NOT disable ANSI_NULLS and ANSI_WARNINGS in your routines. In fact, I don't even remember when I last needed to change the default behavior of a query using SET commands (other than using SET LANGUAGE and SET DATEFORMAT).
According to Microsoft's own website, in a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work and plan to modify applications that currently use it.
Simulating the problem
To simulate this problem, I will provide below a script that will show exactly this error message. Remember to change the name of LinkedServer to the name of your SQL Server instance.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
---------------------------------- -- CRIAÇÃO DO LINKED SERVER ---------------------------------- USE [master] GO DECLARE @instancia NVARCHAR(200) = N'127.0.0.1\SQL2014' IF ((SELECT COUNT(*) FROM sys.servers WHERE name = @instancia) > 0) EXEC master.dbo.sp_dropserver @server=@instancia, @droplogins='droplogins' EXEC master.dbo.sp_addlinkedserver @server = @instancia, @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@instancia,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL ---------------------------------- -- CRIAÇÃO DA TABELA DE TESTES ---------------------------------- IF (OBJECT_ID('Testes.dbo.Teste') IS NOT NULL) DROP TABLE Testes.dbo.Teste SELECT 12456.74 AS Valor, 'Teste 1' AS Tipo INTO Testes.dbo.Teste UNION SELECT 1314.00, 'Teste 2' UNION SELECT 745.99, 'Teste 2' UNION SELECT 1587.90, 'Teste 1' UNION SELECT 100, NULL UNION SELECT NULL, NULL ---------------------------------- -- SIMULAÇÃO DO ERRO ---------------------------------- SET ANSI_NULLS OFF GO SELECT SUM(Valor), Tipo FROM [127.0.0.1\SQL2014].Testes.dbo.Teste GROUP BY Tipo |
Solving the problem
Guys, the solution to this problem is extremely simple and it is already clear from the error message and everything I already said in the post. Just enable the SET ANSI_WARNINGS and SET ANSI_NULLS parameters.
If you have already done this and are still receiving this error message, look for all of your source code, because that's the problem. Make sure you do not have any SP running and that you are disabling one of the two options or some ad hoc code in your application or database access layer. Also check the create command of the Stored Procedures involved, as it may be that in the CREATE / ALTER PROCEDURE command these options have been disabled.
I hope you enjoyed the post.
To the next!
Very good this tip