Cannot edit maintenance plan created in SQL 2005, SP1

Hello,

I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:

Cannot show editor for this task

Additional information:

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

I also cannot save any change that I make in the schedule of the plan.

I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.

Any help is most appreciated!

Eunice Griffin

I get the same thing - I finally solved all the integration services got to create a backup maint. plan now I can't edit it - what a pain.

Cannot edit maintenance plan created in SQL 2005, SP1

Hello,

I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:

Cannot show editor for this task

Additional information:

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

I also cannot save any change that I make in the schedule of the plan.

I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.

Any help is most appreciated!

Eunice Griffin

I get the same thing - I finally solved all the integration services got to create a backup maint. plan now I can't edit it - what a pain.

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql

cannot drop index....it is being used for Foreign Key Enforcement ??

I try to drop a Non Clustered index on a column and get the following
Message
Server: Msg 3723, Level 16, State 6
An Explicit DROP INDEX is not allowed on index
'dbo.Jobs.nci_Jobs_StartTime'. It is being used for FOREIGN KEY constraint
Enforcement.
Ummm...No It's not...!
I scripted the entire DB and searched for any foreign key referencing this
column and it simply does not exist in the script.
I just wanna drop the index named nci_Jobs_StartTime (is that so wrong?)
Here is the Jobs Table Script:
CREATE TABLE [dbo].[Jobs] (
[JobUID] [uniqueidentifier] NOT NULL ,
[ParentJobUID] [uniqueidentifier] NULL ,
[JobTypeID] [tinyint] NOT NULL ,
[JobStatusID] [tinyint] NOT NULL ,
[CNCSystemID] [tinyint] NULL ,
[JobStep] [tinyint] NULL ,
[HasSubJobs] [bit] NOT NULL ,
[CurrentSubJobStep] [tinyint] NULL ,
[JobStartTime] [datetime] NULL ,
[JobStopTime] [datetime] NULL ,
[JobPickedUpAtTime] [datetime] NULL ,
[JobPickedUpByMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1
_CI_AS
NULL ,
[JobData] [varbinary] (4096) NULL ,
[JobRawData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retry] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
[JobUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [DF__Jobs__Retry__4AB81AF0] DEFAULT (0) FOR [Retry]
GO
CREATE INDEX [nci_Jobs_StartTime] ON [dbo].[Jobs]([JobStart
Time]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] ADD
CONSTRAINT [FK_Jobs_CNCSystem] FOREIGN KEY
(
[CNCSystemID]
) REFERENCES [dbo].[CNCSystem] (
[CNCSystemID]
),
CONSTRAINT [FK_Jobs_Jobs] FOREIGN KEY
(
[ParentJobUID]
) REFERENCES [dbo].[Jobs] (
[JobUID]
),
CONSTRAINT [FK_Jobs_JobStatus] FOREIGN KEY
(
[JobStatusID]
) REFERENCES [dbo].[JobStatus] (
[JobStatusID]
),
CONSTRAINT [FK_Jobs_JobType] FOREIGN KEY
(
[JobTypeID]
) REFERENCES [dbo].[JobType] (
[JobTypeID]
)
GOYou have to drop foreign key, below is the syntax:
ALTER TABLE tablename
DROP CONSTRAINT foreign key
Hope this will help you|||there is no foreign key.
that is the problem.
GAJ
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
> You have to drop foreign key, below is the syntax:
> ALTER TABLE tablename
> DROP CONSTRAINT foreign key
> Hope this will help you|||Strange... What does sp_helpconstraint say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:O1BKuojBEHA.3748@.tk2msftngp13.phx.gbl...
> there is no foreign key.
> that is the problem.
>
> GAJ
>
> "Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
> news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
>|||does not list any FKEY on the JobStartTime Column.
I look in SQL
I look in Toad
I look everywhere
no sign of any FKEY on jobs.JobStartTime
The weird thing is, I can "Change" the index definition. I modified the
index to use a different column, etc. No issues.
I just cant drop the stupid thing.
GAJ|||Strange... Can you produce a repro? If not, I'd suspect some type of
corruption somewhere. Try DBCC CHECKDB and DBCC CHECKCATALOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uJ$OTasBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> does not list any FKEY on the JobStartTime Column.
> I look in SQL
> I look in Toad
> I look everywhere
> no sign of any FKEY on jobs.JobStartTime
> The weird thing is, I can "Change" the index definition. I modified the
> index to use a different column, etc. No issues.
> I just cant drop the stupid thing.
>
> GAJ
>|||what's a "Repro"
I ran dbcc checkdb and all seems well.
this is very very weird
GAJ|||"Repro" = Code with which we can reproduce the error.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uCPqr9sBEHA.3064@.tk2msftngp13.phx.gbl...
> what's a "Repro"
> I ran dbcc checkdb and all seems well.
> this is very very weird
>
> GAJ
>|||the ddl is in my first post.
I wish there was something else I could give you, I'd do it in a heartbeat.
Thanks for the feedback up to now anyway
GAJ|||I'm sorry, but that is not a complete repro. There are tables that you
reference in your FK constraint which are lacking. The point of a repro is
for use to reproduce the same error message. In this case, I didn't find
anything obvious, but sometime you see the problem if you have it in front
of you. Hence the request for a repro. If you can't produce such, it might
be time for opening a case with MS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uSIEoBvBEHA.3344@.tk2msftngp13.phx.gbl...
> the ddl is in my first post.
> I wish there was something else I could give you, I'd do it in a
heartbeat.
> Thanks for the feedback up to now anyway
>
> GAJ
>sql

Cannot Drop Index and PK Constraints

I am trying to create a new column on a table, but realized that there is already an PK index created. I am unable to drop the PK, or even delete the table. I also am unable to delete the index.Please be more specific - what error messages are you receiving ? Is it because of a system error or a constraint error (like you are deleting a primary key which is a foreign key in another table(s)) ?

Cannot Drop Index

I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
ALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda
|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda
|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:

> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>

Cannot Drop Index

I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:
> ALTER TABLE is what you are supposed to use. What statement did you use and
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
> not work.
> Gracias. LaEsmeralda
>

Cannot Drop Index

I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeraldaALTER TABLE is what you are supposed to use. What statement did you use and
what was the error message?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
I could not find a way to remove primary key from an index, thus could not
drop it.
Command:
drop index Romm_Inv.PK_Room_Inv
Error
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
It is being used for PRIMARY KEY constraint enforcement.
The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it did
not work.
Gracias. LaEsmeralda|||Hola LaEsmeralda
You do need to use ALTER TABLE to remove a primary key constraint, which
will drop the index along with it.
What does 'did not work' mean? Can you show us the statement you were using
and the error message you received.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
>I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it
> did
> not work.
> Gracias. LaEsmeralda|||I made it, Tom. Thanks a lot for quick help.
"Tom Moreau" wrote:

> ALTER TABLE is what you are supposed to use. What statement did you use a
nd
> what was the error message?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:F00810B8-62EA-4E19-A1EF-71AA5C8F9AB8@.microsoft.com...
> I could not find a way to remove primary key from an index, thus could not
> drop it.
> Command:
> drop index Romm_Inv.PK_Room_Inv
> Error
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 'Room_Inv.PK_Room_Inv'.
> It is being used for PRIMARY KEY constraint enforcement.
> The help utility refer to ALTER TABLE options to DROP CONSTRAINT but it di
d
> not work.
> Gracias. LaEsmeralda
>

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this?
Thanks in advance.
<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegr oups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this?
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David
|||Well, in theory it's not a "query", right? It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...?
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegr oups.com...
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David
|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegr oups.com...
> Well, in theory it's not a "query", right? It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...?
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David
|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegr oups.com...
> Because it's the client tool that generates the timeout, not the server. If
> Management Studio will give you the DDL, just run it in a new query window.
> David

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this'
Thanks in advance.<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this'
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David|||Well, in theory it's not a "query", right' It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...'
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
> >I am having trouble creating a primary key on a large table (6 mil
> > rows)... on a SQL Server Express 2005 database...
> >
> > After I make the changes to the table and Save, the operation starts
> > but times out after about 30 seconds, saying that the timeout has
> > expired.
> >
> > I tried looking for a timeout that would govern this but I have not
> > been able to find one...
> >
> > My query execution timeout is set to 0 (which is unlimited), though it
> > should be irrelevant because this is not a query.... (in fact, I have
> > no problem running quries that take a few mins)...
> >
> > Any idea where/what I need to change to resolve this'
> >
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Well, in theory it's not a "query", right' It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...'
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> > Well, in theory it's not a "query", right' It's a DDL...
> >
> > I am using Management Studio - I right click on the table,and do
> > Modify, then I select the two fields and right click and do "Primary
> > Key"... Then I finally do Save and that's where the error comes after
> > about 30 seconds...
> >
> > I guess I could get the DDL text of adding these PKs and then try to
> > run that in a query window... though why would that be any
> > different...'
> Because it's the client tool that generates the timeout, not the server. If
> Management Studio will give you the DDL, just run it in a new query window.
> David

Cannot create index, timeout expires (SQL Server Express 2005)

I am having trouble creating a primary key on a large table (6 mil
rows)... on a SQL Server Express 2005 database...
After I make the changes to the table and Save, the operation starts
but times out after about 30 seconds, saying that the timeout has
expired.
I tried looking for a timeout that would govern this but I have not
been able to find one...
My query execution timeout is set to 0 (which is unlimited), though it
should be irrelevant because this is not a query.... (in fact, I have
no problem running quries that take a few mins)...
Any idea where/what I need to change to resolve this'
Thanks in advance.<sdragolov@.gmail.com> wrote in message
news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>I am having trouble creating a primary key on a large table (6 mil
> rows)... on a SQL Server Express 2005 database...
> After I make the changes to the table and Save, the operation starts
> but times out after about 30 seconds, saying that the timeout has
> expired.
> I tried looking for a timeout that would govern this but I have not
> been able to find one...
> My query execution timeout is set to 0 (which is unlimited), though it
> should be irrelevant because this is not a query.... (in fact, I have
> no problem running quries that take a few mins)...
> Any idea where/what I need to change to resolve this'
>
What tool are you using to run the query? Can you get a script of the
change and run it in a query window in SQL Server Management Studio?
David|||Well, in theory it's not a "query", right' It's a DDL...
I am using Management Studio - I right click on the table,and do
Modify, then I select the two fields and right click and do "Primary
Key"... Then I finally do Save and that's where the error comes after
about 30 seconds...
I guess I could get the DDL text of adding these PKs and then try to
run that in a query window... though why would that be any
different...'
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159639554.270613.108730@.c28g2000cwb.googlegroups.com...
>
> What tool are you using to run the query? Can you get a script of the
> change and run it in a query window in SQL Server Management Studio?
> David|||<sdragolov@.gmail.com> wrote in message
news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Well, in theory it's not a "query", right' It's a DDL...
> I am using Management Studio - I right click on the table,and do
> Modify, then I select the two fields and right click and do "Primary
> Key"... Then I finally do Save and that's where the error comes after
> about 30 seconds...
> I guess I could get the DDL text of adding these PKs and then try to
> run that in a query window... though why would that be any
> different...'
Because it's the client tool that generates the timeout, not the server. If
Management Studio will give you the DDL, just run it in a new query window.
David|||David - your suggestion did work... Thanks!
David Browne wrote:
> <sdragolov@.gmail.com> wrote in message
> news:1159647740.250757.210120@.c28g2000cwb.googlegroups.com...
> Because it's the client tool that generates the timeout, not the server.
If
> Management Studio will give you the DDL, just run it in a new query window
.
> David

Cannot create index on view

Hi,
I have created a view but I cannot create index on it. My view is like this:
Create view v2 with schemabinding as
select distinct count_big(orderid) as MyCount from dbo.orders group by
orders.customerid
and my index command is:
create unique clustered index myidx2 on v2(MyCount)
I get this error:
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'Northwind.dbo.v2'. It contains one or more disallowed
constructs.
Whats wrong?
Any help would be greatly appreciated.
AminConsult the documentation - distinct is not allowed.
"Amin Sobati" <amins@.morva.net> wrote in message
news:umwVwcECEHA.2308@.tk2msftngp13.phx.gbl...
> Hi,
> I have created a view but I cannot create index on it. My view is like
this:
> Create view v2 with schemabinding as
> select distinct count_big(orderid) as MyCount from dbo.orders group by
> orders.customerid
> and my index command is:
> create unique clustered index myidx2 on v2(MyCount)
> I get this error:
> Server: Msg 1936, Level 16, State 1, Line 1
> Cannot index the view 'Northwind.dbo.v2'. It contains one or more
disallowed
> constructs.
> Whats wrong?
> Any help would be greatly appreciated.
> Amin
>
>

Cannot create compound unique index - server reports duplicate rows but there are none

Hi
I am using SQL Server 2000. I have a simple table (only 6 columns)
which currently contains a few hundred rows. There are two fields:
SupplierGroupID and RateAdjustmentDate, the combination of which
should never be repeated.
I am trying to create an index with these two fields specified and the
'Create Unique' box ticked, however, when I try to do this I get an
error saying that duplicate data was found.
There are no duplicates in the table. I have confirmed this by running
the SQL:
Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentID
All records report a count of 1.
I have tried this on two instances of SQL Server on two different
servers and get the same error with both.
Why is SQL Server reporting that this is duplicate data when there is
none?
TIA
Paul
Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<pd_oflaherty@.hotmail.com> wrote in message
news:1172758648.458249.68170@.n33g2000cwc.googlegro ups.com...
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>
|||Thanks for posting but these links don't help me in this situation -
the issue is that there are NO duplicates of the field combination for
which I want to create a compound unique index.
I am certain there are no duplicates but still SQL will not allow the
index to be created.
On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> <pd_oflahe...@.hotmail.com> wrote in message
> news:1172758648.458249.68170@.n33g2000cwc.googlegro ups.com...
>
>
>
>
>
>
> - Show quoted text -
|||pd_oflaherty@.hotmail.com,
You should try:
Select SupplierGroupID, RateAdjustmentDate, Count(*)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentDate
having count(*) > 1
go
AMB
"pd_oflaherty@.hotmail.com" wrote:

> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>
|||Forget it - I'm being a numpty - there is a duplicate, just didn't see
it despite flagging with a count and repeated scrolling. I either need
new specs or a bigger screen!
On 1 Mar, 14:55, pd_oflahe...@.hotmail.com wrote:
> Thanks for posting but these links don't help me in this situation -
> the issue is that there are NO duplicates of the field combination for
> which I want to create acompounduniqueindex.
> I am certain there are no duplicates but still SQL will not allow theindexto be created.
> On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -

Cannot create compound unique index - server reports duplicate rows but there are none

Hi
I am using SQL Server 2000. I have a simple table (only 6 columns)
which currently contains a few hundred rows. There are two fields:
SupplierGroupID and RateAdjustmentDate, the combination of which
should never be repeated.
I am trying to create an index with these two fields specified and the
'Create Unique' box ticked, however, when I try to do this I get an
error saying that duplicate data was found.
There are no duplicates in the table. I have confirmed this by running
the SQL:
Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentID
All records report a count of 1.
I have tried this on two instances of SQL Server on two different
servers and get the same error with both.
Why is SQL Server reporting that this is duplicate data when there is
none?
TIA
PaulHi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<pd_oflaherty@.hotmail.com> wrote in message
news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>|||Thanks for posting but these links don't help me in this situation -
the issue is that there are NO duplicates of the field combination for
which I want to create a compound unique index.
I am certain there are no duplicates but still SQL will not allow the
index to be created.
On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> <pd_oflahe...@.hotmail.com> wrote in message
> news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
>
> > Hi
> > I am using SQL Server 2000. I have a simple table (only 6 columns)
> > which currently contains a few hundred rows. There are two fields:
> > SupplierGroupID and RateAdjustmentDate, the combination of which
> > should never be repeated.
> > I am trying to create an index with these two fields specified and the
> > 'Create Unique' box ticked, however, when I try to do this I get an
> > error saying that duplicate data was found.
> > There are no duplicates in the table. I have confirmed this by running
> > the SQL:
> > Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> > from tblInvoiceRateAdjustment
> > Group By SupplierGroupID, RateAdjustmentID
> > All records report a count of 1.
> > I have tried this on two instances of SQL Server on two different
> > servers and get the same error with both.
> > Why is SQL Server reporting that this is duplicate data when there is
> > none?
> > TIA
> > Paul- Hide quoted text -
> - Show quoted text -|||pd_oflaherty@.hotmail.com,
You should try:
Select SupplierGroupID, RateAdjustmentDate, Count(*)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentDate
having count(*) > 1
go
AMB
"pd_oflaherty@.hotmail.com" wrote:
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>|||Forget it - I'm being a numpty - there is a duplicate, just didn't see
it despite flagging with a count and repeated scrolling. I either need
new specs or a bigger screen!
On 1 Mar, 14:55, pd_oflahe...@.hotmail.com wrote:
> Thanks for posting but these links don't help me in this situation -
> the issue is that there are NO duplicates of the field combination for
> which I want to create acompounduniqueindex.
> I am certain there are no duplicates but still SQL will not allow theindexto be created.
> On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
> > Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> > <pd_oflahe...@.hotmail.com> wrote in message
> >news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
> > > Hi
> > > I am using SQL Server 2000. I have a simple table (only 6 columns)
> > > which currently contains a few hundred rows. There are two fields:
> > > SupplierGroupID and RateAdjustmentDate, the combination of which
> > > should never be repeated.
> > > I am trying to create an index with these two fields specified and the
> > > 'Create Unique' box ticked, however, when I try to do this I get an
> > > error saying that duplicate data was found.
> > > There are no duplicates in the table. I have confirmed this by running
> > > the SQL:
> > > Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> > > from tblInvoiceRateAdjustment
> > > Group By SupplierGroupID, RateAdjustmentID
> > > All records report a count of 1.
> > > I have tried this on two instances of SQL Server on two different
> > > servers and get the same error with both.
> > > Why is SQL Server reporting that this is duplicate data when there is
> > > none?
> > > TIA
> > > Paul- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -

Cannot create compound unique index - server reports duplicate rows but there are none

Hi
I am using SQL Server 2000. I have a simple table (only 6 columns)
which currently contains a few hundred rows. There are two fields:
SupplierGroupID and RateAdjustmentDate, the combination of which
should never be repeated.
I am trying to create an index with these two fields specified and the
'Create Unique' box ticked, however, when I try to do this I get an
error saying that duplicate data was found.
There are no duplicates in the table. I have confirmed this by running
the SQL:
Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
from tblInvoiceRateAdjustment
Group By SupplierGroupID, RateAdjustmentID
All records report a count of 1.
I have tried this on two instances of SQL Server on two different
servers and get the same error with both.
Why is SQL Server reporting that this is duplicate data when there is
none?
TIA
PaulHi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<pd_oflaherty@.hotmail.com> wrote in message
news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
> Hi
> I am using SQL Server 2000. I have a simple table (only 6 columns)
> which currently contains a few hundred rows. There are two fields:
> SupplierGroupID and RateAdjustmentDate, the combination of which
> should never be repeated.
> I am trying to create an index with these two fields specified and the
> 'Create Unique' box ticked, however, when I try to do this I get an
> error saying that duplicate data was found.
> There are no duplicates in the table. I have confirmed this by running
> the SQL:
> Select SupplierGroupID, RateAdjustmentDate, Count(SupplierGroupID)
> from tblInvoiceRateAdjustment
> Group By SupplierGroupID, RateAdjustmentID
> All records report a count of 1.
> I have tried this on two instances of SQL Server on two different
> servers and get the same error with both.
> Why is SQL Server reporting that this is duplicate data when there is
> none?
> TIA
> Paul
>|||Thanks for posting but these links don't help me in this situation -
the issue is that there are NO duplicates of the field combination for
which I want to create a compound unique index.
I am certain there are no duplicates but still SQL will not allow the
index to be created.
On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hihttp://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-dupl...
> <pd_oflahe...@.hotmail.com> wrote in message
> news:1172758648.458249.68170@.n33g2000cwc.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||Forget it - I'm being a numpty - there is a duplicate, just didn't see
it despite flagging with a count and repeated scrolling. I either need
new specs or a bigger screen!
On 1 Mar, 14:55, pd_oflahe...@.hotmail.com wrote:
> Thanks for posting but these links don't help me in this situation -
> the issue is that there are NO duplicates of the field combination for
> which I want to create acompounduniqueindex.
> I am certain there are no duplicates but still SQL will not allow theindex
to be created.
> On 1 Mar, 14:21, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -