I got the following error message regarding a SQL query I'm running in a program. SQL Server 2005 T-SQL.

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. (severity 16)

Fixing it is easy, set ANSI_NULLS and ANSI_WARNINGS ON, but I wanted to know what a heterogeneous query is. A Google search brings up dozens of results telling me to set ANSI_NULLS and ANSI_WARNINGS, nothing explaining what the term means. The query is:

            (SELECT Column 
            FROM SRV1.DB.DBO.TABLE)

I'm thinking this is due to connecting to multiple database engines in one query, as I've never gotten this error otherwise.
Does "Heterogeneous" just refer to querying two different database engines in this context?

3 Answers 3


Does "Heterogeneous" just refer to querying two different database engines in this context?

Basically, yes. A "heterogeneous" query is executed on a "heterogeneous linked server". All queries to this linked server will be heterogeneous.

And "heterogenous" is a subset of distributed queries

  • Distributed data stored in multiple instances of SQL Server.
  • Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

But "heterogeneous" pops up every now and then:

See linking servers on MSDN

The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.


A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.

And an MSDN example of querying Active Directory in "Joining Heterogeneous Data"

And MSDN again "Heterogeneous Database Replication"


To the best of my knowledge yes.

A problem that I see with your query is that you have no way to know what row is going to come back from your linked table. Is this just dummy code or is your query written this way?

  • It's dummy code, there's a single column I'm fetching from 40 databases and saving into a "status report" table of just those values.
    – Zelda
    Commented Sep 30, 2011 at 19:18
  • 1
    Oh, ok. Just wanted to make sure you were aware that the value that gets put into that column on Srv.db.dbo.table could be different depending on what version of SQL Server you are running the code on. Specifically from SQL 2005 to SQL 2008 using the code posted you'll get a different value.
    – mrdenny
    Commented Sep 30, 2011 at 19:38
  • Now you've got me interested, what's the potential issue, just that I don't know what single row I'm getting? The table only has a single row in this case which is why there's no WHERE in the subquery.
    – Zelda
    Commented Sep 30, 2011 at 19:45
  • If there were multiple rows in the table, with SQL 2005 you get the first row. With SQL 2008 the new table would get the second row. (I might have that backwards.)
    – mrdenny
    Commented Sep 30, 2011 at 21:53
  • I would have loved an article from MS explaining exactly what a "heterogeneous query" really is but it seems such a thing doesn't exist so I'm marking this correct.
    – Zelda
    Commented Oct 5, 2011 at 0:49

Heterogeneous just means different. So a heterogeneous query is in one database and is querying a DIFFERENT database. Since the databases are different, it brings up the possibility of the settings being different which could cause different results than what was intended.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.