Re: Auto truncate a string to be inserted to SQL table
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 01/11/05
- Next message: Hal Heinrich: "Re: How to determine Authentication Mode?"
- Previous message: Tibor Karaszi: "Re: Wrong week of year"
- In reply to: BTLye: "Re: Auto truncate a string to be inserted to SQL table"
- Next in thread: Logicalman: "RE: Auto truncate a string to be inserted to SQL table"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 11 Jan 2005 17:24:42 +0100
It work as expected on my side;
USE tempdb
GO
CREATE TABLE t(c1 varchar(5))
GO
CREATE PROC p AS
INSERT INTO t VALUES('lkjlkjlkjlk')
GO
EXEC p --Warning was returned
SET ANSI_WARNINGS OFF
EXEC p --Warning was not returned
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "BTLye" <BTLye@discussions.microsoft.com> wrote in message news:AE7AD11A-1635-4B06-9FD0-64664DF176B8@microsoft.com... > inside my table there is a INSERT trigger which will invoke a stored > procedure to perform backend processing...I did put the statement as you > suggested but the table is hang once I test for it...Then I put the code in > Query Analyzer, it is also hang...Please see if there is any mistake if you > code like this inside the INSERT trigger: > > ------------------------------------------- > SET ANSI_WARNINGS OFF > exec (proc_my_stored_procedure) > SET ANSI_WARNINGS ON > ------------------------------------------- > > Thanks a lot > > > > "Tibor Karaszi" wrote: > >> See my post about ANSI_WARNINGS. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> http://www.sqlug.se/ >> >> >> "BTLye" <BTLye@discussions.microsoft.com> wrote in message >> news:5442C02F-AA84-4B90-B3A6-DD1F2A235329@microsoft.com... >> > Thanks for the recommendation...But after rule is added...another error >> > message is given...i might need to just truncate the string without halting >> > the process... >> > >> > "Logicalman" wrote: >> > >> >> You will need to establish some rules for the truncation. >> >> >> >> The simplest one is to allow only the first 10 characters through. This is >> >> attained by: >> >> SELECT LEFT(myColName,10) FROM myTableName >> >> >> >> Others may be the right 10 characters (SELECT RIGHT(myColName,10) ..) etc.. >> >> >> >> If you want a more complex algorithm, then I would suggest that INSERTS and >> >> UPDATES to the table are handled vis a Stored Procedure, where your rules can >> >> be effected at much greater depth. >> >> >> >> Tony >> >> >> >> "BTLye" wrote: >> >> >> >> > Hi SQL experts, >> >> > >> >> > May I know how can I auto truncate a string to be inserted to a SQL table to >> >> > avoid from getting the error "String or binary data would be truncated."? For >> >> > instance, if sent-in data has 15 chars but the column length is only set to >> >> > 10, the data will be truncated for its last 5 chars. >> >> > >> >> > I understand that it is definately better to control this in the application >> >> > which performs the record insertion. However, due to certain circumstances I >> >> > might need to control this in SQL server. >> >> > >> >> > Any idea would be much appreciated. Thanks :) >> >> >>
- Next message: Hal Heinrich: "Re: How to determine Authentication Mode?"
- Previous message: Tibor Karaszi: "Re: Wrong week of year"
- In reply to: BTLye: "Re: Auto truncate a string to be inserted to SQL table"
- Next in thread: Logicalman: "RE: Auto truncate a string to be inserted to SQL table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
