Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 8th, 2007, 09:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 14th, 2007, 01:41 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 14th, 2007, 05:17 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)




 
Old March 14th, 2007, 05:20 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old March 15th, 2007, 06:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 3rd, 2007, 12:28 AM
Registered User
 
Join Date: May 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old May 3rd, 2007, 07:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old May 9th, 2007, 04:59 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old May 9th, 2007, 06:51 PM
Registered User
 
Join Date: May 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by robprell
 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...

 
Old May 10th, 2007, 12:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
how to capture java syntax error messages? help.. viber101 Java Databases 2 April 1st, 2009 12:53 AM
SQL Server Reg. SQL Server does not exist error Arsi SQL Server 2000 1 June 11th, 2008 11:20 AM
how to capture 2 SQL queries into 2 datatables hendyhanusin VB.NET 2002/2003 Basics 4 March 26th, 2008 09:31 AM
capture error logs jemacc SQL Server 2000 1 January 10th, 2007 08:17 PM
Error capture from DLL c3r3al_kill3r Classic ASP Components 0 July 1st, 2003 05:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.