Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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?
0
johns00
Asked:
johns00
  • 6
  • 2
1 Solution
 
johns00Author Commented:
There's also the very inconvenient matter of adding those SET statements to all my procs.
0
 
rafranciscoCommented:
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.
0
 
johns00Author Commented:
That answers the second part but not the first.  Every time I set ANSI_WARNINGS OFF I get recompiles at every execution.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JesterTooCommented:
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
0
 
johns00Author Commented:
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.
0
 
johns00Author Commented:
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.
0
 
JesterTooCommented:
My aplogies, John.  I confused this problem with one similar for which the explanation I gave was the fix.  There are two reasons why you're getting recompilations on every invocation of the sproc...

1.  Mixing DDL and DML statements... the last example you showed doesn't actually "interleave" them so that probably isn't the cause in this case.

2.  Issuing any of the "SET" statements to set configuration options to OFF will cause the problem you're seeing.  There are two possible solutions to allow you to remove those statements from your sprocs... (A)  Change the options in SQL Server so that they're the default behavior, and (B) specify the options in your connect string.

Please see this Microsoft KB article for a very complete discussion/example of these issues.

   http://support.microsoft.com/kb/243586/EN-US/

HTH,
Lynn
0
 
johns00Author Commented:
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.
0
 
johns00Author Commented:
I found the AnsiNPW property for the connection string.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now