RobertEnglish
asked on
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.
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.
chapmandew
Do you still get the error if you include SET ANSI_WARNINGS ON at the beginning of the procedure when you create it?
You have to have the same options set on the connection when you EXECUTE it. Not just create it.
ASKER
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.
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.
TRY adding this line inside your sp
SET XACT_ABORT ON
SET XACT_ABORT ON
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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
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