Date:         Wed, 8 Apr 2009 08:18:29 -0700
Reply-To:     sas_googler <odnanrefcolina@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         sas_googler <odnanrefcolina@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Microsoft SQL Server Libname without sqlsvr
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

For those without the benefit of the SAS/ACCESS sqlsvr hook for
Microsoft SQL Server I thought the following might be useful...

libname *your_sas_libname* oledb prompt = no
      provider=sqlOleDb
      dataSource=*the_SQL_SERVER*
      properties=(
         'Persist Security Info'=True
         "Initial Catalog"=*the_catalog*
                 'user id' = *the_user_name*
                 password = *the_password*
      )
      schema=*the_schema* ;


Add the code to the autoexec.sas file and it will be a permanent
libname assignment.

You need to know the catalog and schema names, but you can get
additional details about the connection by submitting both lines below
to start the interactive libname assign like this:

libname tellme oledb; * starts interactive library assignment
*;
%PUT %SUPERQ(SYSDBMSG);   * puts to log details about connection *;

The %put statement will write to the log the following:

=================
OLEDB: Provider=SQLOLEDB.1;Password=xxxxxx;Persist Security
Info=True;User ID=xxxxxx;Data
Source=xxxxxx
=================