15

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?

Improve this question

3 Answers 3

Reset to default
9

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"

Improve this answer
6

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?

Improve this answer
8
  • 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.
    – Ben Brocka
    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
    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.
    – Ben Brocka
    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
    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.
    – Ben Brocka
    Oct 5, 2011 at 0:49
1

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.

Improve this answer

Your Answer

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

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