| Author |
Topic |
|
uxphreak
Starting Member
38 Posts |
 Posted - 2005-04-06 : 13:41:51
|
| Hello,Is there a way I can display all data in a query where an error was caused? For example, I run a query to insert data from one table to another, but let's say one record contains data that causes the insert to generate an error. I want to see the data that caused the error so that I can troubleshoot what could have caused the problem.Thanks,D |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-06 : 13:49:31
|
insert statements execute as a whole.one way to do it is to insert the data in bacthes of say 5000 records and then use bisection method to see where the error is.Go with the flow & have fun! Else fight the flow  |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-06 : 13:51:03
|
| You would use a SELECT statement to see the data.Tara |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 2005-04-06 : 13:55:24
|
| If I'm inserting one record at a time using FETCH, then an error would be generated for each record being inserted that causes the error. I was thinking of using PRINT, but I'm not sure if that is the best approach, and at that I'm not sure exactly how to leverage PRINT when an error is encountered.D |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-06 : 13:59:16
|
| What's the best approach here for your insert is not to use a cursor. Why do you need to process an insert one row at a time?Tara |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 2005-04-06 : 14:07:35
|
| We have to databases on two servers, and I need to get sale data from one database into the other as an order. The order is separated into two tables: the order, and the items in the order. Because the sale contains what was sold and where it was sold from, I first break out where it was sold from as the order, then break out what was sold as the items in the order. First the order is created for each location, then the items sold at the location are added as items for the order. Thus the need for a cursor.When an item being inserted raises an error, it could be any item within the order, which could end up being hundreds of items. I don't have the time to go through each item in the order to find which one(s) caused the error. I need a message that displays the error produced and the data that the caused the error.D |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-06 : 14:17:01
|
| If the field/tables on the database's are formatted the same there should be no problem. unless there is more than you posted or I totaly misunderstand you Tara's right I see no reason for the cursor you are just making it harder for yourself and your system.JimUsers <> Logic |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-06 : 15:10:12
|
| I still don't see a reason for a cursor. And there's not going to be a way to display the data that caused the error without coding it.Tara |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 2005-04-06 : 15:19:14
|
| OK,First, I have one database that consists of sales data. In this database there is a transaction table and a txline table. The transaction table consists of data about a transaction, and the txline table consists of of items sold for each transaction.The second database is used by manufacturing and consists of data related to products, orders, BOM, etc.When a store sells product, a transaction is created and each item sold is entered into the txline table. The second database needs this information for many reasons, one of which is to manage inventory at each location. In order to do so, the transactions (including items sold) must be entered into the second database as an order, with items sold for the order.For instance, if store 1 sells 25 paper plates, 24 rolls of paper towels, and 36 forks, spread throughout the day as, let's say, 15 transactions, then there would be one order for store 1, with the sum of each item in the order.Let's say the something is wrong with one of the items, for instance, maybe the item # is different between both databases. If I insert the item into the second database, an error will occur and the rest of the insert will continue. Unless I go through each item in the order and attempt to insert individually, I have now way of knowing which item caused the error.Now, back to my original question, is there a way to display the data that caused an error when an error occurs?D |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-06 : 15:22:09
|
| Yes there is. Check the value of @@ERROR. If @@ERROR <> 0, then put the data into a table. The data would be whatever variables you are capturing in the cursor. If you need extra help, we'll need to see your code.Tara |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 2005-04-06 : 16:49:59
|
| I used Print + <Column/Parameter Name> + <Column/Parameter Name> + <Column/Parameter Name>It wasn't clean, but it works.Thanks,D |
|
|
|
|
Show data that caused an error