topic Re: Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account? in SAS Data Management

Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account?

Jade_SAS — Wed, 26 Jul 2017 17:16:44 GMT

Hi All,

I have difficulty to understand why the schema is case sensitive in libname statement when connecting to SQL tables with AD account.

For example:

LIBNAME test SQLSVR DATASRC='SQL_Server_name' qualifier=SQL_Database_name SCHEMA=dbo USER='domain\windows_ID' PASSWORD='windows_Password';

here the schema dbo is case sensitive, but the SQL server name and SQL database name are not case sensitive. Do you know the reason of that?

Thank you!

Jade

Re: Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account?

TomKari — Wed, 26 Jul 2017 19:23:10 GMT

It has caused a bunch of people problems through the years. I don't believe this has anything to do with SAS; I think that SAS just passes this through to the SQL Server engine, and this case sensitivity is a feature of SQL Server.

Tom

Re: Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account?

Jade_SAS — Wed, 26 Jul 2017 20:16:42 GMT

Thank you TomKari!

The thing I do not understand are:

1) The schema is not case sensitive in SQL Studio, just like the database name etc.

2) The database name and the server name are not case sensitive in SAS Libname statement

Combine 1) and 2), I did not see anything special about schema, then why only schema is case sensitive in SAS Libname statement?

Thanks,

Jade

Re: Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account?

TomKari — Wed, 26 Jul 2017 22:16:06 GMT

I agree with you that it's odd. Unfortunately, there's no way for us to tell whether it's still case sensitive in SQL Studio, and the product adapts for that, or whether SAS is doing something odd. Just one of those things we have to live with.

However, I can report that the same behaviour exists with at least one other database product (can't remember which, it was years ago), so that leads me to think it's the first option.

Tom

Re: Case sensitive Schema in Libname statement when connectiong to SQL tables with AD account?

Jade_SAS — Fri, 28 Jul 2017 17:11:17 GMT

Thank you, Tom!