Infact I am not using an other applications... it is easy to capture it from an application, I am trying to do this from within my stored procedure.

Cheers

_________________________
- Vijay G
Strive for Perfection

I'll post you an example later this week of a stored procedure tacking errors. Gotta dig up old code.

Here is an example below. Your example is not an "error" by SQL servers definition I believe so it won't generate an error number. Here is an example of what you can do to create an error message:

If you want to capture the exact text that SQL returns where in names tables and what not like it does in query analyser you need to have something call up the procedure/sql code. Capture the results and then display those results. So you could use a stored procedure that calls up a stored procedure that executes your code and then you can display that results. But directly from sql you can't do what you desire. I hope this helps, Try the code below it was based on the SQL 2000 help files with a few tweeks.


USE pubs
GO

Declare @ErrorNum as int

UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
set @ErrorNum = @@Error
print ''
print ''

-- IF @@ERROR = 547
IF @ErrorNum <> 0
   print 'your error message here'
   print 'the error was number ' + convert(varchar(24),@ErrorNum)

OOps copied the not cleaned up version try this

USE pubs
GO

Declare @ErrorNum as int

UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
set @ErrorNum = @@Error

print ''

-- ERROR = 547
IF @ErrorNum <> 0
begin
   print 'your error message here'
   print 'the error was number ' + convert(varchar(24),@ErrorNum)
end

Rob,

Thanks for your interest in this. If I am right, the code you posted has got to do with custom error message in case it errs out. But I wanted to capture the ...
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN CHECK constraint 'CK__authors__au_id__77BFCB91'. The conflict occurred in database 'pubs', table 'authors', column 'au_id'.
The statement has been terminated.

Mainly the one marked in bold...
Hope this explains.

_________________________
- Vijay G
Strive for Perfection

I am also looking for the solution to this problem. I found a few resources (links below) but none are the *right* solution to the problem.

Getting a well-formatted string out of the DBCC OUTPUTBUFFER seems to be what we all are looking for. Please reply if you know how to do this.

Thanks you.

http://www.nigelrivett.net/SQLTsql/s...putBuffer.html
http://www.sqlservercentral.com/colu...dprocedure.asp

Yes, As mentioned in one of my posts I had earlier been through this link - http://www.nigelrivett.net/SQLTsql/s...putBuffer.html

And found it not working always. Let me try the other one, but that has got some downsides at it claims. Let me see if that helps. Thanks for putting it across.

Cheers.

_________________________
- Vijay G
Strive for Perfection

Do you have to do it on the sql side? Can't the application side trap this info?

There is no application side... It's all in SQL...

Infact I too posted this thread in that intent. Wanted to do that from the SQL side not from application side which is easier ;)

_________________________
- Vijay G
Strive for Perfection