Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattgosling72
Starting Member
8 Posts |
 Posted - 2012-02-28 : 10:20:47
|
| Hi,I am having a problem with a Stored Procedure, It has been working for around 4-5 months with no (noticed) problems, but over the last couple of weeks I have had a problem 3 times where a datetime field doesnt get updated correctly. as the datetime field then has a NULL value in it, other aspects of the SP then fail.When I test or rerun the SP with the same data it updates correctly and I'm none the wiser. I've tried running the sp through the debugger and with the profiler but there are no errors to work with...What is the best way to try and troubleshoot this kind of intermittent error?Is leaving a trace running in Profiler a good idea, all on the off chance that there is an error?I'm guessing that the error occurs at the following point in the code, but at the moment have no way of knowing for sure:UPDATE mgedifordhistory SET shed_date = date_req WHERE todays_date = @todays_date AND counter > @last_processeddate_req is a char(8) and shed_date is datetimedate_req (amongst other values) is received via EDI from a supplier,converted to a CSV and then imported into the db Let me know if you need any further information thanksMatt |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-28 : 11:35:23
|
| If you have the ability to modify the stored proc, adding some logging (for example, logging the date string into a logging table) might be one option.Another option would be to add TRY/CATCH blocks and log the errors. There are several methods such as ERROR_LINE(), ERROR_PROCEDURE etc. that can give you additional information. http://msdn.microsoft.com/en-us/library/ms175976.aspxIf the error is really intermittent, I am not too keen on leaving the profiler running.Those are my initial thoughts - hopefully others will add more suggestions/insights. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
mattgosling72
Starting Member
8 Posts |
Posted - 2012-02-28 : 16:25:35
|
quote:
Originally posted by visakh16
why is data_reg a char(8) column if its a date value?
The dat_req field is imported from a CSV file.The CSV is created by a data map within an EDI package called XE. At the time I wrote the sp I couldn't (or didnt research) convert the CSV field to a date on import.To get round it I added an extra column and used the above query to achieve the desired result. Probably not the best option but it worked! Sunitabeck I'll have a further look at what you've suggested thanks |
|
|
|
|
|
|
|
|
intermittent error. char to datetime conversion?