Link to home
Start Free TrialLog in
Avatar of johns00
johns00

asked on

ODBC to OLE DB Switch Problems w/ANSI Warnings & NOCOUNT

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?
Avatar of johns00
johns00

ASKER

There's also the very inconvenient matter of adding those SET statements to all my procs.
You can set this in the Default Connection Options under the Connections tab of the SQL Server properties so that you don't have to go to all your procs.
Avatar of johns00

ASKER

That answers the second part but not the first.  Every time I set ANSI_WARNINGS OFF I get recompiles at every execution.
Try this scenario for re-creating the sprocs...

perform these steps in Query Analyzer...
1.  save the existing sproc(s) as a script.
2.  drop the sproc(s)
3.  actually run the two commands "set nocount on" and "set ansi warnings off"
4.  rerun the creation script(s) for the sproc(s)

This routine was given to me by Microsoft Premium Support Services when we converted from SQL 7 to 2000 about a year after 2000 came out.

HTH,
Lynn
Avatar of johns00

ASKER

How is that going to stop the recompiling I experience at every execution of the proc with ANSI_WARNINGS OFF in it?  I just tried and that didn't work.
Avatar of johns00

ASKER

Here's a test proc:

CREATE   PROCEDURE dbo.TESTProc
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

CREATE TABLE #temp
(
      whatever      INT
)

SELECT * FROM tblSomething
GO

Create an ASP page that uses the string properties I posted above.  Try running a trace on that and you will see SP:Recompile every time.
ASKER CERTIFIED SOLUTION
Avatar of JesterToo
JesterToo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of johns00

ASKER

How do I specify the options for NOCOUNT ON and ANSI_WARNINGS OFF in the connection string?  If you can provide specific examples using the info I provided above that would be helpful.
Avatar of johns00

ASKER

I found the AnsiNPW property for the connection string.