Another DBCC DBREINDEX Question

I am interested in finding out what would happen if I were to call a stored procedure that reindexes a specific table (SQL Server 2000) from .NET code. Specifically, is DBCC DBREINDEX a blocking call (my current assumption is that it in fact is)? I am calling this procedure from a secondary thread, while my primary thread executes other tasks. I think this is a feasible solution, but I just wanted to make sure if my assumption is correct.

Also, I am using a trusted connection, but I am not sure how to treat the SqlConnection object once I call the SPROC; I won't be able to close the connection until the SPROC returns - what would the immediate consequence of keeping a connection open for a long time be (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns). (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns)?

Thanks in advance!

Edit: I forgot to mention, but this whole task is being executed as a scheduled task from the command line - so I also have the option of using osql, but again am not sure about the consequences of calling DBCC DBREINDEX from osql.

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

|||

Madhu K Nair wrote:

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

Maybe I confused you...

The SPROC that I am calling will call DBCC REINDEX: .NET Code > SqlConnection > DBCC REINDEX

What I am asking is what will happen if I call a the stored procedure that contains the T-SQL to reindex the table; will my .NET thread block until this SPROC returns (which could theoretically take hours)? Here is a simple layout of my T-SQL SPROC:

CREATE PROC ReindexArchive

AS

BEGIN

DBCC DBREINDEX('TableName', '', 70)

WITH NO_INFOMSGS

END

GO

So what I am trying to ask is if this sproc gets called from .NET code, will that call be blocked until the table is reindexed and the SPROC returns control to the calling .NET code. Thanks.

|||

i think you need to create multi threads here. otherwise , you will have to wait till the REINDEX Procedure completes to go to next step

Madhu

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.