Dropping / Recreating Indexes

All -
You know when you generate the SQL script for a stored procedure, you get
that initial script at the top that first checks for the procedure in
sysobjects and drops it if it is found?
I want to do the same thing with indexes. I want to create a script that
will first delete a given index if its found and then create a new one.
How would you go about do this?
Thanks,
- Mattif exists (select * from dbo.sysindexes where name = 'IX_index_01' and id =object_id('dbo.tableA'))
drop index dbo.tableA.IX_index_01
GO
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||All -
Sorry about that. I found the DROP INDEX syntax.
Now it looks like the only problem is that if the index is a primary index,
you can't drop it. You have to drop the table, create the table, and then
re-create the index.
Does this sound right?
Thanks,
- Matt
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||Primary Key indexes are created as part of the Primary Key
Constraint. You cannot directly drop the index, but
instead you drop it by dropping the Primary Key
Constraint. So you don't have to drop the table. You must,
however, first drop any Foreign Key Constraints that
reference the PK.
HTH
Vern
>--Original Message--
>All -
>Sorry about that. I found the DROP INDEX syntax.
>Now it looks like the only problem is that if the index
is a primary index,
>you can't drop it. You have to drop the table, create
the table, and then
>re-create the index.
>Does this sound right?
>Thanks,
>- Matt
>
>"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
>news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
>> All -
>> You know when you generate the SQL script for a stored
procedure, you get
>> that initial script at the top that first checks for
the procedure in
>> sysobjects and drops it if it is found?
>> I want to do the same thing with indexes. I want to
create a script that
>> will first delete a given index if its found and then
create a new one.
>> How would you go about do this?
>> Thanks,
>> - Matt
>>
>
>.
>|||Hi Matt
No, you certainly don't have to drop the whole table just to drop an index!
If an index supports a primary key constraint, you have to drop the
constraint, and that will automatically drop the index that supports the
constraint.
You can then readd the constraint.
sp_helpconstraint will show you the constraint names
to drop and re-add the constraint use:
ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT
Full details are in Books Online
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>|||What's the reason behind dropping and recreating the index? If the reason
is to rebuild it (remove fragmentation), then use DBCC DBREINDEX. This will
rebuild your index and you don't need to deal with dropping constraints.
Gail Erickson [MSFT]
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>

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 and create in the same script in management studio

In SQL 2000, we could generate the drop and create statements for an object
in the same script. I have not been able to do this in management studio. Am
I missing something?
No you aren't missing something the tool is<g>. It was somehow left out but
I suspect we will see it in the next service pack. As of now you can only
choose one or the other but not both.
Andrew J. Kelly SQL MVP
"Quinn" <dellsql@.newsgroups.nospam> wrote in message
news:u4l9AgqLGHA.3104@.TK2MSFTNGP11.phx.gbl...
> In SQL 2000, we could generate the drop and create statements for an
> object in the same script. I have not been able to do this in management
> studio. Am I missing something?
>
|||Quinn wrote:
> In SQL 2000, we could generate the drop and create statements for an
> object in the same script. I have not been able to do this in
> management studio. Am I missing something?
Seems to be a bug as the docs indicate you can do a drop, create, or
drop and create.
David Gugick - SQL Server MVP
Quest Software

drill down report using Report sevices help me to get a solution

Hi all

I am new in Sql Server reporting services ..So please help me to clarify my doubt

Please help me to know whether it is possible to generate a drill down report using Report sevices. If it possible please let me know how it can be generated

For Example

Measures

Quarter1 Quarter2 Quarter3#

Profit Profit Profit

====================================================================================

Dimension

Product1 200.50 1500.75 -200.40

Product2 5000.00 4000.00 6000.00

==================================================================================

In the above example Quarter 3 for product 1 shows los in such cases I want to drill down the transaction to get the detailed inormation and to find out the reason how the loss came..

Please help me to get such type of drill down is possible in measures to get the detailed report

With regards

Polachan

In your sample report, I assume these are aggregated values that are summed by some sales? You will want to use a table that holds the sales. That table can be collapsed by the quarters to show the sum of the product for that quarter and the expanded to show the details of the sales.

I know that this reply does not go into detail on how this is done, but it might be a start.

|||

Dear guyinkalamazoo3

Thank u for ur reply to my quries

is it possible to drill down the measure object. ?...

For example in quarer 3 for product1 shows -200.40 loss.

So I want to drill down

Profit

-200.400
--> Customer Product Date Invoice Amt
- -
x Product1 01/10/2007 001 -100
y Product1 01/10/2007 001 -100.40

Can I give this type of drill down is possible in Measure attributes Profit. So I can find in which transaction occure loss to my organisation

Please help me to get a solution for this...

|||

Unfortunately, I hope somebody else might be able to help you with this problem as I am not that familiar with using cubes and OLAP in reporting. This is something that I am just learning myself. I am sure that the technical aspects for drilling down into the actual data in the reports is the same, but I am unfamiliar with data from OLAP.

Sorry I could not be of any further help.

|||

I am just starting on Reporting Services myself, and just finished my first drill down report. What you describe sounds like you should be able to do it.

There is a series of tutorials for Reporting services. The link below is to part of one. This section has 6 lessons that build up to lesson 6 - a drill down report.

http://msdn2.microsoft.com/en-us/library/aa337432.aspx

Hope this helps point you in the right direction.

Shirley

drill down report using Report sevices

Hi all

I am new in Sql Server reporting services ..So please help me to clarify my doubt

Please help me to know whether it is possible to generate a drill down report using Report sevices. If it possible please let me know how it can be generated

For Example

Measures

Quarter1 Quarter2 Quarter3#

Profit Profit Profit

====================================================================================

Dimension

Product1 200.50 1500.75 -200.40

Product2 5000.00 4000.00 6000.00

==================================================================================

In the above example Quarter 3 for product 1 shows los in such cases I want to drill down the transaction to get the detailed inormation and to find out the reason how the loss came..

Please help me to get such type of drill down is possible in measures to get the detailed report

With regards

Polachan

In your sample report, I assume these are aggregated values that are summed by some sales? You will want to use a table that holds the sales. That table can be collapsed by the quarters to show the sum of the product for that quarter and the expanded to show the details of the sales.

I know that this reply does not go into detail on how this is done, but it might be a start.

|||

Dear guyinkalamazoo3

Thank u for ur reply to my quries

is it possible to drill down the measure object. ?...

For example in quarer 3 for product1 shows -200.40 loss.

So I want to drill down

Profit

-200.400
--> Customer Product Date Invoice Amt
- -
x Product1 01/10/2007 001 -100
y Product1 01/10/2007 001 -100.40

Can I give this type of drill down is possible in Measure attributes Profit. So I can find in which transaction occure loss to my organisation

Please help me to get a solution for this...

|||

Unfortunately, I hope somebody else might be able to help you with this problem as I am not that familiar with using cubes and OLAP in reporting. This is something that I am just learning myself. I am sure that the technical aspects for drilling down into the actual data in the reports is the same, but I am unfamiliar with data from OLAP.

Sorry I could not be of any further help.

|||

I am just starting on Reporting Services myself, and just finished my first drill down report. What you describe sounds like you should be able to do it.

There is a series of tutorials for Reporting services. The link below is to part of one. This section has 6 lessons that build up to lesson 6 - a drill down report.

http://msdn2.microsoft.com/en-us/library/aa337432.aspx

Hope this helps point you in the right direction.

Shirley