Re: Changing a primary key data type

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Peter B.L. Rasmussen (PeterBLRasmussen_at_discussions.microsoft.com)
Date: 10/15/04 

Date: Fri, 15 Oct 2004 07:07:05 -0700

Hi Dan

Thanks for all your help. It seems, however, whenever I get one problem
solved, the next one pops right up.

I now have an SP that does the following:

ALTER TABLE Varer DROP CONSTRAINT aaaaaVarer_PK
DROP INDEX Varer.Varenr
DROP STATISTICS Varer.hind_2099048_3A_22A
DROP STATISTICS Varer.hind_2099048_10A_3A
DROP STATISTICS Varer.hind_2099048_3A_10A
DROP STATISTICS Varer.hind_2099048_22A_3A
ALTER TABLE Varer ALTER COLUMN Varenr nvarchar(15) NOT NULL
ALTER TABLE Varer ADD CONSTRAINT PK_Varer PRIMARY KEY ( varenr, lbnr )
RETURN

But - during the execution, it returns with a "Timeout expired" error, and
the first alter table command has been executed, but not the second (The PK
has not been created). The Varer table has about 17000 records.

This work is only just about 25% of what needs to be done. What should I do
to make it run all the way without a timeout error? I am running on MSDE from
the Visual Studio IDE, but eventually it is supposed to be executed on my
client's SQL Server 2000 server.

Peter

"Dan Guzman" wrote:

> > How do I find the names of
> > these in order to drop them? sp_help doesn't show them.
>
>
> Try:
>
> EXEC sp_helpstats 'MyTable'
>
> You can drop stats with
>
> DROP STATISTICS MyTable.MyStats
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Peter B.L. Rasmussen" <PeterBLRasmussen@discussions.microsoft.com> wrote in
> message news:1FB9ECD1-2873-42AC-9F34-AA5E24564463@microsoft.com...
> > Hi Dan
> >
> > Thanks, that returned the necessary information. It showed me that there
> > was
> > both an index and a constraint named aaaaaVarer_PK. Now I can drop the
> > constraint. Why i couldn't do so earlier, I don't understand. But
> > nevermind
> > that now.
> >
> > Another problem shows up now, that has to do with the actual changing of
> > the
> > data type of the field from int to nvarchar(15). Apparently there are some
> > statistics that are depending on this column. How do I find the names of
> > these in order to drop them? sp_help doesn't show them.
> >
> >
> > "Dan Guzman" wrote:
> >
> >> You can detailed table information, including constraints and indexes,
> >> with
> >> sp_help. Then, use your ALTER TABLE to remove the desired constraint.
> >> For
> >> example:
> >>
> >> EXEC sp_help 'MyTable'
> >>
> >> ALTER TABLE MyTable
> >> DROP CONSTRAINT <constraint name here>
> >>
> >> Consider explicitly naming the constraint with you add it back. This
> >> will
> >> make subsequent schema modifications easier:
> >>
> >> ALTER TABLE MyTable
> >> ADD CONSTRAINT PK_MyTable
> >> PRIMARY KEY (MyColumn)
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Peter B.L. Rasmussen" <PeterBLRasmussen@discussions.microsoft.com> wrote
> >> in
> >> message news:8874FCA5-2FBE-4E0F-BEC9-38417C15C238@microsoft.com...
> >> >I need to change the data type for a column that is part of the primary
> >> >key
> >> > of the table from int to nvarchar(15). I will have to get rid of the
> >> > primary
> >> > key constraint first, bu I can't seem to figure out how to do it.
> >> >
> >> > The constraint (or whatever it is) was originally created by Microsoft
> >> > Access Upgrading Wizard, so I don't know what it has made.
> >> >
> >> > I know that the name of the object is "aaaaaVarer_PK"
> >> >
> >> > if I try to drop constraint:
> >> > alter table Varer drop constraint aaaaaVarer_PK
> >> > I get: 'aaaaaVarer_PK' is not a constraint.
> >> >
> >> > if I try to drop index:
> >> > DROP INDEX Varer.aaaaaVarer_PK
> >> > I get: An explicit DROP INDEX is not allowed on index
> >> > 'Varer.aaaaaVarer_PK'. It is being used for PRIMARY KEY constraint
> >> > enforcement.
> >> >
> >> > There must exist a PK constraint related to this index. Where do I find
> >> > the
> >> > name of that constraint?
> >> >
> >> > in sysobjects aaaaaVarer_PK has xtype=PK, type=K, id=18099105
> >> >
> >> > id 18099105 exists in sysconstraints, but all I can see there is that
> >> > it
> >> > is
> >> > related to the Varer table
> >> >
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Changing a primary key data type
    ... Peter ... > ALTER TABLE Varer ALTER COLUMN Varenr nvarcharNOT NULL ... > client's SQL Server 2000 server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Alter table errors due to statistics
    ... The hind_ statistics are really not statistics, but Hypothetical INDexes, ... > "Tom Moreau" wrote in message ... >> and then do your ALTER TABLE. ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table errors due to statistics
    ... and then do your ALTER TABLE. ... Columnist, SQL Server Professional ... "Gary Johnson" wrote in message ... ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this ...
    (microsoft.public.sqlserver.server)
  • Re: Output from SET STATISTICS IO highly suspect
    ... statistics IO has always been somewhat flaky... ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... > After the tests were finished, my test database was left with 1287,67 MB> available from the pre-allocated 1500 MB. ... The tempdb had grown to a> whopping 2000+ MB!!! ...
    (microsoft.public.sqlserver.server)
  • Re: Error from Maintenance plan
    ... You most probably have either an index on that computed column, or SQL Server has created statistics ... > CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.server)