MSSQL - SELECT * causes SqlDateTime overflow - how to fix?

Hello,

I have an MS SQL 2012 Express DB.  Using Management Studio to run commands.
The DB has many tables, one of them seems to have a problem.

I'm assuming the one row has a out of spec value in 'PayDate' column.  

If I try:
select PayDate from table.name where isdate(PayDate) = 0
then I get 0 rows and 0 error

If I try:
select PayDate from table.name where isdate(PayDate) = 1
then I get 228989 rows and 'query complete with errors'
An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Note, this is just a simple select.  I'm NOT inserting or updating.

How can I track down which row has the bad value and how to edit that value to something in spec?

I might be thinking strange, I'm not an SQL expert - Please help :-)

Thanks for any advice,

--Paul
LVL 2
Chief AvocadoChief of Problem Avocado'sAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
Hi,

Did you try to use CONVERT column to DATETIME in the WHERE predicate? Isdate is non deterministic if not used with CONVERT/CAST.
select PayDate from table.name where isdate(CONVERT(DATETIME,PayDate)) = 1

Open in new window


Regards,
Daniel
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Extra Info: (using the real table names)

USE [myDATABASE]
GO
DBCC CHECKTABLE ('[Practice].[MarketDividendData]')
GO

Open in new window


Result:
Msg 2570, Level 16, State 3, Line 2
Page (1:111172), slot 5 in object ID 661577395, index ID 1, partition ID 72057605572132864, alloc unit ID 72057605743050752 (type "In-row data"). Column "PayDate" value is out of range for data type "datetime".  Update column to a legal value.
DBCC results for 'Practice.MarketDividendData'.
There are 235505 rows in 2506 pages for object "Practice.MarketDividendData".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Practice.MarketDividendData' (object ID 661577395).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
HI Daniel,

The error message will occur with a very simple query

Select * from [Practice].[MarketDividendData];
GO

Open in new window


RESULTS
An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Chief AvocadoChief of Problem Avocado'sAuthor Commented:
FURTHER ATTEMPTS TO FIX:

DBCC CHECKTABLE ('[Practice].[MarketDividendData]', REPAIR_ALLOW_DATA_LOSS)

Open in new window


RESULTS:
DBCC results for 'Practice.MarketDividendData'.
Msg 2570, Level 16, State 3, Line 2
Page (1:111172), slot 5 in object ID 661577395, index ID 1, partition ID 72057605572132864, alloc unit ID 72057605743050752 (type "In-row data"). Column "PayDate" value is out of range for data type "datetime".  Update column to a legal value.
The system cannot self repair this error.
There are 235505 rows in 2506 pages for object "Practice.MarketDividendData".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Practice.MarketDividendData' (object ID 661577395).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
Daniel_PLDB Expert/ArchitectCommented:
Right, now I see. Thanks for clarification

You need to identify rows out of range and update them.
SELECT PayDate FROM [Practice].[MarketDividendData]
WHERE PayDate< '1/1/1753 12:00:00 AM' OR PayDate > '12/31/9999 11:59:59 PM'

Open in new window

0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
I ran the query, but the result is
(0 row(s) affected)

Reading through the results of the DBCC commands, the table has 235505 rows, yet when I do a "select all" then only 228989 rows are returned before the error shows.   I'm thinking that row 228990 is the problem....  I'm looking for ways now to return a specific row...  I suspect 228989 will be ok and 228990 will show the problem
0
Daniel_PLDB Expert/ArchitectCommented:
You can read page and check other column values in the slot specified in DBCC and then using that value update PayDate. Be careful this is an undocumented way. Before fixing anything please perform full backup. Problem is in slot 5.

DBCC TRACEON (3604);
DBCC PAGE (MarketDividendData,1,111172,3);

Open in new window


Edit:
What about convert?
SELECT PayDate FROM [Practice].[MarketDividendData]
WHERE CONVERT(DATETIME,PayDate,101)< '1/1/1753 12:00:00 AM' OR CONVERT(DATETIME,PayDate,101)>'12/31/9999 11:59:59 PM'

Open in new window

0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Let me test your idea, in the meantime I've been tracking down the row (using my simple ways) - Oh yes I have several backups, I'm way out my skills range, so made sure I had good backup 1st.

