Hi everyone, I am a relative newbie to SQL and trying to do things via the self-taught method. I really have an issue that I am just unsure of what to do. I am working with a procedure that is for emailing invoices to customers. There can only be one account for a primary email address. From time to time a user will assign a second account to the email address. When the process runs, it sees the error and will not continue processing the remaining records. Any suggestions as to what I might be able to do? I need to have it so that the remaining records will process. (I think it may be important to note this is an application on a company intranet site.) Thanks for any help you can provide.
Without looking at the system it's difficult to say. You probably shuold change it to allow multiply accounts on an email address. It sounds like the invoices ae being produced for the email addresses and it is getting the account info for the email address. It should be producing invoices for the accounts and the email address should be an attribute.
I guess the easy way out would be to make the email adresses unique.
========================================== Cursors are useful if you don't know sql. DTS can be used in a similar way. Beer is not cold and it isn't fizzy.
nr thanks for replying, the system does in fact call up the invoices to match with the email addresses. The email addresses are basically unique but users are able to attach an account to a primary email address that is already being used. The issue the dept wants me to resolve is being able to continue processing the remaining records once the system hits a snag. Right now, it stops on the error and will not process anymore records. Do you have any ideas on that? I don't know if there is something I need to insert into the DTS or Stored Procedure. Many thanks
I think you will need to identify where in the code the process is stopping. It will either be an explicit decision by the developer to "return" out of what it's doing or it will be because an unexpected error has halted execution. Do you have a "safe" environment where you can duplicate the problem without actually processing invoices and emailing clients?
You can also post the stored procedure here in this thread for additional specific suggestions. If you do that, encluse the code in "code tags" to preserve the formatting (see forum faq)
TG thanks for responding. I don't have a safe environment although I created Backups of tables and some Stored Procedures where I think the issue may be. There are statements in some of the stored procedures that say "Rollback Transaction", is that the same thing as what you mean by "Return" or is it something different?
Not necessarily. Like NR says, it's nearly impossible to trouble-shoot without seeing code. Have you identified the general strategy of the process?
ie: there is a main SP that "loops" through all your clients and for each loop iteration a bunch of other SPs are called. And if one of the called SPs returns an error then the looping stops and no other clients are processed.
I don't want to be insulting but it sounds like you are in over your head :) How is that someone so inexperienced with sql has been tasked to solve this problem?
TG No insult taken-I am in over my head by the luck of the draw. I have not had the benefit of schooling with SQL but have been working hard to learn it on my own. Sometimes people land in situations where they have to 'sink or swim' as they say and that is where I am now. I will try to locate the looping in the process that you mention and work from there. Thanks for the feedback.
That looping thing was just a wild guess on my part. Could be totally different. Understanding existing code is a good way to learn. Post any specific questions if you get stumped on a particular statement.