Indexed View and accessing base tables

[Funka]

(SQL Server 2000 Enterprise)

i have created an indexed view joining two base tables: this view was created with ANSI_NULLS and QUOTED_IDENTIFIER set to on. the view works great, but client applications immediately receive errors on stored procedures which access the base tables. these stored procedures were created with same settings, and contain in the body:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

my goal is to be able to bring the indexed view online, but without breaking or needing to adjust the many client applications accessing the db (all of which is done through two separate stored procs). putting the SET options in these stored procedures themself seems to make no difference. has anyone any experience or suggestions for how to make this work? thanks!

 

[jhall156]

I've read that when running your stored procedure you get the ansi_x settings of the connection. What kind of errors are you getting? JHall

 

[Funka]

the client apps are complaining of incorrect SET options for ARITHABORT and sometimes QUOTED_IDENTIFIER.

and yes, it seems like the SETtings of the connection are indeed being used. so what i need to do is override the client app settings, and provide and use the correct 7 settings needed. is this possible? thanks!

 

[jhall156]

Perhaps you could have a stored procedure (usp_SetAnsi maybe)
like:
CREATE PROCEDURE dbo.usp_SetAnsi AS
SET ANSI_DEFAULTS ON
GO

and then call that first?

worth a shot. JHall