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>
Subject: Re: Reading MS SQL Server files
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
Most of the info I needed was in here:
(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
In addition, although I am not sure it was necessary, I also
downloaded and installed the free "Microsoft SQL Server 2005
which you can get here:
This is a TinyUrl to the same location:
"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
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 =
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
'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
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:
I then printed the contents to a file as follows:
PROC PRINTTO PRINT= "TestDat.txt";
PROC PRINT DATA=WORK_LIB.MyTblUn;
I hope this helps someone.