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?
<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?
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.
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
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
I found the AnsiNPW property for the connection string.
ASKER