topic Re: SQL-Server dates opening as text fields in Enterprise Guide in SAS Data Management

SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Thu, 24 Aug 2017 14:11:12 GMT

Issue: Opening an SQL table in Enterprise Guide displays SQL datetimes as SAS datetimes, but displays SQL dates as a SAS character field.

Products: SAS 9.4M3 (Win64), EG 7.1, SQL Server 2012 (via SAS/Acccess to ODBC)

There was a SAS note in 2010, dealing with SAS v9.1 & SQL Server 2008 - I'm hoping there's been an update since?

I've found workarounds here & elsewhere, but I don't want to have to play around with CAST() or INPUT() functions in code; I'd actually prefer to find the right tweak to the libname statement so that opening a SQL-Server table in EG displays SQL datetimes as SAS datetimes, and SQL dates as SQL dates.

Re: SQL-Server dates opening as text fields in Enterprise Guide

Reeza — Thu, 24 Aug 2017 15:47:49 GMT

Is DBSASTYPE an option here?

Re: SQL-Server dates opening as text fields in Enterprise Guide

SASKiwi — Fri, 25 Aug 2017 00:13:52 GMT

Which ODBC SQL Server driver are you using? We are currently using SQL Server Native Client 11.0 which handles dates better.

Re: SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Fri, 25 Aug 2017 13:15:16 GMT

@Reeza - not in this case. I'm trying to avoid data set options in favour of global options (or config settings).

To clarify the client's requirements: To be able to open a SQL-Server table in Enterprise Guide v7.1 and have SQL datetimes appear as datetimes and SQL dates appear as dates.

SAS 9.4M3 on a Win64 server.
libname to a SQL-Server 2012 database via ODBC driver.

@SASKiwi - I'll have to check with the SysAdmins but thanks for the tip.

Is the solution likely to be:

a SAS libname option
a SQL-Server client version
a SQL-Server driver update
a combination of the above?

Thanks.

Re: SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Tue, 29 Aug 2017 07:20:42 GMT

@Reeza, @SASKiwi - I'm struggling to find a "SAS configuration" resolution to this issue (without creating table-specific views, transformations, etc).

Need to make a judgement call on this by the end of the week.

How confident are you that updating the SQL Server driver will resolve this? Presumably there'd be nothing to lose by doing so?

Thanks.

Re: SQL-Server dates opening as text fields in Enterprise Guide

SASKiwi — Tue, 29 Aug 2017 21:49:17 GMT

@AndrewHowell - Correct. Good practice would be to have several ODBC drivers so behaviour differences can be checked. It is easy just to add another ODBC driver then test it using the connection string approach:

libname testodbc ODBC noprompt = "server=MySQLServer;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;"  qualifier = "MyDatabase" schema = "MySchema";

Re: SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Tue, 29 Aug 2017 22:35:15 GMT

Thanks, @SASKiwi - I've done that on Linux platforms, but I found Windows platforms generally aren't good at supporting multiple versions of drivers, later ones tend to replace earlier ones. Currently we have a v6 SQL driver, which we'll upgrade & test tomorrow. I'm presuming the upgrade won't adversely affect existing SQL ODBC connections.

Will update later on.

Thanks.

Re: SQL-Server dates opening as text fields in Enterprise Guide

Reeza — Tue, 29 Aug 2017 22:39:04 GMT

Isn't there an MS SQL specific connection via SAS as well, or specific drive for MS SQL? That may also have different behaviour.

Re: SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Tue, 29 Aug 2017 22:46:17 GMT

Yes, but this client only has SAS/Accsss to ODBC.

Re: SQL-Server dates opening as text fields in Enterprise Guide

SASKiwi — Tue, 29 Aug 2017 23:00:20 GMT

@AndrewHowell - If you test using a connection string then you can explicitly point at a particular driver as my example demonstrates. Regarding Windows supporting multiple versions of drivers, we have at least two ODBC drivers installed on all of our Windows SAS servers and have no problems pointing at the required one using connection strings. I'm interested to see how you get on so I much appreciate your feedback.

It looks like Microsoft has updated its SQL Server ODBC drivers recently so you might like to get the latest versions of both Version 11 and Version 13.1:

http://www.microsoft.com/en-nz/download/details.aspx?id=36434

https://www.microsoft.com/en-us/download/details.aspx?id=53339

Re: SQL-Server dates opening as text fields in Enterprise Guide

AndrewHowell — Thu, 31 Aug 2017 23:18:01 GMT

Upgrade of SQL-Server client on the SAS server did the trick perfectly. Thanks to all for your help.