Dropping Indexes

I'm doing some bulk inserting into various tables, and I'm dropping all the
indexes first, and rebuilding them after the data load. One of the tables
has several nonclustered indexes + a clustered index. My question is
simple: does the order I drop the indexes make a difference in the speed
with which they are dropped? My thought is that the clustered index should
be dropped last. Any suggestions?
ThanksFrom the Books Online topic "Dropping Indexes"
Dropping a clustered index can take time because in addition to dropping the
clustered index, all nonclustered indexes on the table must be rebuilt to
replace the clustered index keys with row pointers to the heap. When you
drop all indexes on a table, drop the nonclustered indexes first and the
clustered index last. That way, no indexes have to be rebuilt.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Mike C#" <xyz@.xyz.com> wrote in message
news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> I'm doing some bulk inserting into various tables, and I'm dropping all
> the indexes first, and rebuilding them after the data load. One of the
> tables has several nonclustered indexes + a clustered index. My question
> is simple: does the order I drop the indexes make a difference in the
> speed with which they are dropped? My thought is that the clustered index
> should be dropped last. Any suggestions?
> Thanks
>|||That's what I figured, just wanted to make sure. Thanks Gail.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:Odo3RpbhGHA.4864@.TK2MSFTNGP03.phx.gbl...
> From the Books Online topic "Dropping Indexes"
> Dropping a clustered index can take time because in addition to dropping
> the clustered index, all nonclustered indexes on the table must be rebuilt
> to replace the clustered index keys with row pointers to the heap. When
> you drop all indexes on a table, drop the nonclustered indexes first and
> the clustered index last. That way, no indexes have to be rebuilt.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:OvpqShbhGHA.1276@.TK2MSFTNGP03.phx.gbl...
>sql

Dropdownlist: Database connections problems at EditItemTemplate

Hello,

I'm attempting to generate a dropdownlist by binding it to a database table. My screen has a view of the records plus a footer for inserting new records. I want to populate a drop down list of items from the database on both FooterTemplate and EditItemTemplate. I can successfully create the list on FooterTemplate (the insert new record fields). Here, I have no problems with my database connections.

On page load, if I hit 'Edit', it gives me this error:

There is already an open DataReader associated with this Connection which must be closed first.

I tried closing the datareader and I get an error:Invalid attempt to Read when reader is closed.

Here's the snippet of code:

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load'Put user code to initialize the page here

LoadDataFromDB()

IfNot IsPostBackThen

DataBindGrid()

EndIfEndSubSub LoadDataFromDB()Dim strSQLStmntAsStringDim cmdDefaultsAs SqlCommand' Create new command object passing it our SQL query' and telling it which connection to use.

strSQLStmnt = "SELECT tblTemplates.TemplateID, tblTemplates.Description, " _

& "tblTemplates.Tuition, tblTemplates.Books, tblTemplates.TrainingTable, tblTemplates.Housing, " _

& "tblResidency.ResidencyType, tblCategory.Type, tblCategory.Description AS CategoryDesc, " _

& "tblScholarship.ScholarshipType FROM tblTemplates INNER JOIN tblResidency ON tblTemplates.ResidencyID = " _

& "tblResidency.ResidencyID INNER JOIN tblScholarship ON tblTemplates.ScholarshipID = tblScholarship.ScholarshipID " _

& "INNER JOIN tblCategory ON tblTemplates.CategoryID = tblCategory.CategoryID"

cmdDefaults =

New SqlCommand(strSQLStmnt, conDefaults)' Open the connection, execute the command, and close the connection.

conDefaults.Open()

rdrDefaults = cmdDefaults.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

DefaultsDataGrid.ShowFooter =

TrueEndSubSub DataBindGrid()

DefaultsDataGrid.DataSource = rdrDefaults

DefaultsDataGrid.DataBind()

EndSubFunction BindResidency()As DataSet'Populate the ddlDataSetConst strSQLDDLAsString = _

"SELECT Distinct ResidencyID, ResidencyType FROM tblResidency"

Dim myDataAdapterAs SqlDataAdapter =New _

SqlDataAdapter(strSQLDDL, conDefaults)

myDataAdapter.Fill(ddlDataSet)

Return ddlDataSetEndFunction

And the asp.net:

<asp:TemplateColumn HeaderText="Residency">
<ItemTemplate>
<%# Container.DataItem("ResidencyType") %>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList id="add_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</FooterTemplate>
<EditItemTemplate>
<asp:DropDownList id="edit_residency" runat="server" Width="178px" DataSource="<%# BindResidency() %>" DataTextField="ResidencyType" DataValueField="ResidencyID">
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>

Any help would be appreciated!

Thanks,

Curtis

Sure enough, after posting I figured out what the problem was. Basically, I can easily create another SQLConnection to the SQL Server db and use it to populate all my drop down lists. May not be the most efficient use of database connections, but it works.

Thanks,

Curtis

|||

Without changing some structure of your code, that is how you would have to do it. The BindResidency is being called while the datagrid is looping through your datareader, so the connection is in use at that point. You can get around it, by pre-filling the dataset (Prior to the datagrid databind call) and having the function return it. You may also want to cache the dataset in your function, especially if the list doesn't change often.

sql

Drop The Create Table

I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Either add an ExecSQL task to add the permissions or do the create AND the
GRANT both within the same ExecSQL task.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Darren" <jobs@.supre.au.com> wrote in message
news:%23lUe7vvDFHA.960@.TK2MSFTNGP09.phx.gbl...
I have a DTS package which drops then creates a table before inserting
data from a csv.
My problem is each time I drop the create the table, I must reset the
permissions to the table. How can I automate this?
My current create table code is:
CREATE TABLE [DataFlex].[dbo].[stylemaster] (
[style] varchar (12) NOT NULL,
[retail] numeric (11,2) NULL,
[nzretail] numeric (10,2) NULL,
[descr] varchar (40) NOT NULL,
[colour] int NOT NULL,
[colourway] int NULL,
[season] varchar (10) NULL,
[maingroup] varchar (10) NULL,
[subgroup] varchar (9) NULL,
[story] varchar (9) NULL,
[ac7] varchar (1) NULL,
[fabric] varchar (12) NULL,
[imagename] varchar (30) NULL,
[units] int NULL,
[dollarmargin] numeric (10,2) NULL,
[onsale] char (1) NULL,
[active] varchar (1) NULL,
[fabgroup] varchar (9) NULL
)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql