SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SQL-Server dates opening as text fields in Enterprise Guide

Accepted Solution Solved
Reply
Moderator
Posts: 316
Accepted Solution

SQL-Server dates opening as text fields in Enterprise Guide

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.


Accepted Solutions
Solution
‎08-31-2017 07:18 PM
Super User
Posts: 3,857

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

Posted in reply to AndrewHowell

@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

 

View solution in original post


All Replies
Super User
Posts: 23,262

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

Posted in reply to AndrewHowell

Is DBSASTYPE an option here?

Super User
Posts: 3,857

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

Posted in reply to AndrewHowell

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

Moderator
Posts: 316

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

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

Moderator
Posts: 316

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

Posted in reply to 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.

Super User
Posts: 3,857

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

Posted in reply to AndrewHowell

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

 

Moderator
Posts: 316

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

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.

Super User
Posts: 23,262

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

Posted in reply to AndrewHowell

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

Moderator
Posts: 316

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

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

Solution
‎08-31-2017 07:18 PM
Super User
Posts: 3,857

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

Posted in reply to AndrewHowell

@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

 

Moderator
Posts: 316

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 531 views
  • 0 likes
  • 3 in conversation