October 25, 2007 at 7:03 am
I am trying to check for errors in a DB before i use the data for my developing, what is the best way so that i can find errors and return a result set of those errors
October 25, 2007 at 7:18 am
Can you be a bit more verbose with the type of errors you are looking for?
Are you looking to catch exceptions like divide by zero, or are you looking for a database that may be down?
A little more info will help us greatly so that we can help you.
Thanks.
-Luke.
October 25, 2007 at 7:40 am
I am looking into certain fields for the right data, something like date field shud contain only after the year 1980 or a character field chud have either A/B/C else return a result set of errors...
October 25, 2007 at 8:11 am
If you are looking to limit the records returned by a value or range of values, like your first example, you'd be able to do that in your where clause.
Select myDate
From myTable
WHERE mydate > '1980-01-01'
As for the second one, if you want it to return values from a range and if not in that range I'd probably use a Case statement... something like
Select CASE WHEN myCol in ('A','B','C') THEN myCol
ELSE 'Error' End AS myCol
FROM myTable
you can check out BOL for the entire syntax of the case statement here.
http://msdn2.microsoft.com/en-us/library/ms181765.aspx
Hope this helps..
-Luke.
October 25, 2007 at 8:29 am
Actually i want check these errors before importing data into different DB, if i find erorrs I want to display those in my result set and ask the users to correct the right data.
how about using table variable here
October 25, 2007 at 8:48 am
Well I see this as having a couple of solutions. That depends on how this is going to be handled moving forward however, Is this a one time thing or something that will happen on a scheduled basis? If this is a one time data transfer, I'd just create some queries that would find the records that are not correct. Like for your date column just query where date < '1980-01-01', you could perhaps select those into a temporary table or even a more permanent exportErrors table that you'd be able to consolidate and get to the users to correct.
If you need to do this on a scheduled basis, you could look at perhaps putting constraints on the data for your destination db (which I'd assume you may want anyhow to keep your data nice and orderly since your users are apparently having some issues with this) and then run the import via DTS/SSIS and have the package handle the errors the constraints would throw back at you, perhaps logging them to a table that you could then report on. Also this would give you the flexibility of perhaps only doing partial imports, import everything where the datemodified is between whatever date range you choose for your export time frame.
October 25, 2007 at 8:59 am
Exactly, this will be on sheduled basis but i dont want to use a table to track my errors, as and when I run this proc before import I shud find errors and give a result set from my proc, by seeing which user can correct his errors for right data.
October 25, 2007 at 9:09 am
I take it adding validation to the current application/database is out of the question? It would seem more appropriate to be asking them to correct their data as soon as they enter it, instead of once it's already stored in the db.
Other wise, I don't see how you store this data after you find your errors unless you were to modifiy the current table and once you find the errors you could throw a bit that says this row has an error and perhaps a notes column to describe it to the user. Either way you need some sort of semi permanent storage so that you can then either a) email or b) otherwise notify the user that they need to take care of it and what exactly they need to fix. You can't just log the ID and throw that to the user to say hey there's something wrong with this record, go figure it out. Also, once it's corrected you need to know that it was corrected so that you can check it on the next export routine...
Since it's an asynchronous process you need to perpetuate your data somewhere. Why not use a table?
October 26, 2007 at 6:35 am
yes, but we do not have any control over the user application, the client bought it from different vendor and we are supporting database. we have created another database for a different report using so importing data from the original to this report DB and trying to eliminate wrong data into our reporting DB. but the wrong data is nothing to do with the original application and so these problems
October 26, 2007 at 7:08 am
Mike Levan (10/25/2007)
Exactly, this will be on sheduled basis but i dont want to use a table to track my errors, as and when I run this proc before import I shud find errors and give a result set from my proc, by seeing which user can correct his errors for right data.
I don't see how you are going to show a result set to your client if you don't have the errors stored somewhere. You should probably import the data into a staging table, an exact copy of the production table, where there are no constraints. Then your procedure could insert only valid records into the production table and delete them from the staging table. That way you would always have the bad records in your staging table and they would be accesible for you to present them to your client as needing some editing before inport.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 26, 2007 at 8:07 am
How about checking each field for error and pop up a message of that error. I have 20 fields where i need to check for errors. I wud liek to know once i pop up a mesg through IF statements how can i insert them into a table so that user can read the messages.
October 26, 2007 at 8:11 am
I guess that's what I'm confused about... Will the users be running this import or will it be scheduled automatically to occur at a set time. If It's scheduled, there no way to pop up a message to anyone because the process is running unattended.
That's why we've all been talking about somehow storing your errors and reporting on them after the process completes.
October 26, 2007 at 8:26 am
this will be a sheduled job and i will be emailing them errors occured everyday.
since i have no control on user entryi have to some have send mesg to the user for correction, looking for the best method
October 26, 2007 at 8:57 am
Firstly, depending on how many rows we're talking about the idea of doing your checks with IF statements seems like it would perform rather poorly as you'd be evaluating 1 row at a time instead of working with sets. I'd really strive to get to the idea of using a staging table to import your data into and querying the 20 columns for known good values and populating your final table with those known good values and then remove those known good values from the staging table. Then at the end of your import routine you have your staging table storing only those records that will cause problems.
From there you can use whatever tool you prefer to query that table and email the various records off to the users for correction.
October 26, 2007 at 9:06 am
If its a staging table from where i'll be importing gud data into my Db, how can i handle the left over wrong data in staging table, how can i send mesg to user saying there is an error in so and so column pls correct it?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply