Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER CO
LUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>