Failed INSERTs: Saved anywhere?  
Author Message
mmmleo2005





PostPosted: Sat Aug 02 21:52:21 CDT 2003 Top

SQL Server Developer >> Failed INSERTs: Saved anywhere?

We have a simple web form (method=post) that was
processing text comments into a varchar(500) field through
VBScript in ASP. Unfortunately in the version that went
to production, no error handling was performed on form
input that exceeded this 500 character database limit. As
a result we were not trapping the resulting SQL database
error ('String or binary data would be truncated.'). By
the time we realized our problem, we found that we are
missing some data points that we know were submitted.

Is there any other place that this information is stored
outside of the active database (i.e. transaction log) so
that we may still possibly recover? In SQL Profiler, when
testing the page, we are seeing a 'SQL:BatchComplete'
EventClass with the INSERT statement intact (including the
full comments that exceed 500 characters), but are worried
that there is no corresponding transaction log entry or
other storage facility for these failed INSERTs.

Also, why isn't SQL simply truncating the string on its
own?

Any help here would be greatly appreciated.


Tom

SQL Server193  
 
 
oj





PostPosted: Sat Aug 02 21:52:21 CDT 2003 Top

SQL Server Developer >> Failed INSERTs: Saved anywhere? No, unless data is committed nothing can even be found in database log
(*.ldf) - which you could use Lumigent's Log Explorer to restore. Regarding
the truncation, please take a look at the following article for some info on
how data truncation is resolved by sqlserver.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_6d2r.asp?frame=true

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net





> We have a simple web form (method=post) that was
> processing text comments into a varchar(500) field through
> VBScript in ASP. Unfortunately in the version that went
> to production, no error handling was performed on form
> input that exceeded this 500 character database limit. As
> a result we were not trapping the resulting SQL database
> error ('String or binary data would be truncated.'). By
> the time we realized our problem, we found that we are
> missing some data points that we know were submitted.
>
> Is there any other place that this information is stored
> outside of the active database (i.e. transaction log) so
> that we may still possibly recover? In SQL Profiler, when
> testing the page, we are seeing a 'SQL:BatchComplete'
> EventClass with the INSERT statement intact (including the
> full comments that exceed 500 characters), but are worried
> that there is no corresponding transaction log entry or
> other storage facility for these failed INSERTs.
>
> Also, why isn't SQL simply truncating the string on its
> own?
>
> Any help here would be greatly appreciated.
>
>
> Tom
>