topic Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX? in SAS Data Management

If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Jade_SAS — Mon, 30 Jan 2017 16:14:51 GMT

Hi All,

We have SAS server under UNIX, if we access Microsoft SQL data via SAS/Access to Microsoft SQL data, is there a way does not need to edit ODBC.ini file?

I googled and found the following link, it seems we definitely still need to edit the ODBC.ini file even via SAS/Access to Microsoft SQL data (instead of the SAS/Access to ODBC)

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001405609.htm

Please advise!

Thanks,

Jade

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

SASKiwi — Mon, 30 Jan 2017 19:30:21 GMT

It is definitely possible for Windows SAS and this reference suggests it could be possible for Unix:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-configure-SAS-ACCESS-Interface-to-ODBC-on-UNIX-Platforms/ta-p/231900

That is assuming SAS/ACCESS to SQL Server uses ODBC underneath.

You would usually use the NOPROMPT option say on a LIBNAME statement to specify the connection string however this reference says it is not supported under Unix:

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0gz66qe8msnkyn1jgnhrr0l2k8y.htm

I'd try this anyway just to confirm if it is correct or not. In my experience specifying database connections completely in a LIBNAME or CONNECT TO statement is way better than having to define DSNs elsewhere.

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Jade_SAS — Mon, 30 Jan 2017 21:01:02 GMT

Thank you SASKiwi!

But the example in the reference using NOPROMPT still has DSN which I think should be defined in the ODBC.ini file, right?

libname mydblib sqlsvr noprompt="uid=myusr1;
pwd=mypwd1; dsn=sqlservr;" stringdates=yes;

So I think there is no way to avoid configuring ODBC.ini file, please correct me if I am wrong.

Thanks,

Jade

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

SASKiwi — Mon, 30 Jan 2017 22:57:06 GMT

Here is a typical connection string we use for SAS under Windows and it completely defines the connection to SQL Server:

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

We don't have Unix SAS so I can't try this for Unix. But if you removed Trusted Connection=yes and added uid= and password = you might get something close to what might work under Unix. I'm keen to know how you get on.

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Jade_SAS — Tue, 31 Jan 2017 16:43:37 GMT

Thanks SASKiwi!

So the server name here is the real server name, not the name that set up in the ODBC file?

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

We have SAS ODBC to SQL data installed in UNIX, now thinking add on SAS/Access to Micosoft SQL data to reduce the work load of admin ( if no need to edit the ODBC.ini file in UNIX, we have a lot servers and databases, really a lot to manage in the ODBC.ini file for admin)

Thanks,

Jade

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

SASKiwi — Tue, 31 Jan 2017 19:53:05 GMT

Yes it is the "real" server name. If you use SQL Server Studio then it is the server name you enter to connect to your required databases.

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Jade_SAS — Tue, 31 Jan 2017 20:56:22 GMT

Thank you, this is really helpful!

Jade