Issue with SAS/ACCESS Interface to Microsoft SQL Server and datetime fields

[rpellicaan]

Hi,

We are migrating from SAS on Windows to SAS on AIX, and therefor from OLEDB to SAS/ACCESS Interface to Microsoft SQL Server.

We now have a an issue with an update statement to datetime / datetime2 fields, that does not occur when using OLEDB.

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Operand type clash: numeric is incompatible with datetime2

Why does SAS correctly translates the variable sysProcesDatum to a datetime string in the WHERE clause, but not in the SET ?

Is there a generic solution to fix this? preferably without changing any code.

LIBNAME BI SQLSVR  Datasrc=BI3  SCHEMA=dbo datetime2=yes ;

OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT ;

%LET sysProcesDatum=1901461843.9547;
%PUT %sysfunc(putn(&SYSPROCESDATUM, datetime20)); 

PROC SQL ;
         UPDATE BI.TEST
		 SET EDWH_procesEinde_DTD = &sysProcesDatum.
         WHERE EDWH_procesStart_DTD = &sysProcesDatum.
		 ;
QUIT ;

Table definition:

CREATE TABLE [dbo].[TEST](	
	[EDWH_procesStart_DTD] [datetime] NULL,
	[EDWH_procesEinde_DTD] [datetime2](3) NULL	
) ON [PRIMARY]
GO

Log:

33         %LET sysProcesDatum=1901461843.9547;
34         %PUT %sysfunc(putn(&SYSPROCESDATUM, datetime20));
SYMBOLGEN:  Macro variable SYSPROCESDATUM resolves to 1901461843.9547
2                                                          The SAS System                              15:50 Thursday, April 2, 2020

02APR2020:15:50:44
35         


36         PROC SQL ;
37                  UPDATE BI.TEST
38         		 SET EDWH_procesEinde_DTD = &sysProcesDatum.
SYMBOLGEN:  Macro variable SYSPROCESDATUM resolves to 1901461843.9547
39                  WHERE EDWH_procesStart_DTD = &sysProcesDatum.
SYMBOLGEN:  Macro variable SYSPROCESDATUM resolves to 1901461843.9547
40         		 ;
SQLSRV: AUTOCOMMIT is NO for connection 2
SQLSRV: AUTOCOMMIT turned ON for connection id 2
 
SQLSRV_280: Prepared: on connection 2
SELECT * FROM "dbo"."TEST"
 
SQLSRV: AUTOCOMMIT is NO for connection 3
SQLSRV: AUTOCOMMIT turned ON for connection id 3
 
SQLSRV_281: Executed: on connection 3
update "dbo"."TEST" set "dbo"."TEST"."EDWH_procesEinde_DTD" = 1901461843.9547 where "dbo"."TEST"."EDWH_procesStart_DTD" =  CAST 
('2020-04-02T15:50:43.9547000' AS DATETIME2)
 
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Operand type clash: numeric is 
       incompatible with datetime2
WARNING: This SQL statement is not allowed to be passed directly to the DBMS for processing because the DBIDIRECTEXEC system option 
         is not turned on by the user or is temporarily turned off by Proc SQL.
SQLSRV: AUTOCOMMIT is NO for connection 2
SQLSRV: COMMIT performed on connection 2.
 
SQLSRV_282: Prepared: on connection 2
SELECT * FROM "dbo"."TEST"
 
 
SQLSRV_283: Prepared: on connection 2
SELECT  "EDWH_procesStart_DTD", "EDWH_procesEinde_DTD"  FROM "dbo"."TEST"  WHERE  ( "EDWH_procesStart_DTD" = CAST 
('2020-04-02T15:50:43.9547000' AS DATETIME2) ) FOR UPDATE OF  "EDWH_procesStart_DTD", "EDWH_procesEinde_DTD"
 
 
SQLSRV_284: Executed: on connection 2
Prepared statement SQLSRV_283
 
SQLSRV: COMMIT performed on connection 2.
NOTE: No rows were updated in BI.TEST.

