| This post was kindly contributed by Heuristic Andrew - go there to comment and to read the full post. |
SAS 9.4 (TS1M2) crashes hard when using BULKLOAD=YES with the latest Microsoft ODBC Driver 11 for SQL Server. For a brief moment you may see in the SAS log ERROR: BCP initialize error: [Microsoft][ODBC Driver 11 for SQL Server]Connection is not enabled for BCP, and then the SAS window closes.
The Windows Event Viewer has the following information:
Faulting application name: sas.exe, version: 9402.0.21456.21239, time stamp: 0x53d05339
Faulting module name: ntdll.dll, version: 6.1.7601.18229, time stamp: 0x51fb1072
Exception code: 0xc0000374
Fault offset: 0x000ce753
Faulting process id: 0x5724
Faulting application start time: 0x01d004d90519e3fc
Faulting application path: C:Program FilesSASHome2x86SASFoundation9.4sas.exe
Faulting module path: C:WindowsSysWOW64ntdll.dll
Report Id: 4c84f206-70cc-11e4-bd42-0205857feb80
SAS Support acknowledged the crash and will address it in the next maintenance release. At this time I do not see any KB article.
If you upload large data sets from SAS to SQL Server, make sure to try either BULKLOAD or INSERTBUFF. The performance improvement can be dramatic.
Until the maintenance release, SAS suggested using larger values of READBUFF instead of BULKLOAD. In a few simple tests, larger values of READBUFF are much better than the default READBUFF=1, but they are slower than BULKLOAD=YES. Also, huge values of READBUFF are not better, and it may depend on the workload.
Microsoft Windows 7 comes with a SQL Server driver version 6, which is from 1996. The new driver is useful to use date-only columns. With the old driver, date-only columns are converted to character format (see SAS note 40080).
Both SQL Server drivers can be installed and use simultaneously using different connection strings, so one option is bulkloading using the old driver (for data sets without date-only variables).
This post first appeared on Heuristic Andrew.
| This post was kindly contributed by Heuristic Andrew - go there to comment and to read the full post. |