EXEC date problem

Hello all,
I have a standard stored procedure that contains the following code:

SET @.SQL = 'SELECT id, building, firstname, lastname, closed, assignto, vandalism, entered_by, phone, roomno, status, dateentered, date_closed AS DATECLOSED FROM requests '
SET @.SQL = @.SQL + 'WHERE building_no IN (' + @.BUILDINGLIST + ') AND STATUS_NO = ' + @.STATUS + ' '

Works fine, but my dates come back unformated i want to replace the above:

dateentered and date_closed

with

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED
CONVERT(varchar(30), date_closed, 101) AS DATECLOSED

but, when i do, i receive the following error:

ERROR CLOSE TO AS

Does anyone have any ideas? Thanks in advance.

Richard M.what's it look like when you've already replaced them?

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED

also, dateentered appears to be a field that doesn't exist.

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