Posted by Erland Sommarskog on 07/17/07 09:55

sqlgirl (tootsuite@gmail.com) writes:
> Hi, thanks for the information. Is ARITHABORT the only setting we need
> to be concerned with?
>
> We ran a test. Cleared out the cache entry for our sp by altering it.
> Verified that it was no longer listed in syscacheobjects.
>
> Next, we ran the query from QA, it added an entry to syscacheobjects.
> Then, we ran it from excel. It still creates a second entry in
> syscacheobjects.

And by then you had added SET ARITHABORT ON to the VBA code?

There are indeed other options that matter, but ARITHABORT is typically
the tricky one.

You can look at the column setopts in syscacheobjects, to see if there is
any difference. Unfortunately, Books Online does not specify which bit
that corresponds to which setting. (I assume that since you look in
syscacheobjects, that you are on SQL 2000. In SQL 2005 this information
is available in a more digestable form.)

You can also examine the SET option, by looking at the processes from
Profiler, keep an eye on Current connections, and Audit:Login, as well
as StmtCompleted.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]