January 31, 2012 at 9:17 am
I have a column in a table defiled as VARCHAR(245).
If I am trying to insert a string that is longer than 245 characters, the insert statement is failing.
Is there any global setting (SQL Server 2008 R2-Standard) that I can turn on to truncate the string which is more than 245 characters in length and store the first 245 characters in the column..?
Thanks!
January 31, 2012 at 9:35 am
I don't know about any settings, but is there a reason you can't just do a left(yourColumn, 245) on whatever you are inserting?
January 31, 2012 at 9:44 am
THANKS for your response.
We are migrating an old application from Sybase ASE 12.5 to SQL Server 2008 R2.
Sybase does this automatic truncate..so, if the users inserts more than 245 characters, it is automatically truncated and stored in the DB.
Users want the same behaviour in SQL Server as well.. I checked with the application team to put a warning message in the GUI if the string length is greater than 245.
Since there are lot of places it could happen, management doesn't want to implement that solution in the UI at this point since we are close to the deadline.
UI team will implement it in the next release...but I need to take care of it now.
January 31, 2012 at 10:18 am
I need to ask...
Changing the column from varchar(245) to varchar(max) is an option?
January 31, 2012 at 10:46 am
I proposed it...but Varchar(max) is not accepted by the team...they want to limit it to 245
January 31, 2012 at 10:50 am
Can You rely on a trigger to safe truncate the data to the column?
And tell your team it's not acceptable to the UI to sent invalid data to the DB!
And tel it LOUDLY!
January 31, 2012 at 10:53 am
You can set ANSI_WARNINGS OFF, but it may have other effects that are not desired (like arithmetic overflow and divide by zero not throwing errors). Best option is to truncate before attempting to insert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2012 at 10:56 am
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?
No, because types are checked before triggers fire.
CREATE TABLE tooSmall (
SomeCol VARCHAR(10)
)
GO
INSERT INTO tooSmall VALUES ('1q23u132838907432897289789247832948')
/*
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
*/
go
CREATE TRIGGER trg_Truncate ON tooSmall INSTEAD OF INSERT
as
INSERT INTO tooSmall
SELECT LEFT(SomeCol, 10) FROM INSERTED
go
INSERT INTO tooSmall VALUES ('1q23u132838907432897289789247832948')
/*
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
*/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2012 at 11:08 am
Let me clarify,
Alter the column to varchar(max) AND create the trigger, it ill avoid you to set off warnings (setting it off is a bad, bad, very bad idea)
and keep the size requiriments for your team since data ill never get over 254 characters.
January 31, 2012 at 11:15 am
Siva Ramasamy (1/31/2012)
I proposed it...but Varchar(max) is not accepted by the team...they want to limit it to 245
Does it means that "the team" is Okay with losing data rather than extend the size of that particular column?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 31, 2012 at 11:21 am
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?No, because types are checked before triggers fire.
Yes, if you use a view 😉 ...
CREATE TABLE dbo.Test
(
String varchar(245) NOT NULL
);
GO
CREATE VIEW dbo.TestView
WITH SCHEMABINDING AS
SELECT
String = CONVERT(varchar(8000), String)
FROM dbo.Test AS t;
GO
CREATE TRIGGER [trg TestView IOI]
ON dbo.TestView
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.Test (String)
SELECT
LEFT(String, 245)
FROM INSERTED;
END
-- Success
INSERT dbo.TestView (String) VALUES(REPLICATE('X', 250));
GO
SELECT
t.String,
DataSize = DATALENGTH(t.String)
FROM dbo.Test AS t
GO
DROP VIEW dbo.TestView;
DROP TABLE dbo.Test;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 11:24 am
I understand the team worry about extending the size of the column.
It can broke some procedure expecting a 245 size string.
I dont know you DB or app, but I know changing from sysbase can be a pain.
and I guessing the worst: the app is old, no layers and no fancy SPs (or you can easily change only a single line in the app/SP to truncate it), maybe even no OO.
January 31, 2012 at 11:24 am
jcb (1/31/2012)
I need to ask...Changing the column from varchar(245) to varchar(max) is an option?
VARCHAR(something <= 8000) would be better. MAX types prevent online index builds (before SQL Server 2012 is released anyway) and typing the column as a potential LOB has all sorts of other query plan side-effects.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 31, 2012 at 11:29 am
SQL Kiwi (1/31/2012)
GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?No, because types are checked before triggers fire.
Yes, if you use a view 😉 ...
True, but probably not an option in this case seeing as they're looking for a quick fix and locating all the places that it can happen for a fix isn't apparently allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2012 at 11:32 am
Personally... I would tell them no. We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer. Tell them SQL Server has these safeguards in place to ensure data integrity.
Somehow you have to force them to do things the right way. That's my 2 cents anyway...
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply