SAS/ACCESS® Interface to Microsoft SQL Server might return a "Datetime field overflow..." error during bulk load


You can enable bulk load in the SAS/ACCESS Interface to Microsoft SQL Server engine by setting the following driver option.

On UNIX systems, edit the odbc.ini file, and set (or change if the values exist already) the following in the corresponding ODBC DSN:

Enablebulkload=1


On Microsoft Windows systems, open the ODBC Administrator and select Configure for the ODBC DSN entry. On the Bulk tab, select Enable Bulk Load:

enable bulk load.png


If this option is set and you have the datetime data type format in your SAS data set, an error similar to the following might occur when loading data into Microsoft SQL Server:

ERROR: During insert: [SAS][ODBC SQL Server Wire Protocol driver]Datetime
       field overflow. Error in parameter 1. : [SAS][ODBC SQL Server Wire
       Protocol driver]Operation cancelled. Error in parameter 2.


To work around this issue when bulk load is enabled, set the following driver option.

On UNIX systems, edit the odbc.ini file, and set (or change if the values exists already) the following in the corresponding ODBC DSN:

TimestampTruncationBehavior=1


On Windows systems, open the ODBC Administrator and select Configure for the ODBC DSN entry. On the Advanced tab, add the following in the Extended Options field:

extended options.png