"Lynn" <L***@discussions.microsoft.com> wrote in message
news:07686BF1-4778-4DC0-9A8A-71E3CE6B382A@microsoft.com...
> Goodmorning everybody.  I have a maint job that runs once weekly - this is
> one of the agent job steps:
>
> EXEC db1..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
> EXEC db2..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
> EXEC db3..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
>
> Of course, I've got one of those in there for each of the db's I wish to
> reindex.  It fails, however, on one of the databases with this error:
>
> Msg 1934, Sev 16: DBCC failed because the following SET options have
> incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
>
> I've read the SET OPTION
> (http://support.microsoft.com/default.aspx?scid=KB;en-us;q301292) doc, and
> it
> seems this is due to the computed columns in one db.  So.  I'm trying to
> fix
> it.  I'm wondering if this is correct ? :
>
> SET ARITHABORT ON
> SET QUOTED_IDENTIFIER ON
> EXEC db1..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
> EXEC db2..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
> EXEC db3..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> @replacechar='*'
> SET ARITHABORT OFF
> SET QUOTED_IDENTIFIER OFF
>
> Or, would I need to set arithabort on and off before and after each EXEC ?
> Like I said, this is a once weekly job - tomorrow is the next run -
> hopefully
> I can get around this before then.
>
> Thank you in advance.
> -- Lynn

"Dan Guzman" wrote:

> > Or, would I need to set arithabort on and off before and after each EXEC ?
>
> Once SET on, the ARITHABORT and QUOTED_IDENTIFIER are on for the duration of
> the connection.  Just set at the beginning of your script and forget.  No
> need to turn these off afterward.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Lynn" <L***@discussions.microsoft.com> wrote in message
> news:07686BF1-4778-4DC0-9A8A-71E3CE6B382A@microsoft.com...
> > Goodmorning everybody.  I have a maint job that runs once weekly - this is
> > one of the agent job steps:
> >
> > EXEC db1..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> > EXEC db2..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> > EXEC db3..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> >
> > Of course, I've got one of those in there for each of the db's I wish to
> > reindex.  It fails, however, on one of the databases with this error:
> >
> > Msg 1934, Sev 16: DBCC failed because the following SET options have
> > incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
> >
> > I've read the SET OPTION
> > (http://support.microsoft.com/default.aspx?scid=KB;en-us;q301292) doc, and
> > it
> > seems this is due to the computed columns in one db.  So.  I'm trying to
> > fix
> > it.  I'm wondering if this is correct ? :
> >
> > SET ARITHABORT ON
> > SET QUOTED_IDENTIFIER ON
> > EXEC db1..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> > EXEC db2..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> > EXEC db3..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')',
> > @replacechar='*'
> > SET ARITHABORT OFF
> > SET QUOTED_IDENTIFIER OFF
> >
> > Or, would I need to set arithabort on and off before and after each EXEC ?
> > Like I said, this is a once weekly job - tomorrow is the next run -
> > hopefully
> > I can get around this before then.
> >
> > Thank you in advance.
> > -- Lynn
>
>
>