RowNum = 228989 - OK
RowNum = 228990 - ERROR
RowNum = 228991 - OK


SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY OwnerID) AS RowNum,
            *
        FROM
            [Practice].[MarketDividendData]
    ) sub
WHERE
    RowNum = 228990

Open in new window


RESULTS:
An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
CONVERT =  (0 row(s) affected)
0
Daniel_PLDB Expert/ArchitectCommented:
Noone knows your data better than you :) If you've found good row and value for column which uniquely identify row in that table, just update PayDate column for that row to correct value.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
DBCC TRACEON (3604);
DBCC PAGE (myDATABASE,1,111172,3);

Open in new window


RESULTS: (wow!)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:111172)


BUFFER:


BUF @0x00000000088E0240

bpage = 0x000000034540A000          bhash = 0x0000000000000000          bpageno = (1:111172)
bdbid = 5                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 39412                       bstat = 0x9
blog = 0x5adb215a                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000034540A000

m_pageId = (1:111172)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x220
m_objId (AllocUnitId.idObj) = 178606m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057605743050752                                
Metadata: PartitionId = 72057605572132864                                Metadata: IndexId = 1
Metadata: ObjectId = 661577395      m_prevPage = (1:111171)             m_nextPage = (1:111173)
pminlen = 64                        m_slotCnt = 94                      m_freeCnt = 764
m_freeData = 7240                   m_reservedCnt = 0                   m_lsn = (19824:3516:37)
m_xactReserved = 0                  m_xdesId = (0:4674341)              m_ghostRecCnt = 0
m_tornBits = 335621500              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          
PFS (1:105144) = 0x40 ALLOCATED   0_PCT_FULL                             DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A060

0000000000000000:   30004000 e1070000 dc050000 c55a0300 4f0c0000  0.@.á...Ü...ÅZ..O...
0000000000000014:   24369500 d1070000 4c378941 60e5f53f 00000000  $6•.Ñ...L7‰A`åõ?....
0000000000000028:   cea60000 f97f0000 00000000 91a60000 2dcf83bb  Φ..ù.......‘¦..-σ»
000000000000003C:   b376c33f 0d000006 01004c00 49524953           ³vÃ?......L.IRIS

Slot 0 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 0 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 0 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 0 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219845                 

Slot 0 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 0 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778724                

Slot 0 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 0 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 1.368500000000000e+000  

Slot 0 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2016-11-30 00:00:00.000   

Slot 0 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 0 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 0 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-09-30 00:00:00.000   

Slot 0 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 1.520600000000000e-001  

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (946dd9aaa9a1)       
Slot 1 Offset 0xac Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A0AC

0000000000000000:   30004000 e1070000 dc050000 c65a0300 4f0c0000  0.@.á...Ü...ÆZ..O...
0000000000000014:   24369500 d1070000 a5f78daf 3db3f23f 00000000  $6•.Ñ...¥÷.¯=³ò?....
0000000000000028:   17a60000 f97f0000 00000000 dba50000 e57e87a2  .¦..ù.......Û¥..å~‡¢
000000000000003C:   409fc03f 0d000006 01004c00 49524953           @ŸÀ?......L.IRIS

Slot 1 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 1 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 1 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 1 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219846                 

Slot 1 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 1 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778724                

Slot 1 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 1 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 1.168760000000000e+000  

Slot 1 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2016-05-31 00:00:00.000   

Slot 1 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 1 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 1 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-04-01 00:00:00.000   

Slot 1 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 1.298600000000000e-001  

Slot 1 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (7459f7256d1d)       
Slot 2 Offset 0xf8 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A0F8

0000000000000000:   30004000 e1070000 dc050000 c75a0300 4f0c0000  0.@.á...Ü...ÇZ..O...
0000000000000014:   24369500 d1070000 0551f701 482d0040 00000000  $6•.Ñ....Q÷.H-.@....
0000000000000028:   73a60000 f97f0000 00000000 35a60000 185b0872  s¦..ù.......5¦...[.r
000000000000003C:   50c2cc3f 0d000006 01004c00 49524953           PÂÌ?......L.IRIS

Slot 2 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 2 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 2 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 2 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219847                 

Slot 2 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 2 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778724                

Slot 2 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 2 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 2.022110000000000e+000  

Slot 2 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2016-08-31 00:00:00.000   

Slot 2 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 2 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 2 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-06-30 00:00:00.000   

Slot 2 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 2.246800000000000e-001  

Slot 2 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8d159cb28811)       
Slot 3 Offset 0x144 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A144

0000000000000000:   30004000 e1070000 dc050000 d15a0300 4f0c0000  0.@.á...Ü...ÑZ..O...
0000000000000014:   25369500 d1070000 c74b3789 4160f33f 00000000  %6•.Ñ...ÇK7‰A`ó?....
0000000000000028:   cea60000 f97f0000 00000000 91a60000 f0c4ac17  Φ..ù.......‘¦..ðĬ.
000000000000003C:   4339c13f 0d000006 01004c00 49524953           C9Á?......L.IRIS

Slot 3 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 3 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 3 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 3 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219857                 

Slot 3 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 3 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778725                

Slot 3 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 3 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 1.211000000000000e+000  

Slot 3 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2016-11-30 00:00:00.000   

Slot 3 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 3 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 3 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-09-30 00:00:00.000   

Slot 3 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 1.345600000000000e-001  

Slot 3 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (c4da37824be7)       
Slot 4 Offset 0x190 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A190

0000000000000000:   30004000 e1070000 dc050000 d25a0300 4f0c0000  0.@.á...Ü...ÒZ..O...
0000000000000014:   25369500 d1070000 30f0dc7b b8e4f03f 00000000  %6•.Ñ...0ðÜ{¸äð?....
0000000000000028:   17a60000 f97f0000 00000000 dba50000 29ae2afb  .¦..ù.......Û¥..)®*û
000000000000003C:   ae08be3f 0d000006 01004c00 49524953           ®.¾?......L.IRIS

Slot 4 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 4 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 4 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 4 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219858                 

Slot 4 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 4 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778725                

Slot 4 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 4 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 1.055840000000000e+000  

Slot 4 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2016-05-31 00:00:00.000   

Slot 4 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 4 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 4 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-04-01 00:00:00.000   

Slot 4 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 1.173200000000000e-001  

Slot 4 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (24ee190d8f5b)       
Slot 5 Offset 0x1dc Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A1DC

0000000000000000:   30004000 e1070000 dc050000 d35a0300 4f0c0000  0.@.á...Ü...ÓZ..O...
0000000000000014:   25369500 d1070000 fc6f253b 3602fd3f 00000986  %6•.Ñ...üo%;6.ý?..	†
0000000000000028:   73a60000 f97f0000 00000000 35a60000 d3bce314  s¦..ù.......5¦..Ó¼ã.
000000000000003C:   1dc9c91f 0d000006 01004c00 49524953           .ÉÉ.......L.IRIS

Slot 5 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 5 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 5 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 5 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219859                 

Slot 5 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 5 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778725                

Slot 5 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 5 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 1.813040000000000e+000  

Slot 5 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = INVALID COLUMN VALUE      

Slot 5 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 5 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 5 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-06-30 00:00:00.000   

Slot 5 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 1.502482740300282e-155  

Slot 5 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (dda2729a6a57)       
Slot 6 Offset 0x228 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A228

0000000000000000:   30004000 e1070000 dc050000 d95a0300 4f0c0000  0.@.á...Ü...ÙZ..O...
0000000000000014:   26369500 d1070000 f1f44a59 8638b63f 00000000  &6•.Ñ...ñôJY†8¶?....
0000000000000028:   0ca70000 f97f0000 00000000 cfa60000 ca15dee5  .§..ù.......Ϧ..Ê.Þå
000000000000003C:   22be833f 0d000006 01004c00 49524953           "¾ƒ?......L.IRIS

Slot 6 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 6 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 6 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 6 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219865                 

Slot 6 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 6 Column 5 Offset 0x14 Length 4 Length (physical) 4

f_ParentID = 9778726                

Slot 6 Column 7 Offset 0x18 Length 4 Length (physical) 4

DivType = 2001                      

Slot 6 Column 8 Offset 0x1c Length 8 Length (physical) 8

NetAmount = 8.680000000000000e-002  

