Post reply

Linked Server Issue

  • June 24, 2002 at 1:08 pm

    #101173

    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

    #431510

    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

    steve@dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • June 24, 2002 at 2:36 pm

    #431522

    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

    #449662

    Matt,

    Did you ever resolve this issue? I am having the same problem.

    Dan

  • February 25, 2003 at 2:25 am

    #449675

    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

    #449681

    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