Issue with Error handling

[TonyKhela]

Hi,
I'm trying to capture the error in the @ErrorSave variable, but without success. Please see below.
Any help would be appreciated.
Thanks in advance
TK

CREATE TABLE [dbo].[complaint1](
[Complaint_No] [varchar](8) ,
[Complaint_Description] [varchar](50)
)
/*
Data in the text file d:\complaint.txt
hhhhhhhhhhhhhhhhhhhhhhh
FUPHLD,Fully Upheld (complaint fully upheld),jhj
SPUPHLD,Significant Points of complaint upheld
MPUPHLD,Minor Points of complaint upheld
NOTUPHLD,Complaint not upheld
UNDINVES,Complaint received and under investigation
*/

declare @ErrorSave int
set @ErrorSave=0

set nocount on

BULK INSERT FOI.dbo.complaint1 FROM "d:\complaint.txt" WITH ( FIELDTERMINATOR ='\,')
IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR
select @ErrorSave
select * from complaint1
drop table complaint1

--Error message
--Msg 4863, Level 16, State 1, Line 14
--Bulk load data conversion error (truncation) for row 1, column 1 (Complaint_No).

 

[TonyKhela]

I think the issue is that its not actually getting to the error handling code and bombing out when the error ocurrs.

Any ideas?

 

[SQLDenis]

non trapable error!
raed this

http://www.sommarskog.se/error-handling-I.html

and specifically this

http://www.sommarskog.se/error-handling-I.html#batchabort1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TonyKhela]

So if I need to trap this BULK INSERT error, or a xp_cmdshell error, how would I do that?
I can see that theres an ERRORFILE option, but I need the controlling sproc to capture the error number so I can 1. record it and 2. write logic to handle this error (to notify me etc).
There must be a solution to this.
Any help is appreciated.
Thanks
TK

 

[SQLDenis]

1 parse the file before importing and check for valid format

2 (from the link I gave you)

There is no way to switch off batch-abortion on a general level. But there is actually one way to handle the case in T-SQL, and that is through linked servers. If you call a remote stored procedure, and the procedure runs into a batch-aborting error, the batch in the calling server is not aborted. On return to the local server, @@error holds the value of the error that aborted the batch on the remote server, and the return value of the stored procedure is set to NULL. (At least my tests indicate this. Thus, it is not the same case as when a local procedure dies with scope-abortion, when the return value is not set at all.) It goes without saying, that this is a fairly lame workaround that is only applicable in special situations.

Some notes:

It must be a truly remote server. If you call a procedure in the local server with four-part notation, SQL Server is too smart for you.
Set up the remote server with SQLOLEDB. When I set up the remote server with the OLE DB-over-ODBC provider (MSDASQL), the diagnostics about the error was poorer on the calling server.


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TonyKhela]

Excellent.
I'll try the remote server thing.
on the
"parse the file before importing and check for valid format"
How do I do that? Is it within the format of BULK INSERT?
Thanks
TK

 

[SQLDenis]

no you would parse it with a custome written script, this could be your first step in a DTS and only if the file passed inspection would you go to the next step (importing the file)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/