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/17/05 

Date: Mon, 17 Jan 2005 13:18:44 +0100

I think you will have a tough time if you can't change the code and you can't change the database
setting. I don't know where else you'd look!

Also, some of the database settings that correspond to SET options are essentially worthless as they
are overridden by the programming interface used (ADO, ODBC etc).

Doesn't the application use stored procedures? 

-- 
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:E6E5C995-7A31-4478-B37B-0976509A4005@microsoft.com...
> The INSERT statement is resided at the application. I have no idea where
> should I put the "SET ANSI_WARNINGS OFF" statement since the record insertion
> is performed by the application (my objective is not to touch any code from
> the application). Put to INSERT trigger of the table? But I think the error
> is returned prior to the trigger is invoked. Set it at database level? But
> this will definitely affect other people (they might need the ANSI_WARNINGS
> to be set to ON). Any other way?
>
> "Tibor Karaszi" wrote:
>
>> Again (as posted twice already here), look as SET ANSI_WARNINGS OFF. This will allow the insert,
>> truncating the string and not returning any errors.
>>
>> -- 
>> 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:AD1A6B65-9871-4D1E-9EAF-8603AF089612@microsoft.com...
>> > Sorry for the ambiguity...
>> >
>> > Actually what I want to do is truncate the string using any method at MSSQL
>> > server so that the INSERT action will not prompt me for any error.
>> >
>> > There is an application written by somebody else previously and being
>> > deployed to lot of client PCs. The app will insert record to SQL table at the
>> > end of the processing. I understand that it's faster to add the control
>> > (truncate the string) at the app. But making this will require all the client
>> > PCs to deploy the enhanced app again whereby in my case is not accepted by
>> > the users.
>> >
>> > So I wish to look for any alternative to do it at SQL server. The record
>> > should be inserted into the table by auto truncating any string which exceeds
>> > the max length being allowed by the table without giving any error. This is
>> > because for any error that prompts, the app will terminate and no record will
>> > be inserted.
>> >
>> > Hope that I've made my condition clear. Thanks for the guide so far :)
>> >
>> >
>> > "Logicalman" wrote:
>> >
>> >> BTLye,
>> >>
>> >> I'm not quite with what you are trying to do.
>> >> You have two choices to either turn off the warning and ignore them, or to
>> >> avoid the warnings being produced.
>> >> For the latter (my preference) dependant on how the application is sending
>> >> the INSERT instruction will depend on how you can proceed.
>> >> e.g. If you are calling a SProc from the application, and passing params,
>> >> one of which is the string causing problems, then you can handle this inside
>> >> the SProc ...
>> >>
>> >> ..(start of SProc) ...
>> >>      @myVal nvarchar(100) = ''
>> >> AS
>> >>      SET @myVal  = LEFT(@myVal, 10)
>> >> ... (Rest of SProc) ...
>> >>
>> >>
>> >> If you are using a direct INSERT statement, then you will need to handle
>> >> this in the application itself. A point to note is that SProcs can, and do,
>> >> afford a little extra security to any application/SQL setup, ads they act as
>> >> middle-ware to ensure data integrity (as above) and also avoid any of the
>> >> underlaying table schema being visible to the Application side.
>> >>
>> >> Tony
>> >>
>> >>
>> >> "BTLye" wrote:
>> >>
>> >> > 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 :)
>>
>>
>>

Relevant Pages

  • Re: Find out the database name from the connection string
    ... If you're already connected to the database, ... going to be any more of a performance hit than string parsing. ... In your app config file, keep the parameters you want separate and then ... There is no magic string parser that will look at a connection string ...
    (microsoft.public.sqlserver.server)
  • Re: [PHP] Size Limit PHP + MSSQL
    ... I am trying to grab a very long string that exists in the database. ... When I do a select and print it out, php keep truncating the string - ...
    (php.general)
  • Re: Does anyone know which database engine creates .TDB files?
    ... The extension may simply be generated by the app and not indicate the actual ... Many db's have a string at the beginning of the file to indicate which ... database is being used. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
Quantcast