Can SAS import SQL Server TIME columns using the ODBC engine?

[ScottBass]

tl;dr: 

I'm getting this error when trying to import SQL Server TIME(0) columns:

ERROR: CLI cursor fetch error: [Microsoft][SQL Server Native Client 10.0]Numeric value out of range

I note this previous post on this topic:

https://communities.sas.com/t5/General-SAS-Programming/CLI-cursor-fetch-error-Microsoft-SQL-Server-N...

Details:

My environment:

SAS 9.3

SQL Server 2016

SAS/Access ODBC engine

I've run this in SQL Server Management Studio to create a test table:

DROP TABLE IF EXISTS tmp.deleteme

SELECT CAST(GETDATE() AS DATETIME2(0)) AS [datetime]
      ,CAST(GETDATE() AS DATE)         AS [date]
      ,CAST(GETDATE() AS TIME(0))      AS [time0]
      ,CAST(GETDATE() AS TIME(7))      AS [time7]
  INTO tmp.deleteme

Here is my SAS libname statement:

27         %libname_sqlsvr(libref=TMP,server=MYSERVER,port=,database=MYDB,schema=tmp)
=================================================================================
LIBREF:           TMP
CONNECT:          NOPROMPT="Driver={SQL Server Native Client 
10.0};Server=MYSERVER;Database=MYDB;Trusted_Connection=yes;"
INTERNAL OPTIONS: schema=tmp bulkload=yes dbcommit=100000 direct_exe=delete preserve_names=yes
USER OPTIONS:
=================================================================================

Here are other SAS options (many are irrelevant to this issue but here they are anyway):

* set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options dbidirectexec;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
options lognumberformat=1;

When I run this code:

data test1;
   set tmp.deleteme (keep=date:);
run;

proc sql;
   connect using tmp;
   create table test2 as
   select *
   from connection to tmp (
SELECT datetime, date from tmp.deleteme
   );
quit;

data test3;
   set tmp.deleteme (keep=time:);
run;

proc sql;
   connect using tmp;
   create table test4 as
   select *
   from connection to tmp (
SELECT time0, time7 from tmp.deleteme
   );
quit;

tests 3 & 4 throws the above error message.

If SQL Server stores time values differently than SAS, shouldn't SAS's ODBC engine handle this issue?

I really want to store the data in the correct format in SQL Server, i.e. TIME(0) for time columns.

I really want to just treat the SQL Server table (or view) like a "native" SAS data set.  (My end users will want that too).

One approach I can think of is to 1) create a view in SQL Server that casts the time values to varchar, and 2) create a view in SAS that converts the character strings to SAS time values.

But that is 1) messy, 2) won't perform as well, and 3) redundant (?) - shouldn't SAS deal with this???

[ChrisNZ]

Is it time0 or time7 or both that generate the error? If time7, do you need nanosecond times?

[ScottBass]

---

@ChrisNZ wrote:
Is it time0 or time7 or both that generate the error? If time7, do you need nanosecond times?

---

Both generate the error.  time7 was only for testing, to see if it made a difference.  My actual data is time0.

This works...:

proc sql;
   connect using tmp;
   create view test as
   select datetime, date, input(time,time8.) as time format=time.
   from connection to tmp (
SELECT datetime, date, CAST(time0 AS VARCHAR(8)) AS time FROM tmp.deleteme
   );
quit;

... but I wish I could only embed the libname in the view somehow.  I need the explicit pass through for this to work, and I can't figure out how to embed a libname while using explicit pass through.

(I suppose I could create a view on SQL Server to cast to varchar, then a SAS view that references that SQL Server view.  A bit messy though...)

Edit:  That's what I've done.

On SQL Server:

Three views with the same derived columns for three similar tables.  Excerpt:

          -- workaround bug in SAS 9.3
         ,episode_end_time_sas =
          CAST(episode_end_time AS VARCHAR(8)) 

         ,episode_start_time_sas =
          CAST(episode_start_time AS VARCHAR(8)) 

Then a view (about 450 columns) which UNION ALL's the three table views into a single view of the three data assets.

Then in SAS:

data workspde.all_flat;
   * set PDV order (keep columns in alphabetical order) ;
   if 0 then set dmt.vwALL_FLAT (drop=episode_end_time_sas episode_start_time_sas);

   set dmt.vwALL_FLAT (drop=episode_end_time episode_start_time);  * <<<< these are the problem columns ;
   episode_end_time     = input(episode_end_time_sas,time.);
   episode_start_time   = input(episode_start_time_sas,time.);

   format episode_end_time episode_start_time time.;
   drop episode_end_time_sas episode_start_time_sas;
run;

If I had to do this often, I could save this as a SAS view in a permanent library somewhere...

[ScottBass · Accepted solution]

I also opened a ticket with SAS TS, and have gotten a reply.  So I'll answer my own question (with help from SAS TS).

It's a bug in SAS 9.3, fixed in SAS 9.4. 

So if you've found this via searching communities.sas.com, and are still on SAS 9.3, then you have my commiserations.

[ChrisNZ]

>If SQL Server stores time values differently than SAS, shouldn't SAS's ODBC engine handle this issue?

I think SAS receives whatever is send from the ODBC source, and falls over of the value is illegitimate.

Same if say a string was send for a number field.

I am curious what is sent back for SAS to fail.

Also the driver is probably Microsoft's; each vendor usually provides the driver to their data source.

>if I could only embed the libname in the view somehow. 

I think proc access does that, but you'd need the native access product. ODBC is not supported by proc access.

[ScottBass]

---

@ChrisNZ wrote:

>If SQL Server stores time values differently than SAS, shouldn't SAS's ODBC engine handle this issue?

I think SAS receives whatever is send from the ODBC source, and falls over of the value is illegitimate.

Same if say a string was send for a number field.

I am curious what is sent back for SAS to fail.

Also the driver is probably Microsoft's; each vendor usually provides the driver to their data source.

I'm not in R&D so this is just conjecture...

I think SAS receives whatever is send from the ODBC source

Sure, I get that SAS is using an ODBC API provided by Microsoft, but IMO the SAS ODBC engine should be able to post-process those results as required before returning the results to SAS.

In any case, IMO broken in SAS 9.3, fixed in SAS 9.4 == bug in SAS 9.3.

[ChrisNZ]

>IMO the SAS ODBC engine should be able to post-process those results as required before returning the results to SAS.

It could be argued both ways.

If SAS was hiding data issues, one could complain that they want to know about them.

An option to alter the default behaviour could make everybody happy.

In this case though,

ERROR: CLI cursor fetch error: [Microsoft][SQL Server Native Client 10.0]Numeric value out of range

looks like an ODBC error, so SAS doesn't even get the data. 

>In any case, IMO broken in SAS 9.3, fixed in SAS 9.4 == bug in SAS 9.3.

True.

I wonder what is fixed then, if the error happens with the driver.

Maybe just updating the driver would be enough?

[SimonDawson]

The reason this breaks is down to a mismatch between the amount of bytes allocated compared with what is needed. Its not well documented in the Microsoft reference manual how many bytes are needed and in SAS 9.3 there weren't enough allocated. We found how much must be allocated to have this work correctly in SAS 9.4 and its fixed there.

The details eventually did get documented.