Index is being rebuilt

[Zack Sessions]

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.

[Erland Sommarskog]

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

[Zack Sessions]

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".

[Erland Sommarskog]

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