• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1170
  • Last Modified:

Reading SQL Server 2008 data using SAS9.1

We have a a database on SQL Server 2008 and we just hired a SAS programmer to do some statistics on the data.
We would like to linked both Software so SAS can read the data on the SQL Server 2008.
by the way, the server is running Windows Server 2008.
Thank for any help.
0
LougaLo
Asked:
LougaLo
2 Solutions
 
MohammedUCommented:
You can use OLEDB connection string..
You have to create a sql login IN SQL 2008 and use that in the oledb connection string or the datasource used by the SAS.
oledb provider=sqloledb init_string=’Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myusername Catalog=my_database_name\my_instance_name;Data Source=database_server_name’ schema=dbo ;  etc...
0
 
d507201Database Marketing ConsultantCommented:
I don't work with SQL Server, but I think you have to license SAS/Access for Relational Databases to get Access for OLE DB. In the same bundle you also get SAS/Access for Microsoft SQL Server.  Use that.  Native access is always more efficient especially if you take advanatage of the libname options.  

I work in UNIX and use SAS/Access for Teradata, DB2 and Oracle. The libref options make all the difference in the world in terms of performance..
0
 
Aloysius LowCommented:
depends on which server you are running your base SAS from, you'll need SAS/Access to ODBC (on Windows) or SAS/Access to MSSQL (for non-Windows).

for Windows base SAS, you just need to configure an ODBC connection using Window's Data Sources (ODBC) from the control panel, and use it in a LIBNAME statement.

for non-Windows, i'm not able to advise as I have never tried on that before.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
LougaLoAuthor Commented:
i was able to connect the database with the SAS through ODBC connection. but I am failling to read any data from the database. I tried the sas LIBNAME but it is not working.
0
 
Aloysius LowCommented:
what's your libname statement like?

did you define the schema name in the ODBC connection setup?
0
 
LougaLoAuthor Commented:
yes the schema name is already defined.

here I am using the Windows Authentication to log in the SQL server. I did not set up any password and username.
0
 
Aloysius LowCommented:
can you post your libname statement? did you mean to say that when you tried executing the libname statement it fails? if so, what do you mean when you say "i was able to connect the database with the SAS through ODBC connection."?

what is the schema you are connecting to (when you set up the ODBC connection)?
0
 
LougaLoAuthor Commented:

lowaloysius:
I clicked on Settings --> Control Panel and choose Administrative Tools.
From there, choose Data Sources (ODBC).

The driver has been configured and the test connection is successful.
This is  where I get stuck.

it seems I need to create a library within SAS in order to read tables in sql server.
0
 
Aloysius LowCommented:
do you have something like:
LIBNAME [some name] ODBC DSN=[your ODBC source] SCHEMA=[the schema name in the database];

in your codes?
0
 
LougaLoAuthor Commented:
login to local SQL Server through Windows Authentication

here is the statement

LIBNAME SQL ODBC DSN=’SasODBC’ user='' pw=pwd schema=dbo;

error
ERROR: Libname SQL is not assigned.
ERROR: Error in the LIBNAME statement.
ERROR 22-7: Invalid option name SASODBC.
0
 
Aloysius LowCommented:
how would the following do for you:
LIBNAME SQL ODBC DSN="SasODBC" user='' pw=pwd schema=dbo

what i have done is to change from single quotation marks to double quotation marks

one other thing i'm not sure of is whether you can use the word SQL as the library name. could you try something else?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now