SET options in a view

Question

SET options in a view

webrunner

SSC-Dedicated

Points: 31863
More actions
February 5, 2007 at 1:15 pm

#116108

Hello,
I modified a view in SQL 2000 to include a table from a linked server, and now I am getting this error when the view is queried from a perl script:
Could not execute , Server message number=7405 severity=16 state=1
line=4 server=[server]procedure=[proc]text=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 found out that this error can be handled by using the SET ANSI_NULLS ON and SET ANSI_WARNINGS ON options.
However, I got an error (Error 170) when I tried to include the SET options in the view definition.
Are these options not allowed in views? If not, does someone know a way where I could include them? Or do I need to resort to creating a local temp table where I can store the data on the same db server so the view won't have to use a distributed query? I don't want to set the options at the server level if I can at all avoid it.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

There are two things you are running a Distributed View so you have to follow the restrictions for those Views, then use the correct SET option in your View definition. I don't know why you need a View instead of writing your stored proc directly with OPENQUERY related functions because you still need those in your View definition Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187956.aspx

http://www.sqlteam.com/item.asp?ItemID=1015

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31863 More actions · Answer 2

Hello,

Thanks for your reply. The reason I am using a view is that that is what is being used currently. I can ask to change to using a stored procedure if necessary, but I would prefer to find a way to leave the setup using a view in order to avoid changing too much at once.

Is there a way to issue the SET options in a view definition? If so, do you know the correct syntax?

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

-- Make sure that all of the session settings are set properly

IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

go

The above are the SET options for views but they are not used with partitioned view which is the type you create with linked server using the UNION SET operator. So if you use the above before your create view definition and get error it means you should either move the data to one database and create an indexed view or run plain old stored proc. Hope this helps.

Kind regards,
Gift Peddie

webrunner SSC-Dedicated Points: 31863 More actions · Answer 4

Great, thanks. I think I will use the method of using a stored procedures to bring the data over to the same server and re-create the view without using the linked server.

Thanks again for your help,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html