Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

I am doing this

SELECT * INTO table1 FROM Table0

I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a datetime column and not a smalldatetime column..

share|improve this question
1  
Is table0 actually a view rather than a table? Otherwise can't think how that error message could occur from that query. – Martin Smith Jan 1 at 12:56
nope its a table.. – Rohan Jan 1 at 13:04
2  
Have you checked it for corruption with the data purity option? Any DDL triggers? – Martin Smith Jan 1 at 13:28
3  
Also any computed columns in the source table? – Martin Smith Jan 1 at 17:58
1  
Please edit the question to include the actual error message. – Jon Seigel Jan 1 at 21:32
show 3 more comments

2 Answers

up vote 6 down vote accepted

It sounds to me like you have an implicit conversion to smalldatetime, probably in a computed column. If you look at the Estimated Execution Plan, you'll probably see Compute Scalar operations in there. Hit F4 to see the properties, and you should be able to see the calculations that are being done, including any implicit conversions that are being used.

share|improve this answer
Yes there was a computed column.. Thanks all! – Rohan Jan 2 at 6:02
up vote 1 down vote

This kind of problem is always indicative of corruption and can easily be debugged with

DBCC CHECKTABLE(<tablename>) WITH DATA_PURITY;

DATA_PURITY
Causes DBCC CHECKTABLE to check the table for column values that are not valid or out-of-range. For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

share|improve this answer
Actually as was mentioned earlier, its a computed column that was causing the problem.. – Rohan Jan 2 at 8:59
The fact that the computed column causes an overflow is technically a corruption, and can be detected by the check. – Remus Rusanu Jan 2 at 9:07
1  
@RemusRusanu - Is this only for persisted computed columns? It doesn't report anything for CREATE TABLE T(Id int, X AS CAST('Fish' AS INT));INSERT INTO T DEFAULT VALUES;SELECT * INTO #T FROM T;DBCC CHECKTABLE(T) WITH DATA_PURITY; – Martin Smith Jan 2 at 9:18
@MartinSmith: you're right, data purity will only validate the storage format (ie persisted columns bits on disk). I was thrown aback by the very fact that you could create the computed column, obviously the DDL never evaluated the expression, but on second thought that is normal. Computed column usually depend on row data and during CREATE there are no rows, so it cannot be evaluated. During ALTER ... ADD... it could be evaluated, but there is no 'distinguished' row to try it on, so it would mean a size-of-data scan validation on all rows. – Remus Rusanu Jan 11 at 9:27
1  
Think of it as a feature to catch unhealthy use of SELECT * :) alter table t add [Thou Shall Not Use *] AS 1/0;. – Remus Rusanu Jan 11 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.