error rebuild index with dbcc

I have an error like this, every time I reindex this table
via db maintenance plan.
Rebuilding indexes for table 'tbJOTrStatusbyWC'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it means this error ?
How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
thx.You most probably have an index on a computed columns or an indexed view. And unfortunately, the
maint plan doesn't execute the needed SET commands to be able to rebuild these indexes. You would
have to create your own (TSQL) job where you execute needed SET commands and then DBCC DBREINDEX (or
DBCC INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> I have an error like this, every time I reindex this table
> via db maintenance plan.
> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
> because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it means this error ?
> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> thx.|||Tibor is correct in that the maintenance wizard has issues with things such
as computed columns and indexed views. I suggest you create your own
scheduled job to do the DBREINDEX and not use the wizard and all should be
fine.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$PV9Y2eDHA.3788@.tk2msftngp13.phx.gbl...
> > You are wrong,
> I'm pretty certain that I'm not. But you are welcome to think so, if you
wish.
> I still recommend that you check very closely if you have either indexes
on computed columns or
> indexes on views.
> You do not need any special SET options if you have an PK over several
columns, so it has to be
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:3d9d01c37b5e$e471ee80$a601280a@.phx.gbl...
> > You are wrong,
> >
> > I don't use a computed columns, I use multi colums(JONo,
> > OPID,seqNo) for my primay key, this is the structure.
> >
> > TrackMainID int 4 1
> > JONo int 4 0
> > OPID int 4 0
> > SeqNo int 4 0
> > QtyIN numeric 5 1
> > QtyOut numeric 5 1
> > QtyOpen numeric 5 1
> > WCID int 4 1
> > FirstInDate datetime 8 1
> > LastOutDate datetime 8 1
> >
> > I still don't understand the message : incorrect
> > settings: 'QUOTED_IDENTIFIER, ARITHABORT'
> >
> > I must setting where ?
> >
> >
> >
> > >--Original Message--
> > >You most probably have an index on a computed columns or
> > an indexed view. And unfortunately, the
> > >maint plan doesn't execute the needed SET commands to be
> > able to rebuild these indexes. You would
> > >have to create your own (TSQL) job where you execute
> > needed SET commands and then DBCC DBREINDEX (or
> > >DBCC INDEXDEFRAG).
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> > message
> > >news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> > >> I have an error like this, every time I reindex this
> > table
> > >> via db maintenance plan.
> > >>
> > >> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> > >> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> > >> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> > failed
> > >> because the following SET options have incorrect
> > >> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> > >>
> > >> What does it means this error ?
> > >> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> > >>
> > >> thx.
> > >
> > >
> > >.
> > >
>sql

Error opening table in Windows 2003

I'm running SQL Server 2000 SP3 on Windows 2003 Server.
I can create tables, indexes, etc. in SQL Enterprise
Manager. But I'm unable to open a table to view the data
in SQL Enterprise Manager. It give me the following
error message: Provider cannot be found. It may not be
properly installed.
I've uninstalled SQL Server 2000 and re-installed it with
SP3 but still running into the same problem.
Anyone know what's going on or have any idea to correct
this error?
ThanksDid you try using Query Analyzer? How are you connecting to SQL Server
service (windows auth, sql auth)?
<tchen@.ceira.com> wrote in message
news:03a901c399be$c3fb7b20$a601280a@.phx.gbl...
> I'm running SQL Server 2000 SP3 on Windows 2003 Server.
> I can create tables, indexes, etc. in SQL Enterprise
> Manager. But I'm unable to open a table to view the data
> in SQL Enterprise Manager. It give me the following
> error message: Provider cannot be found. It may not be
> properly installed.
> I've uninstalled SQL Server 2000 and re-installed it with
> SP3 but still running into the same problem.
> Anyone know what's going on or have any idea to correct
> this error?
> Thanks

Error messages 1910; cannot create more than 250 indexes in one table

