Connecting SAS/ACCESS to SQL Server

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Connecting SAS/ACCESS to SQL Server

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


Accepted Solutions
Solution
‎11-05-2014 09:58 AM
Respected Advisor
Posts: 3,124

Re: Connecting SAS/ACCESS to SQL Server

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

View solution in original post


All Replies
Super User
Posts: 9,682

Re: Connecting SAS/ACCESS to SQL Server

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

Frequent Contributor
Posts: 81

Re: Connecting SAS/ACCESS to SQL Server

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.

Super Contributor
Posts: 578

Re: Connecting SAS/ACCESS to SQL Server

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. 

Super User
Posts: 9,682

Re: Connecting SAS/ACCESS to SQL Server

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

Solution
‎11-05-2014 09:58 AM
Respected Advisor
Posts: 3,124

Re: Connecting SAS/ACCESS to SQL Server

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

Frequent Contributor
Posts: 81

Re: Connecting SAS/ACCESS to SQL Server

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.

Respected Advisor
Posts: 3,124

Re: Connecting SAS/ACCESS to SQL Server

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

Frequent Contributor
Posts: 81

Re: Connecting SAS/ACCESS to SQL Server

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.

Respected Advisor
Posts: 3,124

Re: Connecting SAS/ACCESS to SQL Server

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 Smiley Happy

Haikuo

Frequent Contributor
Posts: 81

Re: Connecting SAS/ACCESS to SQL Server

Hi Haikuo,

I did it.Smiley HappySmiley Happy

Thank you so much your inputs really help a lot.Smiley Happy

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

Respected Advisor
Posts: 3,124

Re: Connecting SAS/ACCESS to SQL Server

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

Frequent Contributor
Posts: 81

Re: Connecting SAS/ACCESS to SQL Server

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

after above statements.

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 8303 views
  • 2 likes
  • 4 in conversation