Sql-server – a “heterogeneous query”

errorssql serverterminology

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:

UPDATE SRV.DB.DBO.TABLE SET Column=
            (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?

Best Answer

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.

sp_addlinkedserver

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"

Related Question