[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

SQL Server - "Heterogeneous queries error" - Running sp in Query Analyzer

I have create a stored procedure on SQL server 2000 Server using the code...
SET ANSI_NULLS ON
GO
CREATE PROCEDURE get_XXX.... AS...

However when I execute this in Query Analyzer I get the error "Server: Msg 7405, Level 16, State 1, Procedure get_XXX, Line 71. 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."
When I go to Line 71 it is the first SELECT statement using my Linked Server (another SQL server 2000 Server). So somehow I have to get the ANSI_NULLS /WARNINGS connected to my linked server.

I can't see where I'm going wrong. I put SET ANSI_NULLS ON at the beginning of the sp when i created it and it successfully created it error-free.  I have dropped & recreated the sp many times to no avail.

Please help.
0
RobertEnglish
Asked:
RobertEnglish
1 Solution
 
chapmandewCommented:
Do you still get the error if you include SET ANSI_WARNINGS ON at the beginning of the procedure when you create it?
0
 
BrandonGalderisiCommented:
You have to have the same options set on the connection when you EXECUTE it.  Not just create it.
0
 
RobertEnglishAuthor Commented:
I had orginally put SET ANSI_WARNINGS OFF at the beginning.
But I've just created a new one with SET ANSI_WARNINGS ON at the top and it ran for a while locking up the database.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
TRY adding this line inside your sp

SET XACT_ABORT ON
0
 
BrandonGalderisiCommented:
You don't put them in your procedure.  You put the two set commands BEFORE the create procedure statement in the create script and then BEFORE you call the procedure you have to set the options for the connection.
0
 
RobertEnglishAuthor Commented:
"You put the two set commands BEFORE the create procedure statement in the create script and then BEFORE you call the procedure you have to set the options for the connection."
Just given that a go and I think it may be the answer. I'll do some more rigorous testing and add another comment / accept a solution later
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now