EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

EXCEL XP - Need xls not MHTNL

I am running EXCEL 2002 SP#. When I export to Excel it shows the name of the
report in quotes in the filename box (i.e.; "My Report.xls"), however, it is
default saving as an MHTML document.
Is there any way to force an .xls extension?
--
aleJust install Reporting Services SP1 and it will generate BIFF instead of
MHTML.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ale" <ale@.discussions.microsoft.com> wrote in message
news:445A9480-F90D-44A4-96A7-18B173380F67@.microsoft.com...
> I am running EXCEL 2002 SP#. When I export to Excel it shows the name of
the
> report in quotes in the filename box (i.e.; "My Report.xls"), however, it
is
> default saving as an MHTML document.
> Is there any way to force an .xls extension?
> --
> ale