Hi all,
I am having problems to rebuild or create a new index at SQL server 6.5. It
is always give me error message:1910 " cannot create more than 250 indexes i
n one table. While I am only having 5 indexes in this table. I really don't
know what's went wrong sinc
e it was alright for all this years.
I really need help to deal with this problem since the table is the main tra
nsaction in our application system.
Thanks in advance run sp_help tablename
and put the output here so that myself or even our fellow newsgroup friends
can get more idea about the error
also check this link
http://msdn.microsoft.com/library/d...serr_1_733k.asp
Regards,
MD
"Femmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9BC246D8-38BF-40DE-AF28-341F23C8DB2E@.microsoft.com...
> Hi all,
> I am having problems to rebuild or create a new index at SQL server 6.5.
It is always give me error message:1910 " cannot create more than 250
indexes in one table. While I am only having 5 indexes in this table. I
really don't know what's went wrong since it was alright for all this years.
> I really need help to deal with this problem since the table is the main
transaction in our application system.
> Thanks in advance |||Here is the output of sp_help tablename
Name Owner Type
When_created
-- -- --
--- --
--
mc_admission dbo user table
3 Sep 1998 7:06PM
Data_located_on_segment
--
default
Column_name Type Length Prec S
cale Nullable TrimTrailingBlanks
FixedLenNullInSource
-- -- -- -- -
-- -- --
--
ac_no char 10
no yes
no
ur_no char 10
no yes
no
bill_no char 10
no yes
no
bill_date datetime 8
no (n/a)
(n/a)
appr_no char 10
yes yes
yes
adm_type char 2
no yes
no
co_code char 5
no yes
no
mem_code char 5
no yes
no
mem_name varchar 40
yes yes
no
cl_code char 5
no yes
no
adm_doc char 6
no yes
no
ref_fr_doc_code char 6
yes yes
yes
ref_to_doc_code char 6
yes yes
yes
adm_date datetime 8
no (n/a)
(n/a)
dischg_date datetime 8
yes (n/a)
(n/a)
room_no char 5
no yes
no
bill_amt float 8 53 (
null)no (n/a)
(n/a)
bill_gst float 8 53 (
null)no (n/a)
(n/a)
pay_amt float 8 53 (
null)no (n/a)
(n/a)
pay_gst float 8 53 (
null)no (n/a)
(n/a)
oc_code1 char 5
no yes
no
oc_code2 char 5
yes yes
yes
oc_code3 char 5
yes yes
yes
anc_type char 2
yes yes
yes
ipa_stat char 2
yes yes
yes
Identity Seed In
crement
-- --- --
---
No identity column defined. (null) (n
ull)
index_name index_description
index_keys
-- ----
- ----
----
--
----
--
i_admission0 nonclustered located on default
ur_no
i_admission3 nonclustered located on default
adm_date
i_admission2 nonclustered located on default
adm_type
(1 row(s) affected)
No constraints have been defined for this object.
No foreign keys reference this table.|||I'd run consistency checks against the database:
DBCC CHECKDB
DBCC CHECCATALOG
DBCC NEWALLOC
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Femmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9BC246D8-38BF-40DE-AF28-341F23C8DB2E@.microsoft.com...
> Hi all,
> I am having problems to rebuild or create a new index at SQL server 6.5.
It is always give me error message:1910 " cannot create more than 250
indexes in one table. While I am only having 5 indexes in this table. I
really don't know what's went wrong since it was alright for all this years.
> I really need help to deal with this problem since the table is the main
transaction in our application system.
> Thanks in advance

Error messages 1910; cannot create more than 250 indexes in one table

Hi all
I am having problems to rebuild or create a new index at SQL server 6.5. It is always give me error message:1910 " cannot create more than 250 indexes in one table. While I am only having 5 indexes in this table. I really don't know what's went wrong since it was alright for all this years.
I really need help to deal with this problem since the table is the main transaction in our application system
Thanks in advance :)run sp_help tablename
and put the output here so that myself or even our fellow newsgroup friends
can get more idea about the error
also check this link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_733k.asp
Regards,
MD
"Femmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9BC246D8-38BF-40DE-AF28-341F23C8DB2E@.microsoft.com...
> Hi all,
> I am having problems to rebuild or create a new index at SQL server 6.5.
It is always give me error message:1910 " cannot create more than 250
indexes in one table. While I am only having 5 indexes in this table. I
really don't know what's went wrong since it was alright for all this years.
> I really need help to deal with this problem since the table is the main
transaction in our application system.
> Thanks in advance :)|||Here is the output of sp_help tablenam
Name Owner Type When_created
-- -- --- --
mc_admission dbo user table 3 Sep 1998 7:06PM
Data_located_on_segment
--
default
Column_name Type Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource
-- -- -- -- -- -- -- --
ac_no char 10 no yes no
ur_no char 10 no yes no
bill_no char 10 no yes no
bill_date datetime 8 no (n/a) (n/a)
appr_no char 10 yes yes yes
adm_type char 2 no yes no
co_code char 5 no yes no
mem_code char 5 no yes no
mem_name varchar 40 yes yes no
cl_code char 5 no yes no
adm_doc char 6 no yes no
ref_fr_doc_code char 6 yes yes yes
ref_to_doc_code char 6 yes yes yes
adm_date datetime 8 no (n/a) (n/a)
dischg_date datetime 8 yes (n/a) (n/a)
room_no char 5 no yes no
bill_amt float 8 53 (null)no (n/a) (n/a)
bill_gst float 8 53 (null)no (n/a) (n/a)
pay_amt float 8 53 (null)no (n/a) (n/a)
pay_gst float 8 53 (null)no (n/a) (n/a)
oc_code1 char 5 no yes no
oc_code2 char 5 yes yes yes
oc_code3 char 5 yes yes yes
anc_type char 2 yes yes yes
ipa_stat char 2 yes yes yes
Identity Seed Increment
-- --- ---
No identity column defined. (null) (null)
index_name index_description index_keys
-- --- -----------
i_admission0 nonclustered located on default ur_no
i_admission3 nonclustered located on default adm_date
i_admission2 nonclustered located on default adm_type
(1 row(s) affected)
No constraints have been defined for this object.
No foreign keys reference this table.|||I'd run consistency checks against the database:
DBCC CHECKDB
DBCC CHECCATALOG
DBCC NEWALLOC
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Femmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9BC246D8-38BF-40DE-AF28-341F23C8DB2E@.microsoft.com...
> Hi all,
> I am having problems to rebuild or create a new index at SQL server 6.5.
It is always give me error message:1910 " cannot create more than 250
indexes in one table. While I am only having 5 indexes in this table. I
really don't know what's went wrong since it was alright for all this years.
> I really need help to deal with this problem since the table is the main
transaction in our application system.
> Thanks in advance :)