Slot 6 Column 9 Offset 0x24 Length 8 Length (physical) 8

PayDate = 2017-01-31 00:00:00.000   

Slot 6 Column 10 Offset 0x0 Length 0 Length (physical) 0

DistReason = [NULL]                 

Slot 6 Column 11 Offset 0x0 Length 0 Length (physical) 0

DistQualifying = [NULL]             

Slot 6 Column 12 Offset 0x30 Length 8 Length (physical) 8

EffDate = 2016-12-01 00:00:00.000   

Slot 6 Column 13 Offset 0x38 Length 8 Length (physical) 8

TaxCredit = 9.639999999999999e-003  

Slot 6 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (75f9058e1b74)       
Slot 7 Offset 0x274 Length 76

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 76                    
Memory Dump @0x000000005221A274

0000000000000000:   30004000 e1070000 dc050000 da5a0300 4f0c0000  0.@.á...Ü...ÚZ..O...
0000000000000014:   26369500 d1070000 2041f163 cc5dbb3f 00000000  &6•.Ñ... AñcÌ]»?....
0000000000000028:   54a60000 f97f0000 00000000 18a60000 bfd4cf9b  T¦..ù........¦..¿Ôϛ
000000000000003C:   8a54883f 0d000006 01004c00 49524953           ŠT.?......L.IRIS

Slot 7 Column 1 Offset 0x48 Length 4 Length (physical) 4

OwnerID = IRIS                      

Slot 7 Column 2 Offset 0x4 Length 4 Length (physical) 4

TaxYear = 2017                      

Slot 7 Column 3 Offset 0x8 Length 4 Length (physical) 4

f_RecordType = 1500                 

Slot 7 Column 6 Offset 0xc Length 4 Length (physical) 4

f_RecordNo = 219866                 

Slot 7 Column 4 Offset 0x10 Length 4 Length (physical) 4

f_ParentType = 3151                 

Slot 7 Column 5 Offset 0x14 Length 4 Length (physica

Open in new window

0
Daniel_PLDB Expert/ArchitectCommented:
Fine, just update PayDate for row with OwnerID = 'IRIS' and  TaxYear = '2017' and f_RecordType = 1500 and  f_RecordNo = 219859 and  f_ParentType = 3151 and  f_ParentID = 9778725 and  f_ParentID = 9778725 and  DivType = 2001 and  NetAmount = 1.813040000000000e+000 :)

I don't know what date your app requires there, maybe you can calculate it or you have defaults or assume by looking in other tables columns.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Out of interest..  If we had not been able to find which row number the error occurred on, what other method  would you use to correct the value of PayDate?
0
Daniel_PLDB Expert/ArchitectCommented:
You've already done this method by looking at the data by issuing select and finding black holes. If we speak about clustered index data (index=1) and heap (index=0). If problem lies in nonclustered index, just rebuild/recreate it :)
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Ok, you just broke my head
0
Daniel_PLDB Expert/ArchitectCommented:
If you want to really search through you can identify nonclustered indexes on that table and see whether any of them is built with your PayDate column. Then you identify which row corresponds to clustered index key value and read data for PayDate column directly from that nonclustered index (with index hint)

select <nonclustered_index_columns> FROM [Practice].[MarketDividendData] WITH (NOLOCK, INDEX(<nonlustered_index_with_PayDate_column>)) WHERE clustred_key_column=<value_for_row_with_error

This'll work because every nonclustered index has clustered key values in the leaf level :)
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
May I ask for your help to craft the update query ?   I'm not strong on this stuff, I'm almost scared.

Lets assume I want to set the PayDate value to '2017-11-23 00:00:00.000'
for row 228990 when ordered by OwnerID

This select was working for me earlier

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY OwnerID) AS RowNum,
            *
        FROM
            [Practice].[MarketDividendData]
    ) sub
WHERE
    RowNum = 228990

Open in new window

0
Daniel_PLDB Expert/ArchitectCommented:
By knowing all values of columns making failed row (besides PayDate) I'd rather stick to values of these columns to make an update, thanks to DBCC PAGE.

BEGIN TRANSACTION
UPDATE [Practice].[MarketDividendData] 
SET PayDate = '2017-11-23 00:00:00.000'
WHERE
OwnerID = 'IRIS' AND TaxYear = '2017' AND f_RecordType = 1500 AND f_RecordNo = 219859 
AND f_ParentType = 3151 AND f_ParentID = 9778725 AND f_ParentID = 9778725 
AND DivType = 2001 AND NetAmount = 1.813040000000000e+000 
AND DistReason IS NULL AND DistQualifying IS NULL 
AND EffDate = '2016-06-30 00:00:00.000' AND TaxCredit = 1.502482740300282e-155;

--if following is ok commit, else rollback
SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY OwnerID) AS RowNum,
            *
        FROM
            [Practice].[MarketDividendData]
    ) sub
WHERE
    RowNum = 228990;
	
--COMMIT TRAN;
--ROLLBACK TRAN;

Open in new window

0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Oh, it does not like it.  Screenshot attached.  I chose ROLLBACK


(0 row(s) affected)
An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Open in new window

2017-11-23-23_12_59-Clipboard.png
0
Daniel_PLDB Expert/ArchitectCommented:
Try first with SET PayDate = NULL, in addition please identify nonclustered indexes on this table. You might need to script their definition, drop, fix data, recreate. However error and DBCC results doesn't imply that.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
I am sorry to report that NULL did not work.  Same error, sqldatetime overflow.
I'll need to read up how to identify nonclustered indexes...  I'm not too sure on this specific part, but I will try
0
Daniel_PLDB Expert/ArchitectCommented:
I'm thinking, since data is wrong anyways, just make an update. Don't run select, just update and commit. I'm not sure we can count on SQL correct behavior with corrupted data in a column.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
aha!  I surprised myself, I think I have the info.   Screenshot attached.
2017-11-23-23_27_41-JW-LAP---Splasht.png
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Update only = (0 row(s) affected).
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
I changed the query around from update to select...  no row returned.
Screenshot attached.
2017-11-23-23_34_47-JW-LAP---Splasht.png
0
Daniel_PLDB Expert/ArchitectCommented:
What's your primary key on this table? I may have made a mistake in columns values. For update just use primary key column(s)

;WITH CTE 
AS
(
	SELECT A.TABLE_SCHEMA,A.TABLE_NAME,A.CONSTRAINT_NAME,B.COLUMN_NAME,B.ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME 
			AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
	WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
		AND A.TABLE_NAME = 'MarketDividendData'
)
SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE A
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
					ORDER BY A.ORDINAL_POSITION
					FOR XML PATH ('') )
					, ' ', ',')
FROM CTE B

Open in new window

0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Aha, I think we can just use the f_RecordNo
Screenshot attached (it fails, so must be the correct row?)
2017-11-23-23_37_03-JW-LAP---Splasht.png
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES YES

IT WORKS!!
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
(1 row(s) affected)
0
Daniel_PLDB Expert/ArchitectCommented:
Heh, what a comment. I've never seen something like this before. Glad your problem's gone ;)
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
SELECT * = 235506 rows

Amazing
0
Daniel_PLDB Expert/ArchitectCommented:
You know you need to troubleshoot the application? Why it allowed corrupted date in that column?

Regards,
Daniel
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
Wow, I am am so very grateful you answered my case and sticking with me until the end.  Thankyou for giving your time to help me, I will do all a can to leave the best endorsement for you.
0
Daniel_PLDB Expert/ArchitectCommented:
Not a problem mate. In case of any problems just get back :) Take care and good luck with the application verification, hope you'll find the bug.
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
The application is a big ugly commercial piece of accountancy software.  It would be pointless to report this to them, I will just get level 1 corporate style support.    I'm thinking, one corruption in 235506 rows of one table from many many tables...

I'm thinking maybe the trouble did not come from the application, but maybe the user turning off the laptop without shutdown
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
I learnt so much about MS-SQL tonight - Thanks
0
Chief AvocadoChief of Problem Avocado'sAuthor Commented:
I cannot thank Daniel enough his help.  Very quickly I felt there was a 'true expert' helping me.   Where else can you get this type of support?  EE and its experts are simply amazing and Daniel is a perfect example of how great it can be.   If I could give 10,000 point I would.

Thanks!
--Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.