Archive

Posts Tagged ‘ANSI_NULLS’

SQL Error – Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…

May 20, 2011 2 comments

Msg 7405, Level 16, State 1, Line 1
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 came through this strange and new error message while our team was deploying build on one of the servers. After debugging and reviewing the code we find the problem which was in one of the hundreds of stored procedures.

The scenario was:
– A stored procedure that uses Linked Server to pull record-set from another SQL Server database.
– Pull records and load this result set on a temp-table.
– For implementing the business logic and to do some calculations the query uses some aggregate functions, like SUM(), MIN(), MAX() on a few columns in that temp table.

Here if any of these aggregated column contain NULL values, it results in an obvious Warning message (not Error), i.e.: Warning: Null value is eliminated by an aggregate or other SET operation.
We could ignore this warning message, but when used in SSIS packages it causes the package to fail. So to avoid this Warning message, “SET ANSI_WARNINGS OFF” option was set at the beginning of the stored procedure.

The stored procedure compiled fine, but when we executed it, it resulted into this strange error: “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…”.

We observed that this error is due to setting ANSI_WARNINGS to OFF just before the linked server call. So the workaround was to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call. And to be on the safer side applied the “SET ANSI_WARNINGS ON” statement at the end of the stored procedure.

MSDN Forum links:
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1d501b57-fc58-4fbe-9bec-6c38ad158a62
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/03cab0c7-9e2b-459a-8ffd-21616ac5c465/
http://social.msdn.microsoft.com/Forums/en-IE/vstsdb/thread/67d2b3d2-e0fd-4669-a2e9-ca79c894b8d2

MS KB article regarding this issue: http://support.microsoft.com/kb/296769

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING – SQL Server

December 10, 2010 4 comments

You have to create a Stored-Procedure and you need a template. You expand the Object Explorer go to Database -> Programmability -> “Stored Procedure” -> (right click) -> select “New Stored Procedure”.
A new editor opens up with lot of commented & flawless code. There you see some predefined statements.

Sometimes while working with Stored Procedures and other SQL Scripts a those few statements makes you confusing and clueless. Most of the time people remove them or just ignore them, but one should know why are they placed there. The 2 mains SET statements that are placed by default are described below with examples:

–// SET ANSI_NULLS (http://msdn.microsoft.com/en-us/library/ms188048.aspx)
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

Syntax: SET ANSI_NULLS { ON | OFF }

create table #tempTable (sn int, ename varchar(50))

insert into #tempTable
select 1, 'Manoj'
UNION ALL
select 2, 'Pankaj'
UNION ALL
select 3, NULL
UNION ALL
select 4, 'Lokesh'
UNION ALL
select 5, 'Gopal'

SET ANSI_NULLS ON
select * from #tempTable where ename is NULL -- (1 row(s) affected)
sn	ename
3	NULL
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

SET ANSI_NULLS OFF
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
sn	ename
3	NULL
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)
sn	ename
1	Manoj
2	Pankaj
4	Lokesh
5	Gopal
drop table #tempTable

–// SET QUOTED_IDENTIFIER (http://msdn.microsoft.com/en-us/library/ms174393.aspx)
Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.
When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

Syntax: SET QUOTED_IDENTIFIER { ON | OFF }

-- Example 1
SET QUOTED_IDENTIFIER ON
create table "#tempTable" (sn int, ename varchar(50)) -- Command(s) completed successfully.

SET QUOTED_IDENTIFIER OFF
create table "#tempTable" (sn int, ename varchar(50)) -- Incorrect syntax near '#tempTable'.

drop table #tempTable

-- Example 2
SET QUOTED_IDENTIFIER ON
select "'My Name is Manoj'" as Col1, "Let's play" as Col2 -- Invalid column name ''My Name is Manoj''. Invalid column name 'Let's play'.

SET QUOTED_IDENTIFIER OFF
select "'My Name is Manoj'" as Col1, "Let's play" as Col2 -- (1 row(s) affected)
Col1			Col2
'My Name is Manoj'	Let's play
-- Example 3
SET QUOTED_IDENTIFIER ON
select '''My Name is Manoj''' as Col1, 'Let''s play' as Col2 -- (1 row(s) affected)
Col1			Col2
'My Name is Manoj'	Let's play
SET QUOTED_IDENTIFIER OFF
select '''My Name is Manoj''' as Col1, 'Let''s play' as Col2 -- (1 row(s) affected)
Col1			Col2
'My Name is Manoj'	Let's play

–// SET ANSI_PADDING (http://msdn.microsoft.com/en-us/library/ms187403.aspx)
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

Syntax: SET ANSI_PADDING { ON | OFF }

-- Example 1
SET ANSI_PADDING ON
create table #tempTable1 (charcol char(20), varcharcol varchar(20))

insert into #tempTable1
select 'Manoj', 'Manoj'
UNION ALL
select 'Pandey', 'Pandey'

select '['+charcol+']' as charcol, '['+varcharcol+']' as varcharcol from #tempTable1
charcol			varcharcol
[Manoj               ]	[Manoj]
[Pandey              ]	[Pandey]
-- Example 2
SET ANSI_PADDING OFF
create table #tempTable2 (charcol char(20), varcharcol varchar(20))

insert into #tempTable2
select 'Manoj', 'Manoj'
UNION ALL
select 'Pandey', 'Pandey'
select '['+charcol+']' as charcol, '['+varcharcol+']' as varcharcol from #tempTable2
charcol		varcharcol
[Manoj]		[Manoj]
[Pandey]	[Pandey]
drop table #tempTable1
drop table #tempTable2

Other SET statements MSDN BOL: http://msdn.microsoft.com/en-us/library/ms190356.aspx