Linkedserver - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS

Last Post 05 Sep 2004 07:43 PM by Math. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Math
New Member
New Member

--
31 Aug 2004 03:35 AM
Hi, All!

I have run into this problem when adding an linked server (sqlserver) and creating an view based on an openquery i.e

CREATE VIEW vwMyView AS
SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM MyTable') MyTable

When querying this view i get, i also tested to create an strored procedure with the same result?

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics

Is there any way i can set ANSI_NULLS on|off within the OPENQUERY ?

Has anyone ben facing this?

Regards, Math
Shafiq
New Member
New Member

--
02 Sep 2004 07:07 AM
Following will do it.

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

GO

CREATE VIEW vwMyView AS
SELECT * FROM OPENQUERY(MyLinkedServer,'SELECT * FROM MyTable') MyTable
GO

Thanks
Shafiq
Shafiq
New Member
New Member

--
03 Sep 2004 08:13 AM
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

GO

CREATE PROC MYPROC
AS
Begin

---- statements
End

By doing the above before creation of MYPROC will not give the error in stored procedure execution. The Set statements will not saved in the stored proc or view but the created view or stored proc has these settings ON.

Thanks
Shafiq
Math
New Member
New Member

--
05 Sep 2004 07:43 PM
Hi and thanx for the quick response!

I managed to create the view using the ansi null / ansi warning properties , i also noticed that i had to set thoose properties when querying the view. as the appplication i use uses the dbLib provider ( php ) i had to execute "Set" statements on the current connection before querying the view, when doing this no error occured..

Thank you all,

Math

You are not authorized to post a reply.

Acceptable Use Policy