SQLSRV: COMMIT performed on connection 2.
SQLSRV: COMMIT performed on connection 2.
41         QUIT ;

[Quentin]

One option is to avoid the implicit pass-through and skip the problem by writing the SQL yourself.

That said, I'm curious what happens if you make them both datetime2, i.e.:

---

---

CREATE TABLE [dbo].[TEST](	
	[EDWH_procesStart_DTD] [datetime2](3) NULL,
	[EDWH_procesEinde_DTD] [datetime2](3) NULL	
) ON [PRIMARY]
GO

 From the results you showed, it's not clear if the difference is the SET statement vs WHERE statement, or if it's datetime vs datetime2.

[rpellicaan]

Hi Quentin,

Thank you for your quick response. Changing all fields to datetime2 unfortunatly does not make difference, as we had that situation in the original table (I introduced the datetime field in this test-table to see if there would be a difference between datetime and datetime2, but there is no difference). It also does not matter whether i create the table on a SQL2012 server or SQL2016 server.

[Quentin]

Gotcha.  So the WHERE clause is recognizing that the value needs to be converted a date-time, and it does so (via CAST).  But the SET clause does not CAST it as a date-time.  Interesting.  

Like I said, I generally avoid implicit pass-through.  But certainly it seems fair to me to expect your code to work.

If you don't get an answer here, I'd send it in to tech support.

[rpellicaan]

Hi Quentin,

The code below works, thank you. Anything in there I could improve?  Is there a way to skip the DSN="BI2" and use the library defintion from the metadata?

And is there an easier way to cast the sysProcesDatum to a sql compatible datestring?

LIBNAME BI SQLSVR  Datasrc=BI2  SCHEMA=dbo datetime2=yes ;

OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT ;

%LET sysProcesDatum=1901461843.9547;
%let sysProcesDatum2=%str(%')%sysfunc(putn(&SYSPROCESDATUM, e8601dt))%str(%'); 
%put &=sysProcesDatum2;

proc sql;
 CONNECT TO SQLSVR (DSN="BI2"); * Pass-Through Facility; 
 execute(
   UPDATE TEST
		 SET EDWH_procesStart_DTD = &sysProcesDatum2.,
		 	EDWH_procesEinde_DTD = &sysProcesDatum2.
         WHERE EDWH_procesStart_DTD = &sysProcesDatum2.
		 ;
		) by SQLSVR;
quit;

[Quentin]

You can do:

connect using BI ; /*i.e. connect using <libref>; */

to connect to the previously defined library BI.

I'm not a huge SQL server person. For one job I have, it doesn't mind double quotes, so I can do stuff like (untested):

LIBNAME BI SQLSVR  Datasrc=BI2  SCHEMA=dbo datetime2=yes ;

OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT ;

%LET sysProcesDatum=1901461843.9547;

proc sql;
 CONNECT using BI; * Pass-Through Facility; 
 execute(
   UPDATE TEST
	 SET EDWH_procesStart_DTD = "%sysfunc(putn(&SYSPROCESDATUM, e8601dt))",
	 EDWH_procesEinde_DTD = "%sysfunc(putn(&SYSPROCESDATUM, e8601dt))"
         WHERE EDWH_procesStart_DTD = "%sysfunc(putn(&SYSPROCESDATUM, e8601dt))"
		 ;
		) by SQLSVR;
quit;

But I think what you have looks good.  It's possible the acceptance of double quotes is configured somewhere in the database or ODBC connection.  I feel like it doesn't work for all databases I use, but I'm happy when it does.

[rpellicaan]

Thank you, again!

Unfortunatly, I was too soon with my conclusion. It seems I am missing additional precision; the update will update 0 rows when EDWH_procesStart_DTD = '2020-04-02 14:36:09.499', because the milliseconds (.499) are missing. Which format adds the additional milliseconds?

I believe you're right about the double quotes: https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/enable-quoted-identifie.... But I believe a collegue has enabled this setting to target other issues.

[Quentin]

I think e8601dt23.3

[rpellicaan]

e8601dt23.3 works, thank you!

[rpellicaan]

