How can I get the message return?

20 Jan 2006 8:28 AM

luyan

For some reasons I want to get the massage of return when I carry out a query.
I only know use @@error:
select * from teste; --teste does't exist
select description from master.dbo.sysmessages where error = @@error
Invalid object name '%.*ls'.
But I hope to get the massage:
Invalid object name 'teste'.

thanks!

20 Jan 2006 8:37 AM

Uri Dimant

Hi

What is you SQL Server's version?

if you are using SQL Server 2005 try
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)

PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'
/* perform corrective activity */
END CATCH

In SQL Server 2000

IF OBJECT_ID('teste ') IS NULL
PRINT 'Invalid object name teste'

Show quoteHide quote

"luyan" <lu***@discussions.microsoft.com> wrote in message
news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@microsoft.com...
> For some reasons I want to get the massage of return when I carry out a
> query.
> I only know use @@error:
> select * from teste; --teste does't exist
> select description from master.dbo.sysmessages where error = @@error
> Invalid object name '%.*ls'.
> But I hope to get the massage:
> Invalid object name 'teste'.
>
> thanks!

20 Jan 2006 8:43 AM

Jens

Therefore you have to use a substitute parameter within the message.
Best thing would be NOT to use SELECT thing, better use the RAISERROR
statement:

USE MASTER

EXEC sp_addmessage
    @msgnum = 60003,
    @severity = 16,
    @msgtext =
        N'Something happened on the way to %s'

RAISERROR(60001,1,1,'heaven')
GO

HTH, Jens Suessmeyer.

20 Jan 2006 8:49 AM

Jens

Sorry wrong RAISERROR according to the exmaples above:

RAISERROR(60003,1,1,'heaven')

20 Jan 2006 8:53 AM

Tibor Karaszi

In 2000, you can't. (Well you can use DBCC OUPUTBUFFER, which is a mess, suggested here a coupe of
days ago. Check the archives.)

In 2005, you can use the new ERROR_MESSAGE() function

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"luyan" <lu***@discussions.microsoft.com> wrote in message
news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@microsoft.com...
> For some reasons I want to get the massage of return when I carry out a query.
> I only know use @@error:
> select * from teste; --teste does't exist
> select description from master.dbo.sysmessages where error = @@error
> Invalid object name '%.*ls'.
> But I hope to get the massage:
> Invalid object name 'teste'.
>
> thanks!

20 Jan 2006 9:24 AM

luyan

Thanks a lot!!

Show quoteHide quote

"Tibor Karaszi" wrote:

> In 2000, you can't. (Well you can use DBCC OUPUTBUFFER, which is a mess, suggested here a coupe of
> days ago. Check the archives.)
>
> In 2005, you can use the new ERROR_MESSAGE() function
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "luyan" <lu***@discussions.microsoft.com> wrote in message
> news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@microsoft.com...
> > For some reasons I want to get the massage of return when I carry out a query.
> > I only know use @@error:
> > select * from teste; --teste does't exist
> > select description from master.dbo.sysmessages where error = @@error
> > Invalid object name '%.*ls'.
> > But I hope to get the massage:
> > Invalid object name 'teste'.
> >
> > thanks!
>
>