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
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Tuesday, March 27, 2012
Wednesday, March 21, 2012
Error on Index!
Hi, I am having a real headache with a table in my db, one of these days
we found that some basic select queries were taking like 4 minutes, to
execute, and they shouldn’t take more that one second, so we were getting
hundreds of timeouts between our applications, I was assigned to check
what was happening, and I ran a script that i found on the web, that takes
every table of the schema and then reindex it, well the thing was in that
specific table the scrip got an error telling me this:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
Server Driver][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.
I check all the indexes in that table and i found 4, but none of them had
an order by column duplicated, I even restore the db somewhere else and
played with those indexes, but it seems like I cant delete one of them the
first one, my index script is the following:
CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
[yearno], [w
No], [Dayno]) ON [PRIMARY]
GO
CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
[Dayno]) ON [PRIMARY]
GO
I tried to remove them all and then recreate them , but when I try to
delete the clustered one, the error pops again and doesn’t let me delete
it, I also check the whole db script to see if i got something else in
another place, but I didn’t found anything... any suggestions?
How can i remove all the indexes, and / or get rid of this slowing error?
Thanks for your time
PD: The following is the reindex script
DECLARE @.MyTable varchar(255)
DECLARE myCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.MyTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @.MyTable
DBCC DBREINDEX(@.MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @.MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
--Thanks!
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/- Can check the output of sp_helpindex table_name?
- Can you try to recreate the clustered index with the option DROP_EXISTING?
AMB
"Edgardo Sepulveda" wrote:
> Hi, I am having a real headache with a table in my db, one of these days
> we found that some basic select queries were taking like 4 minutes, to
> execute, and they shouldn’t take more that one second, so we were gettin
g
> hundreds of timeouts between our applications, I was assigned to check
> what was happening, and I ran a script that i found on the web, that takes
> every table of the schema and then reindex it, well the thing was in that
> specific table the scrip got an error telling me this:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
> Server Driver][SQL Server]A column has been specified more than once in
> the order by list. Columns in the order by list must be unique.
> I check all the indexes in that table and i found 4, but none of them had
> an order by column duplicated, I even restore the db somewhere else and
> played with those indexes, but it seems like I cant delete one of them the
> first one, my index script is the following:
> CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
> [yearno], [wNo], [Dayno]) ON [PRIMARY]
> GO
> CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [wNo],
> [Dayno]) ON [PRIMARY]
> GO
> I tried to remove them all and then recreate them , but when I try to
> delete the clustered one, the error pops again and doesn’t let me delete
> it, I also check the whole db script to see if i got something else in
> another place, but I didn’t found anything... any suggestions?
> How can i remove all the indexes, and / or get rid of this slowing error?
>
> Thanks for your time
>
>
> PD: The following is the reindex script
>
>
> DECLARE @.MyTable varchar(255)
> DECLARE myCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN myCursor
> FETCH NEXT FROM myCursor INTO @.MyTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindexing Table: ' + @.MyTable
> DBCC DBREINDEX(@.MyTable, '', 90)
> FETCH NEXT FROM myCursor INTO @.MyTable
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> EXEC sp_updatestats
>
> --Thanks!
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||The output of the sp_helpindex was:
aaaaatblaction_PK nonclustered, unique, primary key located on PRIMARY ID
Gradectrl nonclustered located on PRIMARY GradeCtrl
hind_1612584833_58A_83A nonclustered, hypothetical, auto create located on
PRIMARY wNo, yearno
hind_1612584833_83A_58A nonclustered, hypothetical, auto create located on
PRIMARY yearno, wNo
hind_c_1612584833_34A clustered, hypothetical, auto create located on
PRIMARY GradeCtrl
hind_c_1612584833_58A clustered, hypothetical, auto create located on
PRIMARY wNo
hind_c_1612584833_83A clustered, hypothetical, auto create located on
PRIMARY yearno
hind_c_1612584833_83A_58A clustered, hypothetical, auto create located on
PRIMARY yearno, wNo
IX_Accountno clustered located on PRIMARY AccountNo, yearno, wNo, Dayno
IX_tblaction nonclustered located on PRIMARY yearno, wNo, Dayno
Linelink nonclustered located on PRIMARY LineLINK
ticketno nonclustered located on PRIMARY TicketNo
I think this is the problem, too many indexes. or too many combinations.
Thanks for the tip Alejandro, any suggestions Based on this info?
Thanks for your Time.
Edgardo
we found that some basic select queries were taking like 4 minutes, to
execute, and they shouldn’t take more that one second, so we were getting
hundreds of timeouts between our applications, I was assigned to check
what was happening, and I ran a script that i found on the web, that takes
every table of the schema and then reindex it, well the thing was in that
specific table the scrip got an error telling me this:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
Server Driver][SQL Server]A column has been specified more than once in
the order by list. Columns in the order by list must be unique.
I check all the indexes in that table and i found 4, but none of them had
an order by column duplicated, I even restore the db somewhere else and
played with those indexes, but it seems like I cant delete one of them the
first one, my index script is the following:
CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
[yearno], [w
No], [Dayno]) ON [PRIMARY]GO
CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [w
No],[Dayno]) ON [PRIMARY]
GO
I tried to remove them all and then recreate them , but when I try to
delete the clustered one, the error pops again and doesn’t let me delete
it, I also check the whole db script to see if i got something else in
another place, but I didn’t found anything... any suggestions?
How can i remove all the indexes, and / or get rid of this slowing error?
Thanks for your time
PD: The following is the reindex script
DECLARE @.MyTable varchar(255)
DECLARE myCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.MyTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @.MyTable
DBCC DBREINDEX(@.MyTable, '', 90)
FETCH NEXT FROM myCursor INTO @.MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
--Thanks!
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/- Can check the output of sp_helpindex table_name?
- Can you try to recreate the clustered index with the option DROP_EXISTING?
AMB
"Edgardo Sepulveda" wrote:
> Hi, I am having a real headache with a table in my db, one of these days
> we found that some basic select queries were taking like 4 minutes, to
> execute, and they shouldn’t take more that one second, so we were gettin
g
> hundreds of timeouts between our applications, I was assigned to check
> what was happening, and I ran a script that i found on the web, that takes
> every table of the schema and then reindex it, well the thing was in that
> specific table the scrip got an error telling me this:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL
> Server Driver][SQL Server]A column has been specified more than once in
> the order by list. Columns in the order by list must be unique.
> I check all the indexes in that table and i found 4, but none of them had
> an order by column duplicated, I even restore the db somewhere else and
> played with those indexes, but it seems like I cant delete one of them the
> first one, my index script is the following:
> CREATE CLUSTERED INDEX [IX_Accountno] ON [dbo].[tblaction]([AccountNo],
> [yearno], [w
No], [Dayno]) ON [PRIMARY]> GO
> CREATE INDEX [Gradectrl] ON [dbo].[tblaction]([GradeCtrl]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [Linelink] ON [dbo].[tblaction]([LineLINK]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [ticketno] ON [dbo].[tblaction]([TicketNo]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblaction] ON [dbo].[tblaction]([yearno], [w
No],> [Dayno]) ON [PRIMARY]
> GO
> I tried to remove them all and then recreate them , but when I try to
> delete the clustered one, the error pops again and doesn’t let me delete
> it, I also check the whole db script to see if i got something else in
> another place, but I didn’t found anything... any suggestions?
> How can i remove all the indexes, and / or get rid of this slowing error?
>
> Thanks for your time
>
>
> PD: The following is the reindex script
>
>
> DECLARE @.MyTable varchar(255)
> DECLARE myCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN myCursor
> FETCH NEXT FROM myCursor INTO @.MyTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindexing Table: ' + @.MyTable
> DBCC DBREINDEX(@.MyTable, '', 90)
> FETCH NEXT FROM myCursor INTO @.MyTable
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> EXEC sp_updatestats
>
> --Thanks!
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||The output of the sp_helpindex was:
aaaaatblaction_PK nonclustered, unique, primary key located on PRIMARY ID
Gradectrl nonclustered located on PRIMARY GradeCtrl
hind_1612584833_58A_83A nonclustered, hypothetical, auto create located on
PRIMARY w
No, yearnohind_1612584833_83A_58A nonclustered, hypothetical, auto create located on
PRIMARY yearno, w
Nohind_c_1612584833_34A clustered, hypothetical, auto create located on
PRIMARY GradeCtrl
hind_c_1612584833_58A clustered, hypothetical, auto create located on
PRIMARY w
Nohind_c_1612584833_83A clustered, hypothetical, auto create located on
PRIMARY yearno
hind_c_1612584833_83A_58A clustered, hypothetical, auto create located on
PRIMARY yearno, w
NoIX_Accountno clustered located on PRIMARY AccountNo, yearno, w
No, DaynoIX_tblaction nonclustered located on PRIMARY yearno, w
No, DaynoLinelink nonclustered located on PRIMARY LineLINK
ticketno nonclustered located on PRIMARY TicketNo
I think this is the problem, too many indexes. or too many combinations.
Thanks for the tip Alejandro, any suggestions Based on this info?
Thanks for your Time.
Edgardo
Monday, March 19, 2012
Error on Clustered Index and NText Field
Hi i have a problem whenever i try to update a table in which i have a clustered index and a ntext field i have this error through a ASP page
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
ThanksMay be you are trying to update more then one recond in query.
BOL:
If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.
Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page unless updating the column with NULL.
Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.|||i only have this error when i try to update the ntext field and the data is > than 10 KB...
Any idea and how could i use the updatetext in a asp SCRIPT?
Thanks again
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.
ThanksMay be you are trying to update more then one recond in query.
BOL:
If an update query could alter more than one row while updating both the clustering key and one or more text, image, or Unicode columns, the update operation fails and SQL Server returns an error message.
Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page unless updating the column with NULL.
Note The UPDATE statement is logged. If you are replacing or modifying large blocks of text, ntext, or image data, use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and UPDATETEXT statements (by default) are not logged.|||i only have this error when i try to update the ntext field and the data is > than 10 KB...
Any idea and how could i use the updatetext in a asp SCRIPT?
Thanks again
Sunday, March 11, 2012
Error number for duplicate column value during an insert for a unique column ?
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
Open a query analyzer and try to manually insert a value into the table and see what SQL complains..
|||The error number for inserting duplicate value in a column of unique index is : 2601.
Wednesday, March 7, 2012
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
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 tablenameand 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 tablenameName 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 :)
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 :)
Wednesday, February 15, 2012
error message
I am getting an error message that I can't debug.
Processing Errors
An unexpected error occured in Report Processing.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
Now the problem, I am running reporting services off of a stored procedure,
that does not have an index or a parameter named index. Why am I getting
this error and how can I correct the problem?We will need some more information to narrow down why this is occurring.
Does this occur when
* Previewing the report
* Rendering the report on the server (if so which render)
* Exporting to Excel
* Some other place
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:A4EA56BF-2AF0-403F-89D7-F0F636CDA7DF@.microsoft.com...
> I am getting an error message that I can't debug.
> Processing Errors
> An unexpected error occured in Report Processing.
> Index was out of range. Must be non-negative and less than the size of
the
> collection. Parameter name: index
> Now the problem, I am running reporting services off of a stored
procedure,
> that does not have an index or a parameter named index. Why am I getting
> this error and how can I correct the problem?
>|||Try setting a datakeyfield?
"Bruce Johnson [MSFT]" wrote:
> We will need some more information to narrow down why this is occurring.
> Does this occur when
> * Previewing the report
> * Rendering the report on the server (if so which render)
> * Exporting to Excel
> * Some other place
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:A4EA56BF-2AF0-403F-89D7-F0F636CDA7DF@.microsoft.com...
> > I am getting an error message that I can't debug.
> >
> > Processing Errors
> >
> > An unexpected error occured in Report Processing.
> > Index was out of range. Must be non-negative and less than the size of
> the
> > collection. Parameter name: index
> >
> > Now the problem, I am running reporting services off of a stored
> procedure,
> > that does not have an index or a parameter named index. Why am I getting
> > this error and how can I correct the problem?
> >
> >
>
>
Processing Errors
An unexpected error occured in Report Processing.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
Now the problem, I am running reporting services off of a stored procedure,
that does not have an index or a parameter named index. Why am I getting
this error and how can I correct the problem?We will need some more information to narrow down why this is occurring.
Does this occur when
* Previewing the report
* Rendering the report on the server (if so which render)
* Exporting to Excel
* Some other place
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:A4EA56BF-2AF0-403F-89D7-F0F636CDA7DF@.microsoft.com...
> I am getting an error message that I can't debug.
> Processing Errors
> An unexpected error occured in Report Processing.
> Index was out of range. Must be non-negative and less than the size of
the
> collection. Parameter name: index
> Now the problem, I am running reporting services off of a stored
procedure,
> that does not have an index or a parameter named index. Why am I getting
> this error and how can I correct the problem?
>|||Try setting a datakeyfield?
"Bruce Johnson [MSFT]" wrote:
> We will need some more information to narrow down why this is occurring.
> Does this occur when
> * Previewing the report
> * Rendering the report on the server (if so which render)
> * Exporting to Excel
> * Some other place
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:A4EA56BF-2AF0-403F-89D7-F0F636CDA7DF@.microsoft.com...
> > I am getting an error message that I can't debug.
> >
> > Processing Errors
> >
> > An unexpected error occured in Report Processing.
> > Index was out of range. Must be non-negative and less than the size of
> the
> > collection. Parameter name: index
> >
> > Now the problem, I am running reporting services off of a stored
> procedure,
> > that does not have an index or a parameter named index. Why am I getting
> > this error and how can I correct the problem?
> >
> >
>
>
Subscribe to:
Posts (Atom)