Problem Note 62503: An error is returned when you try to add datetime values to an SQL Server table using a libref with SAS/ACCESS® Interface to OLE DB
 |  |  |  |  |
When you try to add datetime values to a Microsoft SQL Server table using SAS/ACCESS Interface to OLE DB, the action fails with an error similar to the following:
ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.
This problem occurs under the following conditions:
- You use two OLE DB librefs connected to the same SQL Server database.
- You create a table in SQL Server with a datetime value.
- You add more datetime values into the same SQL Server table using a different libref.
Example
For example, you might encounter this problem if you submit code similar to the following:
libname x oledb provider=sqlncli11 datasource=marlin user=sasdemo pwd=Pass123;
libname y oledb provider=SQLOLEDB datasource=marlin user=sasdemo pwd=Pass123;
* libref y refers to different provider from libref x ;
libname y oledb provider=SQLOLEDB datasource=marlin user=sasdemo pwd=Pass123;
* libref y refers to different provider from libref x ;
data work.foo;
format dttmcol datetime.;
dttmcol='01JAN2018 11:23'DT;
run;
format dttmcol datetime.;
dttmcol='01JAN2018 11:23'DT;
run;
proc sql;
drop table x.demotest;
create table x.demotest as select * from work.foo;
* CREATE TABLE "demotest" ("dttmcol" datetime2(0));
insert into x.demotest values('21JUN2018 12:34'DT);
* OK;
insert into y.demotest values('22JUN2018 12:34'DT);
/* ERROR: Value 1 of VALUES clause 1 does not match the data type of the corresponding column in the
object-item list (in the SELECT clause). ; */
quit;
drop table x.demotest;
create table x.demotest as select * from work.foo;
* CREATE TABLE "demotest" ("dttmcol" datetime2(0));
insert into x.demotest values('21JUN2018 12:34'DT);
* OK;
insert into y.demotest values('22JUN2018 12:34'DT);
/* ERROR: Value 1 of VALUES clause 1 does not match the data type of the corresponding column in the
object-item list (in the SELECT clause). ; */
quit;
Background and Cause
The SQL Server database has several providers, including the following:
- SQL Server Native Client 11.0 (SQLNCLI11)
- SQL Server Native Client 10.0 (SQLNCLI10)
- Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
SQLNCLI11 and SQLNCLI10 support the new DATETIME2 data type for SQL Server 2008 and later. But, SQLOLEDB does not recognize the DATETIME2 data type.
In an effort to save some time during connections, SAS attempts to save and reuse data-type information if multiple librefs are connected to the same data source. When two librefs are connected to the same data source, data-source information is shared across the librefs.
The issue occurs because a data type that is supported by one provider might not be supported by other providers for the same database server. Because the shared data-source information could be used by a provider that does not support it, SAS returns the error message.
As a workaround, use the SQL Server Native Client 11 provider instead of the Microsoft OLE DB Provider for SQL Server for all librefs.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to OLE DB | Microsoft® Windows® for x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 |
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8 Pro 32-bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8 Pro x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows 10 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2008 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2008 R2 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2008 for x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2012 Datacenter | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Microsoft Windows Server 2012 Std | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Enterprise 32 bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Enterprise x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Home Premium 32 bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Home Premium x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Professional 32 bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Professional x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Ultimate 32 bit | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
| Windows 7 Ultimate x64 | 9.4_M4 | 9.4_M5 | 9.4 TS1M4 | 9.4 TS1M5 | ||
A fix for this issue for SAS/ACCESS Interface to OLE DB 9.4_M4 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/C9F.html#62503
When you add a datetime value into SQL Server using an OLE DB libref, you might receive the following error: "Failed with ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name."
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2018-07-12 09:33:53 |
| Date Created: | 2018-06-22 14:20:10 |