SQL-Server dates opening as text fields in Enterprise Guide

[AndrewHowell]

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.

[Reeza]

Is DBSASTYPE an option here?

[SASKiwi]

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

[AndrewHowell]

@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.

[AndrewHowell]

@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.

[SASKiwi]

@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";

[AndrewHowell]

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.

[Reeza]

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

[AndrewHowell]

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

[SASKiwi · Accepted solution]

@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

[AndrewHowell]

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