Error Handling using RAISERROR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error Handling using RAISERROR

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-02-23 : 05:03:18

I have read Garth Well's article "Handling Custom Errors in a Client Application", (http://www.sqlteam.com/item.asp?ItemID=2841) and it shows an example how to display the custom error message from an ASP application.

I am trying to do similar things in a Visual Foxpro application. The application calls a stored procedure in SQL Server which has a RAISERROR routine to display a custom error message. Has anyone had experience on how to show the cusom error message when calling from a Visual Foxpro application?

I tried to call the stored procedure, but when the error triggers, it either just shows the system default error message, or it shows an error "Connectivity error: Unable to retrieve specific error information. Driver is probably out of resources."

If I run the stored procedure on SQL Query Analyzer the custom error message can be displayed after the default error message.

Many thanks,
Del Piero

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-02-23 : 13:03:58

Can't help much with Fox Pro but in .NET you can instanciate the Sql Error Object, which would give you greater control on what is being returned.

You can do anything at www.zombo.com

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-10 : 22:44:43

Hi all,

I have come back to this issue. I found that the customized error message can be shown if I first added the message into the sysmessages table. The following illustrates what I mean:

If I merely included a raierror command like raiserror('This is a testing error message!',-1,-1), the Visual Foxpro application cannot return this message. Instead it will say something like "Connectivity error: Unable to retrieve specialized error information".

But if I do this: exec sp_addmessage @msgnum = 50001, @severity=16,
@msgtext=N'This is a testing error message!.',
@lang='us_english'

Then replace the above raiserror with: raiserror(50001,-1,-1), the customized error message can be returned to Visual Foxpro application.

Is this a normal behaviour? i.e. we need to add every ad-hoc message to sysmessages table first instead of specifying ad-hoc message in the raiserror command?

Thanks,
delpiero

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-10 : 23:09:37

Maybe you can use a message that you can pass a argument to. See the example below.


exec sp_dropmessage @msgnum = 50001
go
exec sp_addmessage
	@msgnum = 50001,
	@severity=16,
	@msgtext=N'%ls',@lang='us_english'
go
RAISERROR (50001,16, 1, 'My custom error message')
RAISERROR (50001,14, 1, '****My other error message ****')



Results:
Server: Msg 50001, Level 16, State 1, Line 1
My custom error message

Server: Msg 50001, Level 14, State 1, Line 2
****My other error message ****

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources