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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

12 REPLIES 12
Ksharp
Super User

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

AnandSahu
Calcite | Level 5

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.

DBailey
Lapis Lazuli | Level 10

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. 

Ksharp
Super User

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

Haikuo
Onyx | Level 15

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

AnandSahu
Calcite | Level 5

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.

Haikuo
Onyx | Level 15

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

AnandSahu
Calcite | Level 5

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.

Haikuo
Onyx | Level 15

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

AnandSahu
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

AnandSahu
Calcite | Level 5

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

after above statements.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 22778 views
  • 3 likes
  • 4 in conversation