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.
RobertEnglishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.