| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2006-09-07 : 13:26:03
|
| INSERT INTO tbl_emp_details ( emp_no, emp_category, qualified_flg, create_date, created_user, ) SELECT emp_number, emp_category_code, 'N' AS qualified_flg, getdate(), 'emp_driver', FROM tbl_emp_master WHERE emp_number=@emp_number AND indicator IN('A') -- Error Handling Section SELECT @error_int=@@error IF (@error_int <> 0 ) BEGIN SET @sys_error_msg =(SELECT DESCRIPTION FROM master..sysmessages WHERE error = @error_int) END Here I will be getting only the standard error as Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails. But how can I get the exact message as we see it in the sql query analyzer showing which column insert failed of the NULL issue. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-07 : 13:34:39
|
| Don't check sysmessages for the description. The correct information should be going to the application already as a message.Tara Kizer |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-07 : 13:36:37
|
| Note, the following, taken from Books Online:@@ERROR is the only part of a Microsoft® SQL Server™ 2000 error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings such as object names, are returned only to the application in which they can be processed using the API error handling mechanisms. Also, @@ERROR is raised only for errors, not for warnings; therefore, batches, stored procedures, and triggers do not have visibility to any warnings that may have occurred. |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-09-07 : 14:02:10
|
| This stored procedure is never called by an application.These stored procedures are use to populate data from one table to another.So I was trying to get the exact messgage about the error rather than getting the standard error and debugging it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-07 : 14:04:58
|
| You can't get the information that you need using T-SQL as indicated by the quote from BOL.Tara Kizer |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-07 : 14:07:36
|
| Given that only the emp_category can be causing the problem, just add emp_category_code IS NOT NULLto your WHERE clause. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-07 : 14:08:24
|
| Just looking at your code, I would say it has to be either emp_number or emp_category_code.CODO ERGO SUM |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-09-07 : 14:20:50
|
| This was one stored procedure and I have may other stored procedure which populates other tables.So there would be potential to have the null entry issue to a non-null field.So I was trying to capture the exact error message as it comes in query analyzer rather than getting a standard one. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-07 : 14:33:23
|
| You'll need a front-end application to receive the actual error message.Tara Kizer |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-07 : 14:35:37
|
| I guess my point is - watching errors to figure out that you have NULLs in your data when you don't want to is an exercise in futility. Rather define the data and write your stored procedures to either not allow NULLs when they are not allowed, or write code to replace NULLs with meaningful defaults. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-07 : 14:47:31
|
| One wacky alternative for capturing the error messages as raised by sql server would be to create a script that execs all your SPs. Call that script using OSQL directing the ouput to a log file. The log would contain exactly what would show in a QA window.ie:osql -E -n -i "d:\SQLScripts\myScript.sql" -o "d:\SQLScripts\myScript.log" -bBe One with the OptimizerTG |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2006-09-07 : 15:26:24
|
| Thanks a lot for responses.The basis idea is to log the errors that is caused by null values and other issues into a log table and report it..thats the reason I was asking for the specific error message rather than the standard one. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-09-07 : 16:32:24
|
| Just do the insert into with WHERE ... IS NOT NULL.Insert into the log table the records that are bad, or maybe just an indication that there are bad records.insert logtable "there are NULL values in blabla"orinsert badrecordtable select ... from where ... IS NULLcatching expected errors before thay happen is standard procedure.rockmoose |
|
|
|
|
Error Message issue