topic Re: Connecting SAS/ACCESS to SQL Server in SAS Programming

Connecting SAS/ACCESS to SQL Server

AnandSahu — Wed, 05 Nov 2014 12:55:29 GMT

Hi All,

I have to establish a connection to SQL Server using SAS/ACCESS with ODBC but I don't know which DSN should be use to start the process, UserDSN, SystemDSN or FileDSN.

what is the difference between accessing SQL Server using ODBC OR ODE BD.

I am referring the attached document.

Regards

Anand Sahu

Re: Connecting SAS/ACCESS to SQL Server

Ksharp — Wed, 05 Nov 2014 13:23:48 GMT

There is a name you need to type when you make a ODBC connection with SQL.

Re: Connecting SAS/ACCESS to SQL Server

AnandSahu — Wed, 05 Nov 2014 13:56:13 GMT

Hi Xia keshan,

I just want to know the usage and approach of selecting either UserDSN, SystemDSN or FileDSN. Its clearly mentioned on the attached document that we need to put a server name.

Re: Connecting SAS/ACCESS to SQL Server

DBailey — Wed, 05 Nov 2014 14:38:39 GMT

I would start with SystemDSN as that just means its available to anyone using your computer. UserDSN is only available to the user that created it.

Re: Connecting SAS/ACCESS to SQL Server

Haikuo — Wed, 05 Nov 2014 14:58:15 GMT

Besides the answer you already have, here are couple of other things you may want to consider:

1. File DSN, just like System DSN, is also not user restricted. Once created, it can be shared by many different users, as it is just a file containing connection information.

2. OLEDB vs. ODBC. First, you need to make sure you have license for either of them to start with. ODBC is an open relational database connection API that most of the database manufactures will support. OLEDB is a Microsoft API that works with Oracle in addition to its own database, Access and SQL server. I haven't seen any benchmarks on the efficiency comparison between these two, but they maybe outthere somewhere.

3. You don't have to define any DSN to be able to connect, as long as you have the driver installed. For example, this works for a SQL server database:

libname test odbc schema="dbo" bulkload=YES noprompt="server=yourservername,31723;DRIVER=SQL Server;Trusted_Connection=yes;database=yourdatabasename";

Good luck,

Haikuo

Re: Connecting SAS/ACCESS to SQL Server

AnandSahu — Wed, 05 Nov 2014 18:51:51 GMT

thank you Hikuo...

I just need to know one more thing that how can I examine whether a system has SAS/ACCESS as I would be making connecting remotely on client's system. They use window SAS.

Re: Connecting SAS/ACCESS to SQL Server

Haikuo — Wed, 05 Nov 2014 18:57:25 GMT

SAS/ACCESS is for you to ACCESS relational database or other data format other than SAS tables; SAS/CONNECT is for you to remotely link two SAS instance. Both can be checked using:

proc setinit;run; /*for licensing*/

proc product_status;run;/*for installation*/

Good luck,

Haikuo

Re: Connecting SAS/ACCESS to SQL Server

AnandSahu — Wed, 05 Nov 2014 19:02:18 GMT

One more thing....why do we Authenticate SQL Server and what is NT Authentication.

I know..these questions sound like stupid question but its first time I am going to make a connection tomorrow.

Re: Connecting SAS/ACCESS to SQL Server

Haikuo — Wed, 05 Nov 2014 19:17:36 GMT

You need credentials to access a database. Your credentials (password, userid) can be assigned at OS level (eg. NT authentication) or the database level ( eg. SQL server authentication). Usually OS level authentication (eg. IWA) would be a better option.

Are you excited

Haikuo

Re: Connecting SAS/ACCESS to SQL Server

Ksharp — Thu, 06 Nov 2014 12:08:23 GMT

It doesn't matter if it is UserDSN or SystemDSN .. as long as you can connect to SQL Database.

Re: Connecting SAS/ACCESS to SQL Server

AnandSahu — Thu, 06 Nov 2014 12:41:15 GMT

Hi Haikuo,

I did it.

Thank you so much your inputs really help a lot.

But I faced a problem while running Prompted Connection as per the document after establishment the ODBC DSN connection.:smileyconfused:

%put %superq(sysdbmsg);

above statement threw an error, I couldn't note the error, which caused problems as It gives an "ODBC:" statement with WSID and I am not aware of it.

LIBNAME SQL ODBC prompt;

NOTE: Libref SQL was successfully assigned as follows:

Engine: ODBC

Physical Name: sqlsrv

7 %put %superq(sysdbmsg);

ODBC: DSN=sqlsrv;UID=jebjur;PWD=jebjur1;WSID=d17117

So typed LIBNAME statement manually as its mentioned in the document i.e. WSID=d17117,

LIBNAME SQL ODBC noprompt= "dsn=sqlsrv; uid=himdw; pwd=******; wsid=d17117";

Initially the SQL library created but there were no datasets but after some it started showing tables in the same library. I really couldn't figure out what was the reason. So could you please share views on it.

Regards

Anand Sahu

Re: Connecting SAS/ACCESS to SQL Server

Haikuo — Thu, 06 Nov 2014 15:24:34 GMT

While I am not entirely familiar with this 'prompt' approach, but it seems to me just a way of entering credentials when prompted. WSID is Windows System ID, so it should be your computer name registered in active domain. %superq(sysdbmsg) is to replay whatever you have just entered for your connection. The reason why there was no table and then there were, I am not sure. But one thing obvious to me is that I didn't see SCHEMA= option anywhere in your connection string. This will point your library to your own schema or some other default schema configured in your SQL server database.

Good luck,

Haikuo

Re: Connecting SAS/ACCESS to SQL Server

AnandSahu — Fri, 07 Nov 2014 03:59:22 GMT

I used LIBNAME SQL ODBC DSN=sqlsrv user=sasjlb pw=pwd schema=dbo;

after above statements.