Openquery in a Stored Procedure problem!

• Rav

  Valued Member

  Points: 62

  More actions

  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

• THealey

  SSC Rookie

  Points: 39

  More actions

  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

• Rav

  Valued Member

  Points: 62

  More actions

  October 2, 2001 at 2:57 am

  #418603

  Hey Tony!

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