Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old March 8th, 2007, 09:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #12 (permalink)  
Old March 14th, 2007, 01:41 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
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.

Reply With Quote
  #13 (permalink)  
Old March 14th, 2007, 05:17 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
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)




Reply With Quote
  #14 (permalink)  
Old March 14th, 2007, 05:20 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
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


Reply With Quote
  #15 (permalink)  
Old March 15th, 2007, 06:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #16 (permalink)  
Old May 3rd, 2007, 12:28 AM
Registered User
 
Join Date: May 2007
Location: , , .
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


Reply With Quote
  #17 (permalink)  
Old May 3rd, 2007, 07:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #18 (permalink)  
Old May 9th, 2007, 04:59 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
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?

Reply With Quote
  #19 (permalink)  
Old May 9th, 2007, 06:51 PM
Registered User
 
Join Date: May 2007
Location: , , .
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...

Reply With Quote
  #20 (permalink)  
Old May 10th, 2007, 12:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:21 AM.


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