SQL Server Performance Forum – Threads Archive

Creation of log for stored procedure

Hello everyone, I am creating a master stored procedure, which gives calls to another 35 procedures. All these 35 procedures are used to transfer the data from one databse to another with some kind of formatting. Curretnly we execute these procedures one by one and print the message whenever there is any chance of error. For example, while transfering the Company table from source to destination, if the source table has company address as null then that record should not get added into the destination database.
the code in the stored procedure is if (@cAddress is null)
insert into companies
else
Print ‘Company name null. cannot insert record’
This works ok, when we execute one procedure at a time. but now i want to make a master procedure which will give call to all 35 procedures.
I want to keep track of all the errors for all the stored procedures. Pls can any one tell me how this can be done. The error are supposed to be stored in a file. I was planning to do this by inserting the error records in table and then using that table we can create alog. but the problem is , here we cant store the specific errors, like in which procedure it
occoured, for which field and for which record. what was the problem and all those. Pls do help me thanks in advance Pallavi

Hi, But where is the problem? What is the problem if u call all 35 stored procedure in the master one and the print there own error message? I believe it shud work. Regards
Sachin Samuel

Thanks for the reply The Problem is that i want to store all the errors in another file. This is ok for the messages that we have given, like the example given above, but there can be other runtime errors also. I want to store all messages and runtime errors in a log file. I want to know how to do that? Pallavi

See if these help you
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> I was planning to do this by inserting the error records in table and then using that table we can create alog. but the problem is , here we cant store the specific errors, like in which procedure it occoured, for which field and for which record. what was the problem and all those. <hr height="1" noshade id="quote"></blockquote id="quote"> Why cant you do this? your error table could be something like: Procedure, Table, Field, UniqueIdentifierOfErrorRecord And when u have an error do something like: INSERT INTO ErrTab SELECT &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rocedureName reporting error>, <tableBeingInsertedInto>, <field with the error>, <concatenated identifier> ’I reject your reality and substitute my own’ – Adam Savage

Pallavi: If any fatal error occured during the execution of your procedure then its not possible to logged it into any table or file. Further more sql server will abort the execution. For eg. if 3rd SP giving fatal error while executing your Master Procedure then rest of 27 SPs will not be executed. so make sure that this condition never arise. And if you want to logged the Business Logic / Non fatal errors then you can always use @@ERROR variable to trap the errors and insert the record into the table with whatever informations you required as per benwilson has stated in his reply. Regards,
Mayur.

]]>