Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Exclamation Unanswered: Adding length to varchar column fails

    This is a 2005 database. In table OrderDetail, column Phone is varchar(15) not null.

    I then run this script.
    Code:
    alter table [dbo].[OrderDetail]
    	alter column [Phone] varchar(25) not null
    The new column length now shows up in Object Explorer but when I try to modify old data to a length more than 15, I get the truncation error message.

    There are two indexes on the table, one on the primary key and one on a foreign key column.

    What is going on here?
    Last edited by wey97; 02-19-09 at 11:15.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's something specific to your table.
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.testy')) BEGIN
        DROP TABLE dbo.testy
    END
    
    CREATE TABLE dbo.testy
        (
              tester    VARCHAR(15)    NOT NULL
        )
    GO
    
    INSERT    dbo.testy
    SELECT    'fifteen charact'
    
    ALTER TABLE dbo.testy
    ALTER COLUMN tester VARCHAR(25) NOT NULL
    
    UPDATE    dbo.testy
    SET        tester = 'more than fifteen charact'
    Anything else you haven't mentioned?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by pootle flump
    It's something specific to your table.
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.testy')) BEGIN
        DROP TABLE dbo.testy
    END
    
    CREATE TABLE dbo.testy
        (
              tester    VARCHAR(15)    NOT NULL
        )
    GO
    
    INSERT    dbo.testy
    SELECT    'fifteen charact'
    
    ALTER TABLE dbo.testy
    ALTER COLUMN tester VARCHAR(25) NOT NULL
    
    UPDATE    dbo.testy
    SET        tester = 'more than fifteen charact'
    Anything else you haven't mentioned?

    There are a trigger and default constraints on several columns. Maybe these need to be dropped and recreated after the alter column?

    The change script generated by Management Studio is copying the data to a temp table, dropping the old table, and renaming the temp table to a new table. I don't think this is necessary since the alter column should suffice.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My bet is that the trigger is the culprit

    Care to share the definition?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by georgev
    My bet is that the trigger is the culprit

    Care to share the definition?
    Code:
    
    
    CREATE TABLE [dbo].[BizCardOrderDetail](
    	[BizCardOrderDetailId] [int] IDENTITY(1,1) NOT NULL,
    	[BizCardOrderId] [int] NOT NULL,
    	[CardARNG] [varchar](255) NOT NULL,
    	[CardRank] [varchar](20) NOT NULL,
    	[CardDutyPosition] [varchar](255) NOT NULL,
    	[CardAddress] [varchar](255) NOT NULL,
    	[CardCity] [varchar](75) NOT NULL,
    	[CardState] [varchar](50) NOT NULL,
    	[CardZip] [varchar](20) NOT NULL,
    	[CardDutyPhone] [varchar](25) NOT NULL,
    	[CardDutyPhoneExt] [varchar](20) NULL CONSTRAINT [DF_tblCustomBizCard_CustomDutyPhoneExt]  DEFAULT (''),
    	[CardCellPhone] [varchar](15) NOT NULL CONSTRAINT [DF_tblCustomBizCard_CustomCellPhone]  DEFAULT (''),
    	[CardFaxPhone] [varchar](15) NOT NULL CONSTRAINT [DF_tblCustomBizCard_CustomFaxPhone]  DEFAULT (''),
    	[CardEmailAddress] [varchar](255) NOT NULL,
    	[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_BizCardOrderDetail_dateCreated]  DEFAULT (getdate()),
    	[dateVerified] [datetime] NULL,
    	[ExportedToExcel] [int] NOT NULL CONSTRAINT [DF_tblCustomBizCard_ExportedToExcel]  DEFAULT (0),
    	[dateExported] [datetime] NULL,
    	[dateModified] [datetime] NULL,
    	[ExportFileName] [varchar](500) NULL CONSTRAINT [DF_tblCustomBizCard_ExportFileName]  DEFAULT (''),
    	[ProductListIdx] [int] NOT NULL DEFAULT ((3000)),
    	[UOM] [varchar](30) NOT NULL DEFAULT ('BOX'),
    	[QtyPerUOM] [int] NOT NULL DEFAULT ((500)),
    	[ProductPrice] [int] NOT NULL DEFAULT ((1000)),
    	[CardName] [varchar](50) NOT NULL DEFAULT (''),
     CONSTRAINT [PK_tblBizCardInfo] PRIMARY KEY CLUSTERED 
    (
    	[BizCardOrderDetailId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    /****** Object:  Index [idxBizCardOrderId]    Script Date: 02/19/2009 10:03:23 ******/
    CREATE NONCLUSTERED INDEX [idxBizCardOrderId] ON [dbo].[BizCardOrderDetail] 
    (
    	[BizCardOrderId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO
    
    /****** Object:  Trigger [dbo].[trgBizCardOrderDetail]    Script Date: 02/19/2009 10:03:23 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    /****** Object:  trgBizCardOrderDetail */
    CREATE TRIGGER [dbo].[trgBizCardOrderDetail] 
    
       ON  [dbo].[BizCardOrderDetail]
    
       AFTER INSERT,DELETE,UPDATE
    
    AS 
    
    BEGIN
    	SET NOCOUNT ON;      
    	
    	declare 
    		@ipAddress varchar(50), 
    		@changedByUserId int,
    		@IsDeletion bit
    
    	/* Get userId and user IP address from context_info */
    	set @ipAddress = (select ipAddress from dbo.fnLog_GetUserIDandUserIPfromContextInfo())
    	set @changedByUserId = (select changedByUserId from dbo.fnLog_GetUserIDandUserIPfromContextInfo())
    
    	/* Row is deleted from the table */
    	if exists ((select 1 from deleted)) and (not exists (select 1 from inserted)) 
    		begin 
    			set @IsDeletion = 1
    		end
    	/* Row is either updated or inserted */
    	else
    		begin 
    			set @IsDeletion = 0
    		end
    
    	if (@IsDeletion = 1)
    		begin 
    			insert into	[Log_BizCardOrderDetail](
    				[BizCardOrderDetailId],
    				[BizCardOrderId],
    				ChangedByUserId,
    				ChangedByIP,
    				ChangedDate
    			)
    			select
    				[BizCardOrderDetailId],
    				[BizCardOrderId],
    				@changedByUserId,
    				@ipAddress,
    				getdate()
    			from
    				deleted				
    		end
    	else
    		begin 
    			insert into	[Log_BizCardOrderDetail](
    				[BizCardOrderDetailId],
    				[BizCardOrderId],
    				[CardARNG],
    				[CardRank],
    				[CardDutyPosition],
    				[CardAddress],
    				[CardCity],
    				[CardState],
    				[CardZip],
    				[CardDutyPhone],
    				[CardDutyPhoneExt],
    				[CardCellPhone],
    				[CardFaxPhone],
    				[CardEmailAddress],
    				[dateCreated],
    				[dateVerified],
    				[ExportedToExcel],
    				[dateExported],
    				[dateModified],
    				[ExportFileName],
    				[ProductListIdx],
    				[UOM],
    				[QtyPerUOM],
    				[ProductPrice],
    				[CardName],
    				[ChangedByUserId],
    				[ChangedByIP],
    				[ChangedDate]
    			)
    			select
    				[BizCardOrderDetailId],
    				[BizCardOrderId],
    				[CardARNG],
    				[CardRank],
    				[CardDutyPosition],
    				[CardAddress],
    				[CardCity],
    				[CardState],
    				[CardZip],
    				[CardDutyPhone],
    				[CardDutyPhoneExt],
    				[CardCellPhone],
    				[CardFaxPhone],
    				[CardEmailAddress],
    				[dateCreated],
    				[dateVerified],
    				[ExportedToExcel],
    				[dateExported],
    				[dateModified],
    				[ExportFileName],
    				[ProductListIdx],
    				[UOM],
    				[QtyPerUOM],
    				[ProductPrice],
    				[CardName],
    				@changedByUserId,
    				@ipAddress,
    				getdate()
    			from
    				inserted
    		end
    END

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll need to change the length of the field in [Log_BizCardOrderDetail] as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by blindman
    You'll need to change the length of the field in [Log_BizCardOrderDetail] as well.
    I scripted dropping the constraints, indexes and triggers and then I scripted the column alter and recreate the constraints, indexes and triggers.

    Yep, it occurred to me as I got to the trigger that we are logging changes all of the fields so they'd have to match in length.

    Looks like most of it wasn't necessary, I just altered the columns on Log_BizCardOrderDetail then on BizCardOrderDetail and it worked.


    Taking the long way around...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sometimes you have to ask the locals for the shortcuts
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Shore' 'nuff. Y'all head right down this here execushun path and take a left join where the Johnson's table used to be.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •