ERROR when trying to use ODBC OPENQUERY in stored procedure 1

[jhall156]

I set up a linked server named SBTSODATA using a system dsn that uses the VFP driver to access VFP free tables. The tables show up in the EM. If I go to query analyzer and execute SELECT * FROM OPENQUERY(SBTSODATA,'SELECT * FROM slsgrid') I get a result set. When I create a stored procedure in EM thusly
CREATE PROCEDURE usp_GetSalesGrid AS
SELECT * FROM OPENQUERY(SBTSODATA,'SELECT * FROM slsgrid')

Syntax is okay but when I press OK I get the folg error msg:

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 create another stored procedure that does nothing more than DBBC USEROPTIONS.

when I execute this one from query analyzer I get ANSI_WARNINGS SET but not ANSI_NULLS. However if I just execute DBBC USEROPTIONS FROM within the qa window ANSI_NULLS is SET

I hit the BOL and it tells me that a stored procedure keeps the connection settings that existed when it was created.

If anyone can help out I would really appreciate it.

 

[tlbroadbent]

When using EM to create the stored procedure, explicitly set ANSI_NULL to ON in stored procedure property dialog box. This will set the option on before creating the procedure. Make sure you include the "GO" after the SET statement.

SET ANSI_NULL ON
GO

CREATE PROCEDURE usp_GetSalesGrid AS
SELECT * FROM OPENQUERY(SBTSODATA,'SELECT * FROM slsgrid')

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jhall156]

I feel like a real chowder head. BOL said that setting the ANSI_DEFAULTS or any of those ANSI values would not take effect until after the procedure had finished running. It never even occured to me try and set the value before the create. Many humble thanks.