Post reply

Openquery in a Stored Procedure problem!

  • October 1, 2001 at 9:01 am

    #77867

    Hi

    I have a linked server which i'm trying to issue a openquery to such as:

    SELECT * from OPENQUERY(UNIPLANODBC,'select * from stck where stck_stkno="ASP123-E")

    Now this works fine from query analyser or from ASP. But I put this statement inside a Stored procedure and it says:

    Server: Msg 7405, Level 16, State 1, Line 1

    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.

    I've tried looking up ANDI_NULLS and ANSI_WARNINGS and put set ANDI_NULLS ON or SET ANSI_NULLS OFF in the stored procedure but it still doesnt want to save or run.

    Any clues?

    Cheers

    D. Escott

    email: d-escott@portav.co.uk

  • October 1, 2001 at 12:04 pm

    #418587

    Hi,

    This is from the BOL, 'SET ANSI_NUllS' entry.

    For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

    I don't know if setting has to be set outside the sp, and then the sp dropped an re-created, or the server stopped, etc.. Give it a try.

    Good Luck

    Regards

    Tony Healey

    http://www.SQLCoder.com - Code generation for SQL Server 7/2000


    Regards

    Tony Healey
    www.SQLCoder.com - Free Code generation for SQL Server 7/2000

  • October 2, 2001 at 2:57 am

    #418603

    Hey Tony!

    Nice 1. That did indeed fix the problem - many thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply