I have site running ASP & SQL 2K. I recently switched my connection from ODBC to OLE DB. An example of the connection string properties is below.
<key name="Provider" value="sqloledb" />
<key name="Data Source" value="servername" />
<key name="Initial Catalog" value="dbname" />
<key name="Integrated Security" value="SSPI" />
I have found that certain procs, those with table variables or temp tables, will cause problems in my ASP recordset because they return warning messages. If I set NOCOUNT ON and ANSI_WARNINGS OFF it works fine but my procs recompile every time. What is the proper way to do this?