Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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"

share|improve this answer
add comment

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?

share|improve this answer
    
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 '11 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 '11 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 '11 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 '11 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 '11 at 0:49
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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