Toad World® Forums

Alter Table - just ignored (TSS 5.0.0.444 on Win7 64)


#1

Hi all,

is it just me or did i find a bug? I tried to remove a column using Object
Explorer, right click, Alter Table and removed the column. After clicking OK it
takes about 1 minute to switch to the “generating script” screen and almost
another one to come to the conclusion “No changes detected”. BTW the table is
empty.
I logged off and on, rebooted but always the same result. This is a real PITA!

Best regards from Berlin/Germany,
Stefan


#2

I seem to be having a similar issue (but with TSS 5.0.1.544, Win7 64bit):

I have a few tables in my database where the primary key (integer in all cases) isn’t set to use auto increment. I want to change this, so I do the following:

Right-click table > Alter Table > Select the PK column > Set “Identity” to “true” > Switch to “SQL Script” to view changes (as I need the script to do the same changes several other places)

For the first table, this worked just fine. When I switched to “SQL Script” it generated a nice script for me (quite a lot it has to do actually: rename foreign key relations, rename table, re-create table, copy old values etc etc).

For two other tables however, it didn’t work. I changed the “Identity” to “true”, but when switching to “SQL Script” it seemed to be stuck doing some processing, and after a short while it just said “-- No changes detected”.

Hope somebody can point at something I’m missing, cause I wouldn’t have to write all those scripts manually - too many small details to potentially be fudged up :wink:


#3

I’m having the problem on an even simpler operation, just trying to change the field to Not Null.


#4

Ddyson, good catch !!!

I was able to reproduce you the problem you have reported…

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary


#5

Ddyson, good catch !!!

I was able to reproduce you the problem you have reported…

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image001.gif


#6

Ddyson, good catch !!!

I was able to reproduce you the problem you have reported…

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#7

Ddyson, good catch !!!

I was able to reproduce the problem you have reported in the alter table wizard.

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take , in the wizard.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#8

Ddyson, good catch !!!

I was able to reproduce the problem you have reported in the alter table wizard.

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take , in the wizard.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image001.gif


#9

Ddyson, good catch !!!

I was able to reproduce the problem you have reported in the alter table wizard.

I tried to modify the Adventureworks table below by changing the Modifieddate
datetime to null and it would not take , in the wizard.

So I confirm you MAY have found something.

SET QUOTED_IDENTIFIER ON ;

GO

CREATE TABLE [HumanResources].[Department] (

[DepartmentID] smallint IDENTITY ( 1 , 1 ) NOT NULL ,

[Name] [dbo].[Name] NOT NULL ,

[GroupName] [dbo].[Name] NOT NULL ,

[ModifiedDate] datetime NOT NULL )

ON [PRIMARY]

WITH (DATA_COMPRESSION = NONE) ;

GO

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary


#10

Hi,
unfortunately we know about these issues in Alter Table. we have number of CRs in this area. This will be definetelly fixed in next release (not in upcoming patch).

Thank you for the help.


#11

Thank you for your answer Alexander.

Is there any logical explanation to why I got the correct sql script when adding identity to the PK in one table, but not in two others (the structure of them are exactly the same)?

And also, is there any rough ETA on the next release?


#12

Hm, it’s rather interesting. Could you please send me DDL script for those tables and mention where Alter Table work fine and where not? We’ll take a look. If it’s posible, we can try to include this fixes in the patch to address your issue, but I can’t promise.

Regarding next release - this is open question for me as well :slight_smile: From my perspective we can wait it in the first half of the next year.


#13

Thank you for following up on this Alexander. I’ve sent you a PM with the details.

As information to others that might have issues with this: I accidentally found that trying the same alter table procedure (adding Identity/auto increment to a couple of PKs) in an older version of Toad (4.6.0.303) worked just fine. I don’t know if this is specific for my exact problem, but it’s worth a shot at least :slight_smile: