OK, Now a datetime Question

Trying to select from invoices where date is between today and today -30 days

WHERE Invoices.Date<DATEADD(d, -30, GETDATE())

gets no results

And of course, minutes later I get it working with

WHERE Invoices.Date between DATEADD(d, -30, GETDATE())and dateadd (d,0,getdate())

|||Does the invoices.date contains date & time ? Is both date inclusive ?

where invoices.date >= dateadd(day, datediff(day, 0, getdate()), -30)
and invoices.date <= dateadd(day, datediff(day, 0, getdate()), 0)|||

Cammyr:

I think what K H Tan is getting at is that there might be a time of day issue with the where clause:

WHERE Invoices.Date between DATEADD(d, -30, GETDATE())and dateadd (d,0,getdate())

When I select this dateadd function I get a date/time of December 31 2007 10:57AM. If you also want to include in your selection invoices that are between midnight and 10:56AM on December 31 then you need to alter your range -- maybe something like:

WHERE Invoices.Date between convert(datetime, convert (varchar(10), dateadd (d, -30, getdate()), 112))
and dateadd (d,0,getdate())

or something like what K H Tan has suggested.

Dave

|||

your two posts are doing two different things.

the first is older than 30 days and the second is within the last 30 days. if what you desire is not inclusive you could have done...

WHERE Invoices.Date > DATEADD(d, -30, GETDATE())

|||

I just noticed I didn't pay any attention to the other half of the BETWEEN clause.

dateadd (d,0,getdate())

doesn't result in anything different than

getdate()

odd stored procedure behavior

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.

Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.

Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.

There's some joins, but the main table has maybe 20 million rows.

This is sql server 2000.

Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.

The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.

Why?

brianbrianlanning (brianlanning@.gmail.com) writes:

Quote:

Originally Posted by

We have a stored procedure that we've tried with two slightly
different designs. It needs to take a 30 day date range and return a
result set.
>
Design 1 takes one date as a parameter. The other date is calculated
in a local variable to be 30 days before the one that was passed in.
Both data types are datetime and are in the where clause.
>
Design 2 takes two dates as parameters with the 30 days being
calculated outside the stored procedure, both in the where clause.
>...
Design 1 takes maybe 30 mintues to run. Design 2 runs 15 times
faster.
>
The plan says that Design 1 is doing a table scan on the 20 million
row table. For Design 2, the plan says it's doing a bookmark lookup
on the date in question.
>
Why?

When the optimizer compiles the query plan, it works the stored procedure
as a whole. Thus it has no knowledge of the values of local variables.
Instead it applies standarad assumption which for BETWEEN is 20%, if
memory serves.

However, the optimizer does look at the parameter values, and use
these as guidance. When it knwos both end of the period, it can
estimate more exactly how many rows you will select. Note however
that this plan is cached, and if you the next time call the procedure
with an interval of, say, two years, the same plan will be used,
although that plan may not be good for this longer interval.

What you also could try is:

datecol BETWEEN @.startdate AND dateadd(DAY, 30, @.startday)

Hopefully, the optimizer understands the condition and can act
accordingly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Odd scheduled job behavior

I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc....haven't seen that one, but you could always check the sysjobhistory table in msdb (if you have the rights) to see what the rundate column has stored.

What version of SQL Server are you on?

Odd null behavior

I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.

Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)

create table #t2
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)

select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField

drop table #t1
drop table #t2

The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?

Thanks in advance.

straight out of BOL:

"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."

use the IS NULL clause to assist.

HTH,

Derek

|||Then why does this return a record?:

set ANSI_NULLS off
go
declare @.var datetime
select @.var = null

create table #t1
(
ID int,
DateField datetime
)

insert into #t1 (ID, DateField) values (1, '7/20/2006')

select * from #t1 where #t1.DateField <> @.var

drop table #t1|||

ANSI NULLS setting only affects comparisons of the form:

column <Operator> @.value

column <Operator> literal

value <Operator> @.value

value <Operator> literal

and combinations thereof. The column to column comparison will always follow the ANSI semantics for NULL value comparison. So you could write your SELECT like:

-- Depends on whether you have datetime values with the default value or not

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> coalesce(#t2.DateField, '')

--or

select * from #t1 join #t2 on #t1.ID = #t2.ID

where #t1.DateField <> #t2.DateField

or (#t1.DateField IS NULL and #t2.DateField IS NOT NULL)

or (#t1.DateField IS NOT NULL and #t2.DateField IS NULL)

Odd drill though date problem

Hi, i'm quite new to Reporting Services and am having some problems with a drill through I've sert up.

I have about 10 reports that link to each other passing all the required peramiters to limit the data shown, this includes 2 dates.

When accessing the first report I put a uk date in (dd/mm/yyyy) but RS decides to change it to a us date (mm/dd/yyyy) which the second report doesnt like as It's expecting a uk date.

I cannot pass the first report a us date as it doent like it.
If i pass it a iso date (yyyy-mm-dd) RS converts it into a uk date and passes that into the report.

I dont beleve I should have to manually format the date for the linkthoughs, but cant find a way around this.
I've tryed the reports on 2 diffrent RS 2000 servers with the same results.
Thanks

Dan

Hi Dan,

In the properties of report all the components can be listed on the top select Report in it and then in Language select English(United Kingdom). This should change the default format to dd/mm/yyyy

Hope this Helps.

|||It is better practice to set Language to "=User!Language".

This picks up the locale from IE.

You can still override formatting on individual items.

Read more:
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx

scroll down to #93|||Hi, I've tryed setting the report manguage to 'Default', English(United kingdom), =User!Language none worked .
I even decided that the date format isnt really important and set the second level report language to English United states, but the report still wanted a uk date!

ag: There is no #93, I assume you mean #33

cheers
|||There is a 93, but you have to scroll down - they have not updated the contents at the top.

I think it might be case sensitive.

In the "language" box in report properies, click on 'expression'.

Then use the boxes at the bottom of the editor to select 'user ! lang...'

Think it is in constants or globals, whatever. Double click it to select.

If it still is not working with user!language, it means your IE settings are not set to UK

Can someone else post how to change IE regional settings, I can't be arsed.|||Hi, I've used = User!Language via the expression editor with no luck.
I've written = User!Language to a text box which prints en-GB so IE must be set to english (uk).

I've found no 93, IE doent show anything above no 38, Firefox however does.

|||You haven't overridden the settings in Start - control panel - regional.. - reg. options.

have you?

I remember a box once having short date as UK and long date as US.

I am using FF, well spotted.

Also check IE - options - general - languages...

and check the lang pref order.

The textbox doesn't have an overriding format ?|||No will be right, will check again in the morning.
I had another guy try the report with the same problem.
I've tryed creating 2 new reports one links to the second, this still has the same problem.

I'm really baffled with this.
|||

Are you having the problem in the Visual Studio IDE, or in the report server when you have deployed the report? I find that in the IDE it ignores UK date formatting...but when deployed to the report server it works fine.

Regards

Jof

|||

The ide requires the date to be passed as a us date so doesnt have this porblem.

The deployed rpeorts however expect the dates to be passed as uk.

dan

Odd date data found in table

Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.

Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error:

Server: Msg 8630, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor encountered an unexpected error during execution.

Here are some of the dates that were present in the table:

DateColumn1

--

1900-01-01 857:44:45.813

1900-01-01 872:51:16.427

1900-01-01 872:54:57.440

1900-01-01 873:09:32.107

1900-01-01 873:13:10.560

1900-01-01 873:16:49.867

1900-01-01 888:27:00.640

DateColumn2

-22063-05-18 00:00:00.000

-20285-02-03 00:00:00.000

In addtion there were some other columns that have had odd data in them(VERY WEIRD)

Char25Column

?Q307000
?Q307000
?Q307000

?Q307000

?Q307000

Any Thoughs?

Thanks

Have you run a DBCC CHECKDB lately? You might have some issue with your hardware, storage, or perhaps some 3rd party filter that's somehow modifying your data.

What version of SQL Server is this? If it is SQL Server 2005 hopefully you have database page checksums enabled so that if something does scribble on database pages, then the changes would be detected the next time you read those pages.

Do you have any XPs (extended stored procedures)? A poorly written/tested XP might scribble across SQL Server's memory.

Check your overall system and consider a call to product support. Also, you probably want to check your recent backups to see if the errors occur there as well.

Don

|||

Hi Don, thanks for the quick reply,to answer your question, i have ran DBCC table checks on the table in question and as expected consistancy error were found.( i would have posted them before but I tried to make the post a readable as possible.) (i'll post them below) the odd thing here is the fact that SQL allowed the corrupted data to be inserted in the first place. i mean you would think that SQL servver would throw up an error or two.

As far as the extended sp yes but they wouldnt have been called during the this data input.(btw was push in via informatic) then again reguardless of the client thats pushing and puling the data, SQL server should have had the last called as to what data got commited.

Here are the dbcc table check results( i have ommited a lot of the extract error that were repeated /per row)

DBCC results for 'tLoadedTable'.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidAmt" value is out of range for data type "decimal". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "InvoiceDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "LoadDate" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Table error: object ID 1762977507, index ID 0, partition ID 72057594677035008. A row should be on partition number 1 but was found in partition number 10. Possible extra or invalid keys for:

Msg 8988, Level 16, State 1, Line 1

Row (85:10:0) identified by (HEAP RID = (85:10:0)).

Too many errors found (201) for object ID 1762977507. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".

There are 133508601 rows in 9102393 pages for object "tLoadedTable".

CHECKTABLE found 0 allocation errors and 336633 consistency errors in table 'tLoadedTable' (object ID 1762977507).

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

Thanks

|||

Im running SQL server 2005 Enterprise SP 2, in addtion this has also happened on another database on a different server also running SQL 2005 SP 2 on a different SAN ( same client though)

Thanks

|||

Don, sorry I missed your question about the page verify mode, currently i running all databases in torn_page_verify mode, were mostly a OLAP shop and dont have a lot of rouge client make data modifications. though based on this little bout i may be changing it to page checksums ( though there seems to be an considerable overhead due to the constaint checksum verifications right? )

Thanks

|||

Since DBCC has detected over 360,000 anomalous data values it's quite likely that you have hardware/storage issues. I suggest you get that looked at before you make any further changes to the data.

It may be that you have a wayward XP that's scribbling outside its intended address space. An XP can write anywhere in SQL Server's writable memory, so even if an XP wasn't supposed to be writing on these pages, it might be doing so accidentally. Of course, a well-written and well-tested XP should do this, but it could have a bug or two that's pointing it in the wrong direction. There's nothing that SQL Server can do to keep your XP from scribbling where it shouldn't, other than replacing XPs with CLR modules as we've enabled with SS2005.

At least page checksums can detect that what was read is not the same as what was written. In your case it would detect these issues if they were caused by the IO system. Torn-page detection might detect an IO problem here, but only if the IO system happened to flip some bits near the torn-page bits on a page (there are only 32 of these bits on an 8K page, so the IO problem could well miss those).

And yes, there is some overhead for the page checksums, but you also would have detected this issue much sooner (if indeed it is an IO issue). You'd have to do some tests to see what the overhead would be for your system; it's hard to predict as it depends on cache hit probabilities and other factors .. but often people find it to be less than they expect .. or they're willing to "pay the price" for it since it will detect issues with the IO/storage system.

So: I suggest you investigate your IO system for issues (check out sqliosim and use it to test your system), and that you double- triple check any XPs to make sure they aren't generating those strings of bytes that you're finding in your dates and decimals (check for any strings/varchars as well .. perhaps it's writing a string of readable characters that you'll be able to use to track down the source of the issues).

Don

|||

Hi Don,

Again thanks for the reply.

As far as IO error there were none, ( funny you'd mentioned the sqliosim utility i had ran a check earlier in the week with no errors found) another thing is that this issue is occurring on two separate servers that also reside on separate Sans, oddly the issue occurred on the exact same partition boundary. as it sits I have a trace running on the entire process and found no evidence that any xp or clr routines were being called. ( just the simple sp_prepare and execute calls for table collation definition information. later followed with a API insert bulk process" that shows no errors during the loading)

Well I have been able to re-produce the issue yet its very weird.

Ok, so if I hadn’t mentioned it before the table in question is partitioned. its partition scheme is also shared by other table ( yet they don’t have the same issue.) the data load is being pushed in via infromatioca API insert bulk call. regardless of which partitioned key is used is still allows the records to be entered yet with a dbcc checktable or simple select is performed on the table it fails and returns the above error.

--

I attempting to loading to other partitioned boundaries and some succeeded with no corruption other succeeded with corruption. the thing that really bothers me is that SQL is allowing this errors to be committed. I have checked the input data and the errors are not there so SQL server has to be corrupting them when the insert bulk block is read into memory.(yet again you would think SQL server would be checking it here)

--test 1

I created another table with the same structure with in the same database but did not add it to a partition scheme( just left it on the primary file group( rather PrimaryData file group, I keep the system objects and user object separate) I ran the same ETL loading process into this new table with no errors and the data was clean( I was able to select against it with no issues and the data look good as expected.)

--test 2

I created another database on the built the same table with partitioned scheme and function within it ( so basically the environment is exactly the same as production minus all other table and the extra data within the partitioned table.)

I ran the same ETL load process against the new database... and it loaded fine, no error no corruption.. ughhh!!!

-- additional

I have ran dbcc dbcheck numerous time yet no errors are even returned. i have placed a call with MS but they seem to think its the third party tool that causing the issue ( though I’m not convinced due to I’m able to load into a non-partitioned table and a partitioned table another database on the same server same disk with no issues.)

YIKES!!!!

Thanks

|||

Yes, it does seem very odd. It would seem that even though a 3rd party API was being used, it shouldn't be able to insert bad values into a datatype. That's why I suspect either an IO issue or an XP issue since the bytes seem be be changed outside of SQL Server's actual control.

I suggest you pursue this some more with MS support together with the 3rd party .. perhaps the 3rd party API is using some XP of its own (some XPs are named sp_xxx, so that can seem like no XPs are being called if you're expecting xp_xxx).

Don

|||

Hi Don, Thanks again for your reply,

I have had my SAN admin review the SAN log to look for any kind latency or stale IO issues ( so far he has been able to find anything. I my self haven’t found anything at the OS or SQL level that you suggest IO or memory issues...) very odd. I have though been able to find out that the insert bulk API that the third party vendor had been using was indeed a odbc call to the bcp.exe, funny though i have since the data being submitted ( I’ve captured the network packets to review the data that was being sent to the bcp process, all is well so the corrupted data is not coming from the third party tool ( potentially its could be the memory block that the odbc driver has consumed when the connection was made to the api. perhaps sql is indeed seeing the correct data value verifying that they are compliant and during the commit process of writing the data to physical disk the memory block is being corrupted by some low level IO driver filter.

In addition after reviewing the trace( I know not all events are trapped by SQL server) again the only operations that are called were the sp_prepare and sp_execute which were unprepared before the api called was made. also in reviewing the third party tools model it show no other sql server functions are called ( just internal application calls)

One other thing was weird, the fact that I was able to load into a non-partitioned table with the same load process (third party tool) as well as another database partitioned table (same scheme, same function, same disk, same server) without data corruption.

Who knows? I’m still perusing the MS avenue and will post my findings( so far it my been a MS bug but there escalation team isn’t in until Monday.)

Meanwhile I have been attempting to read up on driver filters (interesting enough they can modify or FILTER data prior to persisting the data ( i guess it happens in the transfer process from memory to bus to disk.)

Though if anyone has any thought please do tell...

Thanks

|||

Could you send me an email so we can look into this a bit more? DonV@.microsoft.com.(nospam)

Don

Odd date data found in table

Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.

Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error:

Server: Msg 8630, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor encountered an unexpected error during execution.

Here are some of the dates that were present in the table:

DateColumn1

--

1900-01-01 857:44:45.813

1900-01-01 872:51:16.427

1900-01-01 872:54:57.440

1900-01-01 873:09:32.107

1900-01-01 873:13:10.560

1900-01-01 873:16:49.867

1900-01-01 888:27:00.640

DateColumn2

-22063-05-18 00:00:00.000

-20285-02-03 00:00:00.000

In addtion there were some other columns that have had odd data in them(VERY WEIRD)

Char25Column

?Q307000
?Q307000
?Q307000

?Q307000

?Q307000

Any Thoughs?

Thanks

Could you send me an email so we can look into this a bit more? DonV@.microsoft.com.(nospam)

Don

|||

Have you run a DBCC CHECKDB lately? You might have some issue with your hardware, storage, or perhaps some 3rd party filter that's somehow modifying your data.

What version of SQL Server is this? If it is SQL Server 2005 hopefully you have database page checksums enabled so that if something does scribble on database pages, then the changes would be detected the next time you read those pages.

Do you have any XPs (extended stored procedures)? A poorly written/tested XP might scribble across SQL Server's memory.

Check your overall system and consider a call to product support. Also, you probably want to check your recent backups to see if the errors occur there as well.

Don

|||

Hi Don, thanks for the quick reply,to answer your question, i have ran DBCC table checks on the table in question and as expected consistancy error were found.( i would have posted them before but I tried to make the post a readable as possible.) (i'll post them below) the odd thing here is the fact that SQL allowed the corrupted data to be inserted in the first place. i mean you would think that SQL servver would throw up an error or two.

As far as the extended sp yes but they wouldnt have been called during the this data input.(btw was push in via informatic) then again reguardless of the client thats pushing and puling the data, SQL server should have had the last called as to what data got commited.

Here are the dbcc table check results( i have ommited a lot of the extract error that were repeated /per row)

DBCC results for 'tLoadedTable'.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidAmt" value is out of range for data type "decimal". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "InvoiceDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "LoadDate" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Table error: object ID 1762977507, index ID 0, partition ID 72057594677035008. A row should be on partition number 1 but was found in partition number 10. Possible extra or invalid keys for:

Msg 8988, Level 16, State 1, Line 1

Row (85:10:0) identified by (HEAP RID = (85:10:0)).

Too many errors found (201) for object ID 1762977507. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".

There are 133508601 rows in 9102393 pages for object "tLoadedTable".

CHECKTABLE found 0 allocation errors and 336633 consistency errors in table 'tLoadedTable' (object ID 1762977507).

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

Thanks

|||

Im running SQL server 2005 Enterprise SP 2, in addtion this has also happened on another database on a different server also running SQL 2005 SP 2 on a different SAN ( same client though)

Thanks

|||

Don, sorry I missed your question about the page verify mode, currently i running all databases in torn_page_verify mode, were mostly a OLAP shop and dont have a lot of rouge client make data modifications. though based on this little bout i may be changing it to page checksums ( though there seems to be an considerable overhead due to the constaint checksum verifications right? )

Thanks

|||

Since DBCC has detected over 360,000 anomalous data values it's quite likely that you have hardware/storage issues. I suggest you get that looked at before you make any further changes to the data.

It may be that you have a wayward XP that's scribbling outside its intended address space. An XP can write anywhere in SQL Server's writable memory, so even if an XP wasn't supposed to be writing on these pages, it might be doing so accidentally. Of course, a well-written and well-tested XP should do this, but it could have a bug or two that's pointing it in the wrong direction. There's nothing that SQL Server can do to keep your XP from scribbling where it shouldn't, other than replacing XPs with CLR modules as we've enabled with SS2005.

At least page checksums can detect that what was read is not the same as what was written. In your case it would detect these issues if they were caused by the IO system. Torn-page detection might detect an IO problem here, but only if the IO system happened to flip some bits near the torn-page bits on a page (there are only 32 of these bits on an 8K page, so the IO problem could well miss those).

And yes, there is some overhead for the page checksums, but you also would have detected this issue much sooner (if indeed it is an IO issue). You'd have to do some tests to see what the overhead would be for your system; it's hard to predict as it depends on cache hit probabilities and other factors .. but often people find it to be less than they expect .. or they're willing to "pay the price" for it since it will detect issues with the IO/storage system.

So: I suggest you investigate your IO system for issues (check out sqliosim and use it to test your system), and that you double- triple check any XPs to make sure they aren't generating those strings of bytes that you're finding in your dates and decimals (check for any strings/varchars as well .. perhaps it's writing a string of readable characters that you'll be able to use to track down the source of the issues).

Don

|||

Hi Don,

Again thanks for the reply.

As far as IO error there were none, ( funny you'd mentioned the sqliosim utility i had ran a check earlier in the week with no errors found) another thing is that this issue is occurring on two separate servers that also reside on separate Sans, oddly the issue occurred on the exact same partition boundary. as it sits I have a trace running on the entire process and found no evidence that any xp or clr routines were being called. ( just the simple sp_prepare and execute calls for table collation definition information. later followed with a API insert bulk process" that shows no errors during the loading)

Well I have been able to re-produce the issue yet its very weird.

Ok, so if I hadn’t mentioned it before the table in question is partitioned. its partition scheme is also shared by other table ( yet they don’t have the same issue.) the data load is being pushed in via infromatioca API insert bulk call. regardless of which partitioned key is used is still allows the records to be entered yet with a dbcc checktable or simple select is performed on the table it fails and returns the above error.

--

I attempting to loading to other partitioned boundaries and some succeeded with no corruption other succeeded with corruption. the thing that really bothers me is that SQL is allowing this errors to be committed. I have checked the input data and the errors are not there so SQL server has to be corrupting them when the insert bulk block is read into memory.(yet again you would think SQL server would be checking it here)

--test 1

I created another table with the same structure with in the same database but did not add it to a partition scheme( just left it on the primary file group( rather PrimaryData file group, I keep the system objects and user object separate) I ran the same ETL loading process into this new table with no errors and the data was clean( I was able to select against it with no issues and the data look good as expected.)

--test 2

I created another database on the built the same table with partitioned scheme and function within it ( so basically the environment is exactly the same as production minus all other table and the extra data within the partitioned table.)

I ran the same ETL load process against the new database... and it loaded fine, no error no corruption.. ughhh!!!

-- additional

I have ran dbcc dbcheck numerous time yet no errors are even returned. i have placed a call with MS but they seem to think its the third party tool that causing the issue ( though I’m not convinced due to I’m able to load into a non-partitioned table and a partitioned table another database on the same server same disk with no issues.)

YIKES!!!!

Thanks

|||

Yes, it does seem very odd. It would seem that even though a 3rd party API was being used, it shouldn't be able to insert bad values into a datatype. That's why I suspect either an IO issue or an XP issue since the bytes seem be be changed outside of SQL Server's actual control.

I suggest you pursue this some more with MS support together with the 3rd party .. perhaps the 3rd party API is using some XP of its own (some XPs are named sp_xxx, so that can seem like no XPs are being called if you're expecting xp_xxx).

Don

|||

Hi Don, Thanks again for your reply,

I have had my SAN admin review the SAN log to look for any kind latency or stale IO issues ( so far he has been able to find anything. I my self haven’t found anything at the OS or SQL level that you suggest IO or memory issues...) very odd. I have though been able to find out that the insert bulk API that the third party vendor had been using was indeed a odbc call to the bcp.exe, funny though i have since the data being submitted ( I’ve captured the network packets to review the data that was being sent to the bcp process, all is well so the corrupted data is not coming from the third party tool ( potentially its could be the memory block that the odbc driver has consumed when the connection was made to the api. perhaps sql is indeed seeing the correct data value verifying that they are compliant and during the commit process of writing the data to physical disk the memory block is being corrupted by some low level IO driver filter.

In addition after reviewing the trace( I know not all events are trapped by SQL server) again the only operations that are called were the sp_prepare and sp_execute which were unprepared before the api called was made. also in reviewing the third party tools model it show no other sql server functions are called ( just internal application calls)

One other thing was weird, the fact that I was able to load into a non-partitioned table with the same load process (third party tool) as well as another database partitioned table (same scheme, same function, same disk, same server) without data corruption.

Who knows? I’m still perusing the MS avenue and will post my findings( so far it my been a MS bug but there escalation team isn’t in until Monday.)

Meanwhile I have been attempting to read up on driver filters (interesting enough they can modify or FILTER data prior to persisting the data ( i guess it happens in the transfer process from memory to bus to disk.)

Though if anyone has any thought please do tell...

Thanks

Odd date data found in table

Hi all, this is REALLY weird, I can t seem to make heads or tails of it. but from my understanding the each datatype has set contrstraint assigned to it ( example int datatype can only except non-decimal numeric values ) as well as a datetime datatype can only except a vaild date.

Though oddly i have found the following dates in a table ( in addtion the when aby kind of data operation is performed on the table sql returns the following error:

Server: Msg 8630, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor encountered an unexpected error during execution.

Here are some of the dates that were present in the table:

DateColumn1

--

1900-01-01 857:44:45.813

1900-01-01 872:51:16.427

1900-01-01 872:54:57.440

1900-01-01 873:09:32.107

1900-01-01 873:13:10.560

1900-01-01 873:16:49.867

1900-01-01 888:27:00.640

DateColumn2

-22063-05-18 00:00:00.000

-20285-02-03 00:00:00.000

In addtion there were some other columns that have had odd data in them(VERY WEIRD)

Char25Column

?Q307000
?Q307000
?Q307000

?Q307000

?Q307000

Any Thoughs?

Thanks

Have you run a DBCC CHECKDB lately? You might have some issue with your hardware, storage, or perhaps some 3rd party filter that's somehow modifying your data.

What version of SQL Server is this? If it is SQL Server 2005 hopefully you have database page checksums enabled so that if something does scribble on database pages, then the changes would be detected the next time you read those pages.

Do you have any XPs (extended stored procedures)? A poorly written/tested XP might scribble across SQL Server's memory.

Check your overall system and consider a call to product support. Also, you probably want to check your recent backups to see if the errors occur there as well.

Don

|||

Hi Don, thanks for the quick reply,to answer your question, i have ran DBCC table checks on the table in question and as expected consistancy error were found.( i would have posted them before but I tried to make the post a readable as possible.) (i'll post them below) the odd thing here is the fact that SQL allowed the corrupted data to be inserted in the first place. i mean you would think that SQL servver would throw up an error or two.

As far as the extended sp yes but they wouldnt have been called during the this data input.(btw was push in via informatic) then again reguardless of the client thats pushing and puling the data, SQL server should have had the last called as to what data got commited.

Here are the dbcc table check results( i have ommited a lot of the extract error that were repeated /per row)

DBCC results for 'tLoadedTable'.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidAmt" value is out of range for data type "decimal". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "PaidDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "InvoiceDt" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Page (85:10), slot 0 in object ID 1762977507, index ID 0, partition ID 72057594677035008, alloc unit ID 72057594649313280 (type "In-row data"). Column "LoadDate" value is out of range for data type "datetime". Update column to a legal value.

Msg 2570, Level 16, State 3, Line 1

Table error: object ID 1762977507, index ID 0, partition ID 72057594677035008. A row should be on partition number 1 but was found in partition number 10. Possible extra or invalid keys for:

Msg 8988, Level 16, State 1, Line 1

Row (85:10:0) identified by (HEAP RID = (85:10:0)).

Too many errors found (201) for object ID 1762977507. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".

There are 133508601 rows in 9102393 pages for object "tLoadedTable".

CHECKTABLE found 0 allocation errors and 336633 consistency errors in table 'tLoadedTable' (object ID 1762977507).

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

Thanks

|||

Im running SQL server 2005 Enterprise SP 2, in addtion this has also happened on another database on a different server also running SQL 2005 SP 2 on a different SAN ( same client though)

Thanks

|||

Don, sorry I missed your question about the page verify mode, currently i running all databases in torn_page_verify mode, were mostly a OLAP shop and dont have a lot of rouge client make data modifications. though based on this little bout i may be changing it to page checksums ( though there seems to be an considerable overhead due to the constaint checksum verifications right? )

Thanks

|||

Since DBCC has detected over 360,000 anomalous data values it's quite likely that you have hardware/storage issues. I suggest you get that looked at before you make any further changes to the data.

It may be that you have a wayward XP that's scribbling outside its intended address space. An XP can write anywhere in SQL Server's writable memory, so even if an XP wasn't supposed to be writing on these pages, it might be doing so accidentally. Of course, a well-written and well-tested XP should do this, but it could have a bug or two that's pointing it in the wrong direction. There's nothing that SQL Server can do to keep your XP from scribbling where it shouldn't, other than replacing XPs with CLR modules as we've enabled with SS2005.

At least page checksums can detect that what was read is not the same as what was written. In your case it would detect these issues if they were caused by the IO system. Torn-page detection might detect an IO problem here, but only if the IO system happened to flip some bits near the torn-page bits on a page (there are only 32 of these bits on an 8K page, so the IO problem could well miss those).

And yes, there is some overhead for the page checksums, but you also would have detected this issue much sooner (if indeed it is an IO issue). You'd have to do some tests to see what the overhead would be for your system; it's hard to predict as it depends on cache hit probabilities and other factors .. but often people find it to be less than they expect .. or they're willing to "pay the price" for it since it will detect issues with the IO/storage system.

So: I suggest you investigate your IO system for issues (check out sqliosim and use it to test your system), and that you double- triple check any XPs to make sure they aren't generating those strings of bytes that you're finding in your dates and decimals (check for any strings/varchars as well .. perhaps it's writing a string of readable characters that you'll be able to use to track down the source of the issues).

Don

|||

Hi Don,

Again thanks for the reply.

As far as IO error there were none, ( funny you'd mentioned the sqliosim utility i had ran a check earlier in the week with no errors found) another thing is that this issue is occurring on two separate servers that also reside on separate Sans, oddly the issue occurred on the exact same partition boundary. as it sits I have a trace running on the entire process and found no evidence that any xp or clr routines were being called. ( just the simple sp_prepare and execute calls for table collation definition information. later followed with a API insert bulk process" that shows no errors during the loading)

Well I have been able to re-produce the issue yet its very weird.

Ok, so if I hadn’t mentioned it before the table in question is partitioned. its partition scheme is also shared by other table ( yet they don’t have the same issue.) the data load is being pushed in via infromatioca API insert bulk call. regardless of which partitioned key is used is still allows the records to be entered yet with a dbcc checktable or simple select is performed on the table it fails and returns the above error.

--

I attempting to loading to other partitioned boundaries and some succeeded with no corruption other succeeded with corruption. the thing that really bothers me is that SQL is allowing this errors to be committed. I have checked the input data and the errors are not there so SQL server has to be corrupting them when the insert bulk block is read into memory.(yet again you would think SQL server would be checking it here)

--test 1

I created another table with the same structure with in the same database but did not add it to a partition scheme( just left it on the primary file group( rather PrimaryData file group, I keep the system objects and user object separate) I ran the same ETL loading process into this new table with no errors and the data was clean( I was able to select against it with no issues and the data look good as expected.)

--test 2

I created another database on the built the same table with partitioned scheme and function within it ( so basically the environment is exactly the same as production minus all other table and the extra data within the partitioned table.)

I ran the same ETL load process against the new database... and it loaded fine, no error no corruption.. ughhh!!!

-- additional

I have ran dbcc dbcheck numerous time yet no errors are even returned. i have placed a call with MS but they seem to think its the third party tool that causing the issue ( though I’m not convinced due to I’m able to load into a non-partitioned table and a partitioned table another database on the same server same disk with no issues.)

YIKES!!!!

Thanks

|||

Yes, it does seem very odd. It would seem that even though a 3rd party API was being used, it shouldn't be able to insert bad values into a datatype. That's why I suspect either an IO issue or an XP issue since the bytes seem be be changed outside of SQL Server's actual control.

I suggest you pursue this some more with MS support together with the 3rd party .. perhaps the 3rd party API is using some XP of its own (some XPs are named sp_xxx, so that can seem like no XPs are being called if you're expecting xp_xxx).

Don

|||

Hi Don, Thanks again for your reply,

I have had my SAN admin review the SAN log to look for any kind latency or stale IO issues ( so far he has been able to find anything. I my self haven’t found anything at the OS or SQL level that you suggest IO or memory issues...) very odd. I have though been able to find out that the insert bulk API that the third party vendor had been using was indeed a odbc call to the bcp.exe, funny though i have since the data being submitted ( I’ve captured the network packets to review the data that was being sent to the bcp process, all is well so the corrupted data is not coming from the third party tool ( potentially its could be the memory block that the odbc driver has consumed when the connection was made to the api. perhaps sql is indeed seeing the correct data value verifying that they are compliant and during the commit process of writing the data to physical disk the memory block is being corrupted by some low level IO driver filter.

In addition after reviewing the trace( I know not all events are trapped by SQL server) again the only operations that are called were the sp_prepare and sp_execute which were unprepared before the api called was made. also in reviewing the third party tools model it show no other sql server functions are called ( just internal application calls)

One other thing was weird, the fact that I was able to load into a non-partitioned table with the same load process (third party tool) as well as another database partitioned table (same scheme, same function, same disk, same server) without data corruption.

Who knows? I’m still perusing the MS avenue and will post my findings( so far it my been a MS bug but there escalation team isn’t in until Monday.)

Meanwhile I have been attempting to read up on driver filters (interesting enough they can modify or FILTER data prior to persisting the data ( i guess it happens in the transfer process from memory to bus to disk.)

Though if anyone has any thought please do tell...

Thanks

|||

Could you send me an email so we can look into this a bit more? DonV@.microsoft.com.(nospam)

Don