varchar length
- I'm not sure where to put this, I hope it's ok here.I have the table below. Everything worked fine, until it was decided to change the length of tx_PatientUniqueCode to 125. Now, the field is locked at a length of 20 and I can't figure out why. I've tried to copy the field and make a new one, which works fine. Then I deleted the old one and renamed the new one; only 20. I've also reorganized and rebuilt the indexes; still 20. I updated the statstics with the new indexes, still nothing. I'm trying to avoid dropping the table because they have data they want to keep in it, but that is a possibility because it's for an application that is under development.Does anyone have any ideas of what I'm doing wrong?emCREATE TABLE [dbo].[PATIENT](
[in_PatientID] [int] IDENTITY(1,1) NOT NULL,
[tx_PatientName] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[tx_PatientUniqueCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[tx_PatientNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tx_Physician] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tx_PatientNotes] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[in_GenderLookupID] [int] NULL,
[dt_DateOfBirth] [datetime] NULL,
[in_HomeBedID] [int] NULL,
[in_CurrentBedID] [int] NULL,
[dt_CurrentBedTime] [datetime] NULL,
[tx_UserName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dt_LastChanged] [datetime] NOT NULL CONSTRAINT [DF_TD_PATIENT_dt_LastChanged] DEFAULT (getdate()),
CONSTRAINT [PK__TD_PATIENT] PRIMARY KEY CLUSTERED
(
[in_PatientID] ASC
) ON [PRIMARY],
CONSTRAINT [UQ__TD_PATIENT_UNIQUECODE] UNIQUE NONCLUSTERED
(
[tx_PatientUniqueCode] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-
how are you attempting to change it ? using enterprise manager or are you scripting the change?
MVDBA
-
Is this field in a relation?
Does this code run?
ALTER TABLE dbo.PATIENT
ALTER COLUMN tx_PatientUniqueCode varchar(125) COLLATE SQL_Latin1_General_CP1_CI_AS not null
If not, what error message are you getting?
- Michael, I always run SQL statements, so everything I've done has been that way. This field, however, was changed from 20 to 125 with the Enterprise Manager; and then I was summoned because it didn't work.
Yes, that SQL statement runs without errors, but when I enter 21 characters into that field I get:"No row was updated.The data in row 6 was not committed.Error Source: .Net SqlClient Data Provider.Error Message: String or binary data would be truncated.The statement has been terminated."em -
When a column data is fixed there may be a check constraint on the column which means you have to ALTER Table drop all constraints then ALTER table again and change the column length. Run a search for check constraint in the BOL(books online). Hope this helps.
Kind regards,
Gift Peddie -
do you have a trigger that writes the contents of this field to another location???
or perhaps a cascading update from a foreign key - they might be trying to write the 21 charachters to a varchar(20) location somewhere else ??
MVDBA
- BooYah! You're the best! It was a trigger to the Patient_History table; we had forgotten to change the length of that UniqueCode field.Thank you soo much!em
-
glad to be of service - done it myself before and couldn't figure out why users with usernames longer than 25 chars were failing to run certain procs!!!!
MVDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply