problem with index on computed columns and insert

• jkalin1

  SSC Enthusiast

  Points: 123

  More actions

  April 14, 2003 at 10:03 am

  #79837

  Ok. I have a table with a computed column. There is an index on this table with only the computed column. When doing an insert in query analyzer, it works. However, if I do the same exact insert from ASP.NET, I get INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS.'. In each case, the insert is run from a stored procedure created with the following settings:

  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

  as specified in this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp

  I can supply code that will generate this if anyone needs it.

  Any ideas????

  -- Joe

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  April 16, 2003 at 10:40 am

  #454403

  This is because when QA executes the stored procedure, it sets the ANSI NULLS ON explicitly (see current connection options).

  asp.net SqlConnection class does not do this. So when the stored procedure runs, the connection will have ANSI NULLS OFF by default.