Update on this issue: We found out that the error message only occurs when the OPTION sastrace=',d' is used. Without this trace option, there is no error message. 

We have created a SAS track on this issue and other releated issues. SAS is investigating the issues.

Besides the error message, we found out that:

- SAS converts a SAS variable differently in an INSERT statement then in an UPDATE statement. This occurs with the SQLSVR engine, on SAS 9.4 M6 on AIX, with SQL datetime, datetime2(3) and datetime(7) datatypes. Only datetime(4) works as expected.

Code to reproduces these issues:

/*OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT sastrace='d,,,d' sastraceloc=saslog nostsuffix pagesize=max linesize=max;*/
OPTIONS DBIDIRECTEXEC NOSQLIPONEATTEMPT sastrace='d,' sastraceloc=saslog nostsuffix pagesize=max linesize=max;

%macro Dummy;
	%IF &SYSSCPL. EQ AIX %THEN
		%DO;
			LIBNAME BI SQLSVR Datasrc=BI2 SCHEMA=dbo datetime2=yes;
			%LET ENGINE=SQLSVR;
		%END;
	%ELSE
		%DO;
			LIBNAME BI OLEDB  BULKLOAD=YES  PROPERTIES=("Integrated Security"=SSPI "Persist Security Info"=True "Initial Catalog"=sas_bi)   DATASOURCE=DB_TST_SAS_BI  PROVIDER=SQLNCLI10.1  SCHEMA=dbo;
			%LET ENGINE=OLEDB;
		%END;

	PROC SQL;
		CONNECT using BI;

		* Pass-Through Facility;
		execute(
			drop table test;
		CREATE TABLE TEST(
			ID INT,
			DT_Start datetime NULL,
			DT_End datetime NULL,
			DT2_3_Start datetime2(3) NULL,
			DT2_3_End datetime2(3) NULL,
			DT2_4_Start datetime2(4) NULL,
			DT2_4_End datetime2(4) NULL,
			DT2_7_Start datetime2(7) NULL,
			DT2_7_End datetime2(7) NULL,
			) ) BY BI;
		;
	QUIT;

%MEND;

%Dummy;

%LET sysProcesDatum=1901461843.9547;
%let sysProcesDatum3=%str(%')%sysfunc(putn(&SYSPROCESDATUM, e8601dt23.3))%str(%');
%let sysProcesDatum4=%str(%')%sysfunc(putn(&SYSPROCESDATUM, e8601dt23.4))%str(%');
%put &=sysProcesDatum3;
%put &=sysProcesDatum4;

PROC SQL;
	DELETE FROM BI.TEST;
QUIT;

PROC SQL;
	INSERT INTO BI.TEST (id, DT_Start, DT2_3_Start, DT2_4_Start,DT2_7_Start)
		VALUES(1, &sysProcesDatum.,&sysProcesDatum.,&sysProcesDatum.,&sysProcesDatum.);
QUIT;

PROC SQL;
	SELECT * FROM BI.TEST;
QUIT;

PROC SQL;
	UPDATE BI.TEST
		SET DT_End = &sysProcesDatum.
			WHERE DT_Start = &sysProcesDatum.
				AND ID=1;
QUIT;

PROC SQL;
	SELECT * FROM BI.TEST;
QUIT;

PROC SQL;
	UPDATE BI.TEST
		SET DT2_3_End = &sysProcesDatum.
			WHERE DT2_3_Start = &sysProcesDatum.
				AND ID=1;
QUIT;

PROC SQL;
	SELECT * FROM BI.TEST;
QUIT;

PROC SQL;
	UPDATE BI.TEST
		SET DT2_4_End = &sysProcesDatum.
			WHERE DT2_4_Start = &sysProcesDatum.;
QUIT;

PROC SQL;
	SELECT * FROM BI.TEST;
QUIT;

PROC SQL;
	UPDATE BI.TEST
		SET DT2_7_End = &sysProcesDatum.
			WHERE DT2_7_Start = &sysProcesDatum.;
QUIT;

PROC SQL;
	SELECT * FROM BI.TEST;
QUIT;