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 delete rows that contain same data

Using SQL Sever 2005 Management Studio I am trying to delete rows that are
duplicated in a table. There is no Primary Key. I get an error message to
the effect that the rows cannot be deleted because it would effect other
rows. If I attempt to change a field in the row I get the same error. In
this application there may be many duplicate rows that will need to deleted
at various times.
Thanks,
Bob HillerNo primary key is often an indication of a data model problem. Even if this
is simply a staging table used as part of an ELT process, you can add a
surrogate key to facilitate set-based processing and use GUI tools.
Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
you can specify a TOP clause on a delete statement to delete only a
specified number of rows like the example below. Similarly, you can use SET
ROWCOUNT in earlier version but need to be careful to execute SET ROWCOUNT 0
afterward.
CREATE TABLE Table1 (Col1 int)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
SELECT * FROM Table1
DELETE TOP (2) FROM Table1 WHERE Col1 = 1
SELECT * FROM Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Using SQL Sever 2005 Management Studio I am trying to delete rows that are
> duplicated in a table. There is no Primary Key. I get an error message to
> the effect that the rows cannot be deleted because it would effect other
> rows. If I attempt to change a field in the row I get the same error. In
> this application there may be many duplicate rows that will need to
> deleted at various times.
> Thanks,
> Bob Hiller
>|||Dan,
Yes, All of the columns contain the same values. Is there any way to delete
these rows by hitting the delete key or right click/delete?
Thanks,
Bob Hiller
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
> No primary key is often an indication of a data model problem. Even if
> this is simply a staging table used as part of an ELT process, you can add
> a surrogate key to facilitate set-based processing and use GUI tools.
> Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
> you can specify a TOP clause on a delete statement to delete only a
> specified number of rows like the example below. Similarly, you can use
> SET ROWCOUNT in earlier version but need to be careful to execute SET
> ROWCOUNT 0 afterward.
> CREATE TABLE Table1 (Col1 int)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> SELECT * FROM Table1
> DELETE TOP (2) FROM Table1 WHERE Col1 = 1
> SELECT * FROM Table1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
>|||AFAIK, you can't delete rows using the GUI when all columns have the same
value. This is because the tools generate a DELETE statement behind the
scenes and the row(s) you want to delete is ambiguous when all columns have
the same value. If you must use a GUI, you'll need to add a column to
uniquely identify a row:
ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
>|||Bob,
As Dan has already said, you will probably struggle to delete these rows
using a GUI tool (e.g. Enterprise Manager) because there is no key defined
for the table. You should probably first add a key to the table, then do
your DELETE operations, and then, if you really don't want a key any more
you could remove the key definition (and in doing so, re-introduce the
design flaw).
There are probably ways around this, if you're prepared to write your
own SQL DELETEs rather than use a GUI to perform the delete ops. But that
doesn't change the fact that there is a design problem which needs to be
fixed. A correct RDBMS (database) design will always see a field (or set of
fields) defined as a unique key, even on temporary tables. Perhaps the only
exception would be staging tables used to clean data, but those will,
nevertheless, need a key defined at some point during the migration process.
HTH
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||The Table I am referring to is just being used for testing. I was using a
random number generator in my program that I developed to send the data to
the table. The random number generator at some point started duplicating
numbers. I am now going to implement the table in production mode and there
will of course be a key. It is very important because I am recording
automotive serial numbers which must be unique.
Thanks,
Bob Hiller
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
> Bob,
> As Dan has already said, you will probably struggle to delete these
> rows using a GUI tool (e.g. Enterprise Manager) because there is no key
> defined for the table. You should probably first add a key to the table,
> then do your DELETE operations, and then, if you really don't want a key
> any more you could remove the key definition (and in doing so,
> re-introduce the design flaw).
> There are probably ways around this, if you're prepared to write your
> own SQL DELETEs rather than use a GUI to perform the delete ops. But that
> doesn't change the fact that there is a design problem which needs to be
> fixed. A correct RDBMS (database) design will always see a field (or set
> of fields) defined as a unique key, even on temporary tables. Perhaps the
> only exception would be staging tables used to clean data, but those will,
> nevertheless, need a key defined at some point during the migration
> process.
> HTH
> Robert
>
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
>|||Hey Bob,
Since this sounds like it's an issue of data cleansing for development
work, try something like the following:
SELECT DISTINCT *
INTO newTable
FROM oldTable
TRUNCATE TABLE oldTable
INSERT INTO oldTable
SELECT *
FROM newTable
DROP TABLE newTable
HTH,
Stu|||Bob,
Fair enough mate!
Just a small observation, for what it may be worth, (and yes, I'm being
somewhat pedantic here):
If you've got a scenario where you're first building a test system, and
then shifting to production (which is practically always the case with any
form of software development) then you might as well spend the extra time
developing your test DDL as fully as possible, because doing so will aid you
when it comes to evaluating the behaviour and performance of your client
software. The point here is, if a unqiue constraint had been enforced on
your test table, you're client program would never have been able to insert
those "duplicate" rows -- and so, you'd have been a step ahead...
Wishing you well with your project,
Regards,
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uWN7VS5SGHA.736@.TK2MSFTNGP12.phx.gbl...
> The Table I am referring to is just being used for testing. I was using a
> random number generator in my program that I developed to send the data to
> the table. The random number generator at some point started duplicating
> numbers. I am now going to implement the table in production mode and
> there will of course be a key. It is very important because I am recording
> automotive serial numbers which must be unique.
> Thanks,
> Bob Hiller
>
> "Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
> news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
>

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