473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Index is being rebuilt

Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.

I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.

As I see it I have two options. One is to use the ExecuteReader method
instead and simply ignore anything returned. The other is to tell SQL
to not tell me that an index is being rebuilt as I don't care to even
know that. I could trap the general network error and ignore it but I
think it would be dangerous to ignore all network errors.

Any suggestions?

Please respond here as email address is invalid to avoid spam.
Jul 20 '05 #1
3 2532
Zack Sessions (zc********@visionair.com) writes:
Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.
You get that message when you create, change or drop on a clustered index
on a table that also has a non-clustered index. Since the clustered index
serves as a the pointer to the data pages, non-clustered indexes must
be rebuilt when the clustered index is changed.

I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.
I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.


That should not happen. I have a little test app for ADO .Net, and I was
not able to reproduce the problem. Which version of the .Net framework
are you using? Have you set up an InfoMessages event handler?

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Thanks for your reply.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
Zack Sessions (zc********@visionair.com) writes:
Usually when I create an index in the Query Analyzer, no message is
returned other than the command completed. But sometimes I get a
message than the index is being rebuilt. I have come to ignore this
message as it appears to not be an issue that needs research.
You get that message when you create, change or drop on a clustered index
on a table that also has a non-clustered index. Since the clustered index
serves as a the pointer to the data pages, non-clustered indexes must
be rebuilt when the clustered index is changed.


Ahh, mystery cleared up. I was wondering what the answer to "why" was.
I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.
Hmm, if so all the more reason to upgrade, eh? :-)
I am now doing more and more in the VB.NET environment using ADO.NET
to maniupulate SQL objects. (Using SQL7, btw.) When I need to create
an index in VB.NET code I use the ExecuteNonQuery method since I do
not expect to process a result set. However, if the call results in
the index being rebuilt the return message blows up the
ExecuteNonQuery method with a general network error.


That should not happen. I have a little test app for ADO .Net, and I was
not able to reproduce the problem. Which version of the .Net framework
are you using? Have you set up an InfoMessages event handler?


Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
SP4. Workstation and server are at .NET Framework 1.1.

Don't understand InfoMessages event handler. Can't find InfoMessages
mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
how I discovered the exception neing raised was a "general network
error".
Jul 20 '05 #3
Zack Sessions (zc********@visionair.com) writes:
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
I have to admit that I am a little mystified. I tried first to get
the message on SQL 2000 SP3, but it remained silent. I know that I have
seen this message quite a bit, but I have not worked much with SQL 7,
so I suspect this message might have been silenced with SP3 of SQL 2000.


Hmm, if so all the more reason to upgrade, eh? :-)


I would say that upgrading because SQL2000 does not produce a message
that wrecks .Net Framework is the most convincing the reason to upgrade
I've seen. :-)
Workstation is XP with VB.NET using ADO.NET. Server is NT4 SP6a, SQL7
SP4. Workstation and server are at .NET Framework 1.1.
I have WinXP, and .Net Framwork 1.1. I have no idea, whether NT/XP could
make the difference.

By the way, if you just submit a print statement with ExecuteNonQuery,
does that too give General Network Error?
Don't understand InfoMessages event handler. Can't find InfoMessages
mentioned in SQL7 or .NET Books Online. I am using a Try/Catch, that's
how I discovered the exception neing raised was a "general network
error".


It's in the SqlConnection class. And it's InfoMessage.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Thiko | last post by:
Hi I have a corrupt PK index on a table column. It is a unique PK. It needs to be dropped and recreated to cure the corruption. The table is on a backup database which is in replication...
3
by: Zack Sessions | last post by:
Usually when I create an index in the Query Analyzer, no message is returned other than the command completed. But sometimes I get a message than the index is being rebuilt. I have come to ignore...
1
by: Tim | last post by:
Hi all, Here is a brief description of a problem I encountered, and how I found a work around after 3 long days. I have a VB6 app that uses ADO and ODBC to get communicate with SQL server 2000...
5
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
0
by: steve | last post by:
I am running mysql db’s under heavy load. How do I make sure that index is never updated to disk. This is the only way I can keep my machine from running hot. I understand that if there is a...
1
by: Raziq Shekha | last post by:
Hello all, SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it gives me an option to reorganize data and index pages. When I check on it, it populates the line "change free...
10
by: wackyphill | last post by:
After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT...
1
by: Sam Durai | last post by:
A paritioned table ( spead across 7 logical partitions) has around 2 billion rows. I added around 2 million rows newly using db2 load utility but the the table is in index build phase for the past...
4
by: nospam | last post by:
I have been running .NET 2003 for some time now. I have been working on the same C++ project for about a year now. All of a sudden, the first file in the project is being built every time I build...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.