Problem Note 17331: "ERROR: Invalid Character Value for Cast Specification" with SAS/ACCESS Interface to OLE DB and SQL Server 2005
 |  |  |  |  |
In SQL Server, a data type of datetime stores date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds. A data type of smalldatetime stores date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types.
When a datetime value is exported to SQL Server 2005 Enterprise edition, the data type being chosen is smalldatetime instead of datetime. This could cause errors if the year portion of the datetime value is greater than 2079. This problem doesn't occur if the datetime value is exported to SQL Server 2000 Enterprise Edition.
One workaround may be to use the DBTYPE= data set option. For example:
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to OLE DB | Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | 9.2 TS1M0 |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows XP Professional | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | 9.2 TS1M0 | ||
A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:
http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#017331A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :
http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#017331| Type: | Problem Note |
| Priority: | medium |
| Topic: | SAS Reference ==> LIBNAME Engines Data Management ==> Data Sources ==> External Databases ==> OLEDB Third Party ==> Information Exchange ==> OLE DB (Object Linking and Embedding Database) |
| Date Modified: | 2006-05-31 10:47:22 |
| Date Created: | 2006-03-23 10:19:33 |