Error 1934 Severity 16 State 1

SQL Server 2000 Standard Sp3. on Windows 2003
What is Error 1934.
I can't find documentaiton on this anywhere. There's only one article in KB
in relation to merge replication. And other stuff on the web about ANSI
settings and indexed views (which I'm not using).
I get these errors in different contexts.
Here's one:
Profiler screenshot
http://www.vinnychi.com/vince/images/sql.jpg
This is a stored procedure that SQL Agent is running from a job. When i run
it this way, I get the 1934 error. If I run it from Query Analyzer I get no
error, and it runs to completion.
SP:
CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
AS
exec UTIL_DEFRAG
RETURN
========
UTIL_DEFRAG is:
CREATE procedure UTIL_DEFRAG
AS
TRUNCATE TABLE BLCSO
TRUNCATE TABLE IBFF_BOL_Temp
TRUNCATE TABLE IBFF_Container_Temp
TRUNCATE TABLE IBFFContainerImportPhase2
TRUNCATE TABLE JobOrderDoor_Temp
TRUNCATE TABLE JobOrderDoorContainer_Temp
TRUNCATE TABLE JobOrderITM_Temp
TRUNCATE TABLE JobOrderITMContainer_Temp
TRUNCATE TABLE JTQ510
TRUNCATE TABLE JTQ520
TRUNCATE TABLE NAIBFF
TRUNCATE TABLE NAIBFF_DeleteTable
TRUNCATE TABLE SpecialRequirementMilestone
declare @.tablename nvarchar(125)
declare cur CURSOR
FORWARD_ONLY
STATIC
READ_ONLY
FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
and name not like 'conflict_%' and name not like 'dt%' and name not like
'MS%' and name not like 'sys%'
order by name
open cur
fetch next from cur
into @.tablename
while @.@.fetch_status = 0
BEGIN
PRINT 'REINDEXING ' + @.tablename
dbcc dbreindex (@.tablename)
fetch next from cur
into @.tablename
END
CLOSE cur
DEALLOCATE curI wouldn't rule out the ANSI settings. This issue has come
up before. SQL Agent and Query Analyzer use different set
options/ansi settings when they connect to SQL Server.
Whatever the set option errors you are seeing as missing,
add to your job script and make sure they are just before
the calling of your stored procedures.
-Sue
On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:

>SQL Server 2000 Standard Sp3. on Windows 2003
>What is Error 1934.
>I can't find documentaiton on this anywhere. There's only one article in K
B
>in relation to merge replication. And other stuff on the web about ANSI
>settings and indexed views (which I'm not using).
>I get these errors in different contexts.
>Here's one:
>Profiler screenshot
>http://www.vinnychi.com/vince/images/sql.jpg
>This is a stored procedure that SQL Agent is running from a job. When i ru
n
>it this way, I get the 1934 error. If I run it from Query Analyzer I get no
>error, and it runs to completion.
>SP:
>CREATE PROCEDURE FFIMPORT_DAILY_CLEANUP
>AS
>exec UTIL_DEFRAG
> RETURN
>
>========
>UTIL_DEFRAG is:
>CREATE procedure UTIL_DEFRAG
>AS
>TRUNCATE TABLE BLCSO
>TRUNCATE TABLE IBFF_BOL_Temp
>TRUNCATE TABLE IBFF_Container_Temp
>TRUNCATE TABLE IBFFContainerImportPhase2
>TRUNCATE TABLE JobOrderDoor_Temp
>TRUNCATE TABLE JobOrderDoorContainer_Temp
>TRUNCATE TABLE JobOrderITM_Temp
>TRUNCATE TABLE JobOrderITMContainer_Temp
>TRUNCATE TABLE JTQ510
>TRUNCATE TABLE JTQ520
>TRUNCATE TABLE NAIBFF
>TRUNCATE TABLE NAIBFF_DeleteTable
>TRUNCATE TABLE SpecialRequirementMilestone
>declare @.tablename nvarchar(125)
>declare cur CURSOR
>FORWARD_ONLY
>STATIC
>READ_ONLY
>FOR SELECT name FROM sysobjects where xtype = N'u' and type = N'u'
>and name not like 'conflict_%' and name not like 'dt%' and name not like
>'MS%' and name not like 'sys%'
>order by name
>open cur
>fetch next from cur
>into @.tablename
>while @.@.fetch_status = 0
>BEGIN
>PRINT 'REINDEXING ' + @.tablename
>dbcc dbreindex (@.tablename)
>fetch next from cur
>into @.tablename
>END
>CLOSE cur
>DEALLOCATE cur
>|||It ended up being
Set ArithAbort ON
needed to be set.
How come this error isn't documented anywhere "official?
I'm also gettinga lot of error 1933 and 1935
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>I wouldn't rule out the ANSI settings. This issue has come
> up before. SQL Agent and Query Analyzer use different set
> options/ansi settings when they connect to SQL Server.
> Whatever the set option errors you are seeing as missing,
> add to your job script and make sure they are just before
> the calling of your stored procedures.
> -Sue
> On Sat, 22 Jan 2005 09:44:42 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>|||Based on your errors, I would guess that you have computed
columns. ANSI settings and computed columns is addressed in
books online under the topics:
SET Options That Affect Results
Creating Indexes on Computed Columns
The Arithabort setting is addressed in the books online
topic Create Index
The article does mention the following:
It is strongly recommended that the ARITHABORT user option
be set server-wide to ON as soon as the first indexed view
or index on a computed column is created in any database on
the server.
You can also find related information in the following:
http://www.microsoft.com/sql/techin...pment/july2.asp
-Sue
On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
<remove@.pacelviATspeakeasy.net> wrote:

>It ended up being
>Set ArithAbort ON
>needed to be set.
>
>How come this error isn't documented anywhere "official?
>I'm also gettinga lot of error 1933 and 1935
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:a76bv0dvssmog6r5j5af4unelg5q7bfn7q@.
4ax.com...
>|||Sue: Thanks for your leads. A large part of the problem was ArithAbort.
Thank you very much.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4c6fv0l327hq7dm20npbajt0vv2nsf10u1@.
4ax.com...
> Based on your errors, I would guess that you have computed
> columns. ANSI settings and computed columns is addressed in
> books online under the topics:
> SET Options That Affect Results
> Creating Indexes on Computed Columns
> The Arithabort setting is addressed in the books online
> topic Create Index
> The article does mention the following:
> It is strongly recommended that the ARITHABORT user option
> be set server-wide to ON as soon as the first indexed view
> or index on a computed column is created in any database on
> the server.
> You can also find related information in the following:
> http://www.microsoft.com/sql/techin...pment/july2.asp
> -Sue
> On Wed, 26 Jan 2005 02:49:32 -0600, "Vince P"
> <remove@.pacelviATspeakeasy.net> wrote:
>
>