Strange error from xp_sqlmaint!! 
Author Message
 Strange error from xp_sqlmaint!!

I have a server running SQL 2000.  There are a number of databases on this
server. When using xp_sqlmaint to check the databases one by one they all
complete correctly except for one database that gives the following error

[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
ARITHABORT'.

If I do a DBCC CHECKDB() for the database it works perfectly.

I have search BOL, groups.google.com and www.microsoft.com/sql but can't
find any expanation or answer.  Does anyone have any ideas?

Tim



Sat, 15 Nov 2003 12:34:55 GMT
 Strange error from xp_sqlmaint!!

Tim,

Sounds like xp_sqlmaint doesn't handle if you set one of those database options to not
default. You set database options using sp_dboption (old way) or ALTER DATABASE. It
should be easy to try to re-create on a test database trying to set one of those
database options (of course checking which one is on for your problematic database).

If this is the case, we'd probably need to post a bug report, and MS might say that it
is by design (as the error message has anticipated this case). But first thing would
be to validate my "theory", as above.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:
> I have a server running SQL 2000.  There are a number of databases on this
> server. When using xp_sqlmaint to check the databases one by one they all
> complete correctly except for one database that gives the following error

> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
> ARITHABORT'.

> If I do a DBCC CHECKDB() for the database it works perfectly.

> I have search BOL, groups.google.com and www.microsoft.com/sql but can't
> find any expanation or answer.  Does anyone have any ideas?

> Tim



Sat, 15 Nov 2003 14:22:58 GMT
 Strange error from xp_sqlmaint!!
Tibor,

Thanks for the reply.  Below are some more observations of the problem.

1) QUOTED_IDENTIFIERS and ARITHABORT are not set as database options (this
is the same for all the databases)

2) From Query Analyser - If I set QUOTED_IDENTIFIERS or ARITHABORT OFF then
the DBCC CHECKDB () fails for this one database but still works for all the
others.

3) If I use ALTER DATABASE and set QUOTED_IDENTIFIERS & ARITHABORT ON then
DBCC CHECKDB generates the following error

Msg 1934, Level 16, State 1, Server SAKL63, Line 1
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.

4) Inside this database there are some replication tables (dbo.MS_merge*
plus some others).  I was learning about replication recently (probably
playing would be better).  I may not have disabled replication correctly.

Having said all the above I think the best option is to recreate this
database and see if the problem goes away.  I would like to work out if the
problem is reproduceable - but I don't know how easy that would be.

Would there be any value in passing the problem database on to MS in case it
is a bug?

Tim


Quote:
> Tim,

> Sounds like xp_sqlmaint doesn't handle if you set one of those database
options to not
> default. You set database options using sp_dboption (old way) or ALTER
DATABASE. It
> should be easy to try to re-create on a test database trying to set one of
those
> database options (of course checking which one is on for your problematic
database).

> If this is the case, we'd probably need to post a bug report, and MS might
say that it
> is by design (as the error message has anticipated this case). But first
thing would
> be to validate my "theory", as above.

> --
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com



> > I have a server running SQL 2000.  There are a number of databases on
this
> > server. When using xp_sqlmaint to check the databases one by one they
all
> > complete correctly except for one database that gives the following
error

> > [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> > following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
> > ARITHABORT'.

> > If I do a DBCC CHECKDB() for the database it works perfectly.

> > I have search BOL, groups.google.com and www.microsoft.com/sql but can't
> > find any expanation or answer.  Does anyone have any ideas?

> > Tim



Sun, 16 Nov 2003 05:02:00 GMT
 Strange error from xp_sqlmaint!!
Strange. There's something in DBCC CHECKDB that doesn't like those settings. But I
can't, with the info at hand, say exactly where the problem is.

If you can re-create the db and the problem goes away, that I'd do that. MS will ask
for a repro anyhow...

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:
> Tibor,

> Thanks for the reply.  Below are some more observations of the problem.

> 1) QUOTED_IDENTIFIERS and ARITHABORT are not set as database options (this
> is the same for all the databases)

> 2) From Query Analyser - If I set QUOTED_IDENTIFIERS or ARITHABORT OFF then
> the DBCC CHECKDB () fails for this one database but still works for all the
> others.

> 3) If I use ALTER DATABASE and set QUOTED_IDENTIFIERS & ARITHABORT ON then
> DBCC CHECKDB generates the following error

> Msg 1934, Level 16, State 1, Server SAKL63, Line 1
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.

> 4) Inside this database there are some replication tables (dbo.MS_merge*
> plus some others).  I was learning about replication recently (probably
> playing would be better).  I may not have disabled replication correctly.

> Having said all the above I think the best option is to recreate this
> database and see if the problem goes away.  I would like to work out if the
> problem is reproduceable - but I don't know how easy that would be.

> Would there be any value in passing the problem database on to MS in case it
> is a bug?

> Tim


> > Tim,

> > Sounds like xp_sqlmaint doesn't handle if you set one of those database
> options to not
> > default. You set database options using sp_dboption (old way) or ALTER
> DATABASE. It
> > should be easy to try to re-create on a test database trying to set one of
> those
> > database options (of course checking which one is on for your problematic
> database).

> > If this is the case, we'd probably need to post a bug report, and MS might
> say that it
> > is by design (as the error message has anticipated this case). But first
> thing would
> > be to validate my "theory", as above.

> > --
> > Tibor Karaszi, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com



> > > I have a server running SQL 2000.  There are a number of databases on
> this
> > > server. When using xp_sqlmaint to check the databases one by one they
> all
> > > complete correctly except for one database that gives the following
> error

> > > [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> > > following SET options have incorrect settings: 'QUOTED_IDENTIFIER,
> > > ARITHABORT'.

> > > If I do a DBCC CHECKDB() for the database it works perfectly.

> > > I have search BOL, groups.google.com and www.microsoft.com/sql but can't
> > > find any expanation or answer.  Does anyone have any ideas?

> > > Tim



Tue, 18 Nov 2003 21:11:20 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Strange WebForms error, strange Oracle answer - baffled!

2. "Syntax Error in expression" strange error

3. Strange Error Log Error

4. Strange Oracle error: ORA-06502 (and Microsoft run-time error -2147467259(80004005))

5. Strange Error in Error Log......

6. Strange Oracle error: ORA-06502 (and Microsoft run-time error -2147467259(80004005))

7. error 12541 - strange error on TNS: no listener

8. Strange Error Log Error

9. Strange ERROR - Error loading file...

10. Unexpected Server Restarts during xp_sqlmaint Backup

11. xp_sqlmaint on sql7

12. DBREINDEX - XP_SQLMAINT - INDEXDEFRAG


 
Powered by phpBB® Forum Software