Deleting Indexes from a table

I am using SQL-DMO to loop through the Index collection and executing the
Remove method to delete indexses in a table. I am getting an error message
that says I can't delete an Index because it was created using 'PRIMARY KEY'
.
I need to be able to remove ALL Indexes from a table. Can I do this with
SQL-DMO?
Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
adv-thanks-anceJD,
You can't remove an index associated with a PRIMARY KEY constraint without
removing the PK constraint itself. So I think you'll either have to drop
the constraint and recreated it or if you just want to rebuild the index use
DBCC DBREINDEX instead.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error
> message
> that says I can't delete an Index because it was created using 'PRIMARY
> KEY'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||You will have to remove first all foreign key constraints, then all primary
key and unique constraints and then you will be able to drop the rest of the
indexes.
AMB
"JD" wrote:

> I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error messag
e
> that says I can't delete an Index because it was created using 'PRIMARY KE
Y'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||Thanks,
Can you remove the PK constraint using SQL-DMO? Or will I have to write
a procedure that loops through all of the tables and remove the PK Constrain
t?
"Jerry Spivey" wrote:

> JD,
> You can't remove an index associated with a PRIMARY KEY constraint without
> removing the PK constraint itself. So I think you'll either have to drop
> the constraint and recreated it or if you just want to rebuild the index u
se
> DBCC DBREINDEX instead.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>
>|||JD,
I haven't actually used it before but there is a KEY object in SQL-DMO that
I believe will do what you're asking. See 'Key Object' in the SQL Server
Books Online. Also, be sure to read Alejandro's feedback as well if you
have RI established with FOREIGN KEYs to your PRIMARY KEYS.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
> Thanks,
> Can you remove the PK constraint using SQL-DMO? Or will I have to write
> a procedure that loops through all of the tables and remove the PK
> Constraint?
> "Jerry Spivey" wrote:
>|||Thanks for the help!
"Jerry Spivey" wrote:

> JD,
> I haven't actually used it before but there is a KEY object in SQL-DMO tha
t
> I believe will do what you're asking. See 'Key Object' in the SQL Server
> Books Online. Also, be sure to read Alejandro's feedback as well if you
> have RI established with FOREIGN KEYs to your PRIMARY KEYS.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
>
>|||Thanks for your help!
"Alejandro Mesa" wrote:
> You will have to remove first all foreign key constraints, then all primar
y
> key and unique constraints and then you will be able to drop the rest of t
he
> indexes.
>
> AMB
> "JD" wrote:
>sql

DeleteSubscription leaves job in SQL Server

Has anyone else noticed that if you create a timed subscription using

the web service and then delete it using the DeleteSubscription method,

there is a job (id'ed by a guid) left in SQL Server Jobs?

Consequently we now have hundres of "orphaned" jobs in our database :-(

A bit more investigation appears to reveal this:

I use data driven subscriptions and create them using CreateDataDrivenSubscription. Therefore I retrieve them using GetDataDrivenSubscriptionProperties. The matchData returned does not contain a scheduleId. Is that correct? Well calling ListSchedules returns an empty row, indicating that the schedules created are not shared, so cannot be deleted using DeleteSchedule anyway. However, and this is where the bug appears to be, the schedules are in the ReportSchedule table and the jobs are in SQL Server. Calling DeleteSubscription does not delete the SQL Server Job, nor does it delete the row from ReportSchedule. Is this a bug? Is there a fix or do I have to delete the row myself and delete the job from SQL Server using DMO or some such hack.

|||

The SQL Agent jobs should be deleted when the associated subscription is deleted. There appears to be a problem with your system.

Can you take a peek at Event Viewer and SQLAgent log files and see if there is any incriminating evidence?

Delete problem-too many parameters

I have a dataview control with the delete method pointing to a logical delete stored procedure in SQL SERVER Express. I am getting an error message saying too many parameters provided. I've check and there is one parameter expected and one passed in. This is my SP, the html, and the debug infor I'm looking at. Any ideas?

PROCEDUREdbo.usp_Drivers_Delete

@.mintDriver_IDint

AS

UPDATEtblDrivers

SETActive= 0

WHEREDriver_ID=@.mintDriver_ID

html:

<DeleteParameters>

<asp:ControlParameterControlID="GridView1"Name="mintDriver_ID"PropertyName="SelectedValue"

Type="Int32"/>

</DeleteParameters>

Debug:

?SqlDataSourceDrivers.DeleteParameters(0)

{System.Web.UI.WebControls.ControlParameter}

System.Web.UI.WebControls.ControlParameter: {System.Web.UI.WebControls.ControlParameter}

ConvertEmptyStringToNull: True

DefaultValue: Nothing

Direction: Input {1}

Name: "mintDriver_ID"

Size: 0

Type: Int32 {9}

?SqlDataSourceDrivers.DeleteParameters.Count

1

Error:

Exception Details:System.Data.SqlClient.SqlException: Procedure or function usp_Drivers_Delete has too many arguments specified.

Perhaps I'm missing something, but you don't really have a delete. You have an update that sets a status column (Active) to 0. I think if you try changing the <DeleteParameters> to <UpdateParameters> you should be fine.

Deb