Heterogeneous queries - Microsoft SQL Server

Adnan

Hi Guys,

I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following error:-

"Heterogene ous 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 dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.

Can anyone please let me know, how to fix this problem.

Thanks
Adnan

Jul 23 '05 #1

Subscribe Reply

8088

Erland Sommarskog

Adnan (ad**********@y ahoo.ca) writes:

I have one stored procedure on SQL 6.5 and retrieving data from SQL
2000. I have defined a linked server on SQL 6.5. But
I am getting following error:-

"Heterogene ous 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 dropped and re-create the stored procedure after adding the SET
ANSI_NULLS & SET ANSI_WARNINGS ON in stored procedure but still getting
this error. I have created this sp on ISQL/W not in Enterprise Manager.

Can anyone please let me know, how to fix this problem.

The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.

ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSA CTIONS OFF
SET CURSOR_CLOSE_ON _COMMIT OFF

then you get the same settings as in the SQL 2000 tools.

Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #2

Erland Sommarskog

Erland Sommarskog (es****@sommars kog.se) writes:

The setting of ANSI_NULLS is saved with the procedure, why it does
not help setting ANSI_NULLS within the procedure.
A clarification: the setting of ANSI_NULLS that applies for a stored
procedure, is the setting that was in effect when you created the procedure.
The rest below applies as before:

ANSI_NULLS is on by default with most interfaces - but not if you use
DB-Library, and the 6.5 tools uses DB-Library. If you insist on using
6.5 tools, be sure to always include this:

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSA CTIONS OFF
SET CURSOR_CLOSE_ON _COMMIT OFF

then you get the same settings as in the SQL 2000 tools.

Certainly far more easier is to use Query Analyzer. (You mentioned
Enterprise Manager. If you mean the 6.5 tool, it has the same issue
as ISQL/W. It you mean EM 2000, this is a poor tool for maintaining
stored procedures.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #3

Similar topics

2177

dbWidget 1.0 - New Heterogeneous Database Development Tool

by: Shawn Spiars | last post by:

dbWidget is a heterogeneous database development tool. It works with most relational databases, including Oracle, SQL Server, MySQL, and Sybase. dbWidget comes with a free, fully functional 30-day trial and is available for download at http://www.yazoosoft.com

Oracle Database

6809

Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

by: Andreas Lauffer | last post by:

I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:

Microsoft SQL Server

1354

Validating heterogeneous XML

by: kj | last post by:

The main rationale I have seen for namespace support in XML is to enable the peaceful coexistence of XML fragments from various sources within the same ("heterogeneous") XML document without their trampling over each other's namespaces. What about the validation of such "heterogeneous" XML documents? Not only are various fragments probably governed by different syntaxes, but these syntaxes are probably specified using different...

.NET Framework

2440

Heterogeneous Container: avoid ifs and casts?

by: Markus Dehmann | last post by:

I defined a base class in order to put heterogeneous values into a standard container: All values that I store in the container are derived from my base class. Now when I iterate over the elements of the container and want my original values back - can I avoid excessive casting and if statements? (see my sample code below.) Can I make it more elegant? #include <iostream> #include <vector>

C / C++

2329

How to run queries on a subset of records in a table?

by: Roger Green | last post by:

I have inherited a complex database that has many dozens of queries that derive data from a people table. I now need to be able to run these queries (from within a significant number of forms) not on the full dataset, but on a subset of the data in the people table. I want to avoid having to put criteria in all of the individual queries or forms, or having to change the data source for all the queries. Is there anyway I can restrict...

Microsoft Access / VBA

2345

Which are better queries in code or stored queries?

by: NeilAnderson | last post by:

I'm a fairly new user of access & I've never had any training, so I'm wondering if I'm doing the right thing here, or if it matter at all. I'm building a database for room booking purposes and I'm trying to do all record changing using queries. From what I can glean from this (excellent!) group this is the right way to go about it. At present what I'm doing is building & trouble-shooting the queries with query-builder then using code to...

Microsoft Access / VBA

4059

Can't see all of the queries when merging to MS Word and can't get to reports

by: Jerry Hull | last post by:

I'm working with a database developed by an untrained person over several years - and on a network that has recently been upgraded with a new server installed and MS office upgraded from 2K (I think - it might have been XP) to 2003. The database is impressive, both in what it does and the obtuse and inconsistent ways it works. There are several hundred queries, for example, with no indication of where they are used or if they are in fact...

Microsoft Access / VBA

4636

Creating Queries in DAO vs ADOX

by: Greg Strong | last post by:

Hello All, Is it better to create a query in DAO where a report has 4 sub-reports each of whose record source is a query created at runtime and everything is in 1 MDB file? From what I've read and experienced it appears DAO is the way to go in this situation, so when is it good to use ADOX to create queries? Why do I ask the question? I've created a MDB file which uses DAO, but

Microsoft Access / VBA

4637

Heterogeneous containers with CRTP

by: Arash Partow | last post by:

Hi all, I've got a question related to emulating aspects of polymorphism with CRTP. Below is a typical polymorphic class hierarchy with a definition of a "somewhat" heterogeneous container of objects. class poly_base { public: virtual int foo(int i, int j) = 0; };

C / C++

10521

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10324

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

12117

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

11590

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

11969

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

11121

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8682

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5387

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

4925

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP