Change Default Connection Properties for SQL Server / ColdFusion 7

["JR "Bob" Dobbs" <webforumsuser@xxxxxxxxxxxxxx>]

Change Default Connection Properties for SQL Server / ColdFusion 7


 I've started working with indexed views in my Microsoft SQL Server 2005 
database.  Whenever a SQL statement alters a field ( INSERT, UPDATE, DELETE ) 
used by the index on an indexed view certain connection settings must be set.

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

 When ColdFusion connects to SQL server the ARIHABORT setting is OFF causing 
SQL statements to fail.


 I currently have two workarounds for this:

 1. Use a stored procedure created with the required settings.

 2. Run a cfquery containing the required settings before the UPDATE, INSERT, 
or DELETE cfquery statement.


 Questions:

 1. Is it possible to configure ColdFusion's connection to set ARITHABORT ON by 
default?  

 2. Does Microsoft's JDBC driver use this setting by default?


 My environment:
 Microsoft SQL Server 2005
 ColdFusion 7.0.2
 Windows 2003
 Using the SQL Server drivers bundled with CF7


 References:
 Improving Performance with SQL Server 2005 Indexed Views
 http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

 PRB: Error "INSERT Failed" When You Update Table Referenced in an Indexed View
 http://support.microsoft.com/kb/305333


 Any help is appreciated.