BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

@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

10 REPLIES 10
Reeza
Super User

Is DBSASTYPE an option here?

SASKiwi
PROC Star

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

AndrewHowell
Moderator

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

@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
PROC Star

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

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
Super User

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
Moderator

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

SASKiwi
PROC Star

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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