June 24, 2002 at 1:08 pm
I created a stored procedure for my linked server but I am getting this error message.
The error message is:
Hetrogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistant query sematics. Enable these options and then reissue your query.
My Stored procedure:
CREATE PROCEDURE testdata AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET ROWCOUNT 20
SELECT * FROM OPENQUERY(UNIDATA,'SELECT CM.ID,CM.NAME1 FROM CONTRACTOR_MASTER CM INNER JOIN CONTRACTOR_DRIVER_PROFILE CDP ON CM.ID = CDP.CONTRACTOR_ID WHERE NAME1 LIKE "JEFF%" AND CANCEL_DATE1 = "" ORDER BY CM.ID')
Any ideas of how to remedy the problem?
Matt
June 24, 2002 at 1:49 pm
I think these must be set when the link is created. Can you edit the link (sorry not that familiar and too busy to dig through it).
Steve Jones
June 24, 2002 at 2:36 pm
I understand what you mean under linked servers but there is nothing under linked server for ANSI_NULLS & WARNINGS.
I did set the connection thing in SQL Server properties and for the db but it does not seem to make a difference.
Matt
February 24, 2003 at 5:24 pm
Matt,
Did you ever resolve this issue? I am having the same problem.
Dan
February 25, 2003 at 2:25 am
You have to set ANSI_NULLS and ANSI_WARNINGS for connections not once you've connected. Try right click on server name in EM and select properties. Click on Connections tab and set ANSI_NULLS and ANSI_WARNINGS properties there.
Note these options apply to all connections made to that server, linked or otherwise.
Far away is close at hand in the images of elsewhere.
Anon.
February 25, 2003 at 5:03 am
Dave, Thanks for the reply... I got it working through your suggestion along with looking at the Knowledge Base Bug Report 296769.
Thanks for the help,
Dan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply