Date:         Tue, 20 Oct 2009 10:27:48 -0400
Reply-To:     Kevin Viel <citam.sasl@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kevin Viel <citam.sasl@GMAIL.COM>
Subject:      ODBC to MS SQL Server 2005 (?)
Content-Type: text/plain; charset=ISO-8859-1

I just successfully read from tables in a MS SQL Server 2005 database.  At
least, I believe it is the 2005 version.  To be more detailed, I think it
is the SQLEXPRESS.  Since it was not quite straight forward, I thought I
would post to SAS-L.

LibName Yeah ODBC NoPrompt
= "DSN=Here1;SERVER=Generic\SQLEXPRESS;PORT=1433;DATABASE=Name;
AUTHENTICATION METHOD=0;UID=me;PWD=&PW." Schema = DBO Access = ReadOnly ;

The Schema was a problem for me, until I read the following:

http://support.sas.com/techsup/technote/ts765.pdf

"1) Use SAS Query Window: from the Tools -> Query menu.
when the Query Window has loaded, go to Tools – Switch Access Mode - ODBC.
Then select your datasource and respond to any prompts that pop up. When
you are connected, you will see a list of available tables from your odbc
datasource. The tables are two-level names such as dbo.table1. The first
level (dbo) is the schema."

Identifying the port as 1433 was also essential, it seems, but correct me
if I am wrong.

I hope this helps or speeds things up for someone else.

Kind regards,

Kevin

PS My thanks to Varsha of SAS Tech support for hammering a previous
problem out over a period of 2 weeks or more.