May 27, 2004 at 11:23 am
I have a client that has an Access Database that they are importing into SQL Server 7 for reporting. The problem they are running into is that there are columns in Access defined as smalldatetime that when they are being uploaded will cause a overflow error (SQL Server 7 columns defined as smalldatetime). They have changed the destination datatype to serveral different ones (datetime, varchar(100)) and they all get errors. Any ideas?
Thanks,
Kevin
May 27, 2004 at 12:04 pm
In the Access I meant the datatype of the columns are date/time
May 28, 2004 at 4:02 am
You need to transform the data before it is imported in by using an active x script.
In case you do not know, click on the transformation tab and select new. Choose ActiveX script. You can then format the date to your requirement by using FormatDateTime or stripping the year, month, day etc and building up a date sql server will recognise.
If you have any problems writing the code come back to me and I will put it together for you.
May 28, 2004 at 8:27 am
Your problem may also come from the fact that Access allows three digit years. The years in a SQL small date time are also limited to the 1900 forward. I often have to change to date time to include dates of birth in the 1800’s.
June 27, 2004 at 6:20 pm
Jonathan,
when I do the activex script, it looks like it is fine until a NULL value is encountered then I get errors about the paramters to the CSTR function.
Once again the Access database is part of a 3rd party program which we are trying to move the data to SQL 7 for reporting. The field is defined as a smalldatetime but when you view the data it on shows the hh:mm pm/am.
I have very little experience with Access.
Thanks,
Kevin
June 30, 2004 at 8:19 am
Try also using the IsNull function in the transformation.
From BOL...
Replaces NULL with the specified replacement value.
ISNULL ( check_expression , replacement_value )
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply