|
 |
asp_databases thread: Error renaming table in Microsoft SQL Server 7
Message #1 by "Marco van Schaijk" <Marco.vanSchaijk@e...> on Mon, 1 May 2000 7:32:20
|
|
Hello,
I have a problem with db_rename in MS-SQL7.
I have created an alter script on a database-table. This alter script
raises some errors which I can reproduce with following Script.
>>>>>>> Start Script >>>>>>>
create table Rename_Test (
Test_ID Int not null,
Test_Char Varchar(10),
constraint PK_Rename_Test primary key (Test_ID)
)
go
sp_rename Rename_Test, tmp_Rename_Test
go
create table Rename_Test (
Test_ID Int not null,
Test_Char Varchar(15),
constraint PK_Rename_Test primary key (Test_ID)
)
go
<<<<<< End Script <<<<<<<
I'll get the following output when running this script in an empty
database:
>>>>>>>> Start output >>>>>>>
Caution: Changing any part of an object name could break scripts and
stored
procedures.
The object was renamed to 'tmp_Rename_Test'.
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'PK_Rename_Test' in the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
<<<<<<<< End Output <<<<<<<
The TransactSQL Help file sais:
sp_rename automatically renames the associated index whenever a PRIMARY
KEY
or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY
KEY constraint, the primary key is also automatically renamed by
sp_rename.
But in the database tmp_Rename_Test has a Primary Key 'PK_Rename_Test'.
I think this is wrong. This should be 'tmp_PK_Rename_Test' or something
like that.
Does anyone know a solution to let db_rename do its job better? Is there a
patch which fixes this?
Greetings,
Message #2 by "Ken Schaefer" <ken.s@a...> on Tue, 2 May 2000 10:08:50 +1000
|
|
<Off the top of my head>
Renaming the table wont rename the constraint - the constraint retains the
same name. Then you try to create another constraint on the new table with
the same name as the old constraint...whoops :-)
</Off the top of my head>
Cheers
Ken
----- Original Message -----
From: "Marco van Schaijk"
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, May 01, 2000 7:32 AM
Subject: [asp_databases] Error renaming table in Microsoft SQL Server 7
> Hello,
>
> I have a problem with db_rename in MS-SQL7.
>
> I have created an alter script on a database-table. This alter script
> raises some errors which I can reproduce with following Script.
> >>>>>>> Start Script >>>>>>>
> create table Rename_Test (
> Test_ID Int not null,
> Test_Char Varchar(10),
> constraint PK_Rename_Test primary key (Test_ID)
> )
> go
>
> sp_rename Rename_Test, tmp_Rename_Test
> go
>
> create table Rename_Test (
> Test_ID Int not null,
> Test_Char Varchar(15),
> constraint PK_Rename_Test primary key (Test_ID)
> )
> go
> <<<<<< End Script <<<<<<<
>
> I'll get the following output when running this script in an empty
> database:
> >>>>>>>> Start output >>>>>>>
> Caution: Changing any part of an object name could break scripts and
> stored
> procedures.
> The object was renamed to 'tmp_Rename_Test'.
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'PK_Rename_Test' in the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> <<<<<<<< End Output <<<<<<<
>
> The TransactSQL Help file sais:
> sp_rename automatically renames the associated index whenever a PRIMARY
> KEY
> or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY
> KEY constraint, the primary key is also automatically renamed by
> sp_rename.
>
> But in the database tmp_Rename_Test has a Primary Key 'PK_Rename_Test'.
> I think this is wrong. This should be 'tmp_PK_Rename_Test' or something
> like that.
>
> Does anyone know a solution to let db_rename do its job better? Is there a
> patch which fixes this?
>
> Greetings,
>
> ---
> You are currently subscribed to asp_databases
$subst('Email.Unsub')
>
>
Message #3 by "Van Schaijk, Marco" <marco.vanschaijk@e...> on Thu, 4 May 2000 06:46:33 +0100
|
|
Why then, does Power Designer 7 come up with this script.
The TransactSQL Help file sais:
sp_rename automatically renames the associated index whenever a PRIMARY
KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY
KEY constraint, the primary key is also automatically renamed by
sp_rename.
Is everyone who is using Power Designer to maintain database models changing
the alter scripts by hand? I refuse to believe that.
Marco van Schaijk
Systems Engineer
EDS
Netherlands Solution Centre (NLSC)
GUI Competence Centre
Phone: +31-(0)318-585322
Fax: +31-(0)318-528998
Mobile: +31-(0)6-20570745
E-mail: Marco.vanSchaijk@e...
-----Original Message-----
From: Lyris [mailto:lyris@p...]
Sent: donderdag 4 mei 2000 15:45
To: Van Schaijk, Marco
Subject: Re: your command request
Here is the text of Message 2407:
Date: Tue, 2 May 2000 10:08:50 +1000
From: "Ken Schaefer" <ken.s@a...>
Subject: Re: Error renaming table in Microsoft SQL Server 7
<Off the top of my head>
Renaming the table wont rename the constraint - the constraint retains the
same name. Then you try to create another constraint on the new table with
the same name as the old constraint...whoops :-)
</Off the top of my head>
Cheers
Ken
----- Original Message -----
From: "Marco van Schaijk"
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, May 01, 2000 7:32 AM
Subject: [asp_databases] Error renaming table in Microsoft SQL Server 7
> Hello,
>
> I have a problem with db_rename in MS-SQL7.
>
> I have created an alter script on a database-table. This alter script
> raises some errors which I can reproduce with following Script.
> >>>>>>> Start Script >>>>>>>
> create table Rename_Test (
> Test_ID Int not null,
> Test_Char Varchar(10),
> constraint PK_Rename_Test primary key (Test_ID)
> )
> go
>
> sp_rename Rename_Test, tmp_Rename_Test
> go
>
> create table Rename_Test (
> Test_ID Int not null,
> Test_Char Varchar(15),
> constraint PK_Rename_Test primary key (Test_ID)
> )
> go
> <<<<<< End Script <<<<<<<
>
> I'll get the following output when running this script in an empty
> database:
> >>>>>>>> Start output >>>>>>>
> Caution: Changing any part of an object name could break scripts and
> stored
> procedures.
> The object was renamed to 'tmp_Rename_Test'.
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'PK_Rename_Test' in the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> <<<<<<<< End Output <<<<<<<
>
> The TransactSQL Help file sais:
> sp_rename automatically renames the associated index whenever a PRIMARY
> KEY
> or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY
> KEY constraint, the primary key is also automatically renamed by
> sp_rename.
>
> But in the database tmp_Rename_Test has a Primary Key 'PK_Rename_Test'.
> I think this is wrong. This should be 'tmp_PK_Rename_Test' or something
> like that.
>
> Does anyone know a solution to let db_rename do its job better? Is there a
> patch which fixes this?
>
> Greetings,
>
> ---
> You are currently subscribed to asp_databases
$subst('Email.Unsub')
>
>
Message #4 by "Ken Schaefer" <ken.s@a...> on Thu, 4 May 2000 23:08:36 +1000
|
|
> Why then, does Power Designer 7 come up with this script.
i don't know, but if I do this, it works...
create table Rename_Test (
Test_ID Int not null,
Test_Char Varchar(10),
constraint PK_Rename_Test primary key (Test_ID)
)
go
sp_rename Rename_Test, tmp_Rename_Test
go
sp_rename PK_Rename_Test, PK_tmp_Rename_Test
go
create table Rename_Test (
Test_ID Int not null,
Test_Char Varchar(15),
constraint PK_Rename_Test primary key (Test_ID)
)
go
I will fiddle a little bit more...
Cheers
Ken
|
|
|
View Cart
