Date:         Tue, 8 Jul 2008 06:41:53 -0700
Reply-To:     Friar Broccoli <EliasRK@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Friar Broccoli <EliasRK@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: Reading MS SQL Server files
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

How to read "Microsoft SQL Server" files


This is a short and dirty description of what I did to read a
MicroSoft SQL Server database that was handed to me on a USB
drive (as one *.mdf and one *_log.ldf file), using SAS (with
SAS/ACCESS installed).

Most of the info I needed was in here:
http://www2.sas.com/proceedings/forum2008/135-2008.pdf

(I note that I wasted a lot of time trying to get the "OLE DB"
stuff in SAS, which uses the magic incantation SQLOLEDB, to
work.  Basically I have no idea what is required to get these
commands to work, and never found a clear description of what
was needed.)

In addition, although I am not sure it was necessary, I also
downloaded and installed the free "Microsoft SQL Server 2005
Express Edition"

which you can get here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en
This is a TinyUrl to the same location:
http://tinyurl.com/ynldpg

"SQLEXPRESS" allows you to read/Modify the SQL code in the
databases as well directly look_at/open the data etc.  It also
adds another ODBC driver (although I used the one that comes
with WindowsXP).

Anyway, I "attached" my MSSQL database to/with "SQLEXPRESS"
and then used the information from that connection for my SAS
connection parameters as follows:


LIBNAME MyDBLib ODBC NOPROMPT =
  "DRIVER=SQL Server;
  uid=;
  PWD=;
  DSN=WhatIsThis;
  SERVER=M32674\SQLEXPRESS;
  DATABASE=dbTest;"
;


In the above for 'DRIVER=':
'SQL Server' is the name given in the "ODBC Data Source
Administrator" as the driver for reading SQL files.  On most
machines you will see "Microsoft Excel Driver (*.xls)" installed
by default in the same column.

I didn't need a UID or password because I used Windows
Authentication in "SQLExpress".

'DSN=' didn't seem to make a difference so I cannot be sure what it
was.

For 'SERVER='
'M32674\SQLEXPRESS' is the "Server Name" given by
SQLEXPRESS to my local server which is the first thing
that I see when I start SQLEXPRESS.  'M32674' happens
to be the name of my computer.

'dbTest' is the name of my test database.  That is I have two
files named dbTest.mdf and dbTest_log.ldg.  "dbTest" is also the
name given to the database in the SQLEXPRESS Object Explorer
window under DataBases after I 'attached' those files to the
server.


Now that SAS has given my database a LIBRARY name
(here MyDBLib) I can read the tables in the DataBase.
One of the tables in dbTest was TblDeux which I read/copied
into the SAS dataset MyTblUn as follows:


DATA WORK_LIB.MyTblUn;
  SET MyDBLib.TblDeux;
RUN;


I then printed the contents to a file as follows:

PROC PRINTTO PRINT= "TestDat.txt";
RUN;

PROC PRINT DATA=WORK_LIB.MyTblUn;
RUN;

I hope this helps someone.