Connecting to Microsoft SQL Server from SAS with OleDB

libname SASData ‘C:\PathToSASfiles\DataFolder’;
    libname SQLSrvr oledb provider=sqloledb init_string=’Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myusername Catalog=my_database_name;Data Source=database_server_name’ schema=dbo ;
    options fmtsearch=(SASData) ;
 
    data SQLSrvr.my_data;
    set SASData.my_sas_dataset ;
    run;
 
/*Note that some SQL Servers run as “Instances” so your Catalog could be something like: DatabaseServer\InstanceServer and your connection string would reflect just that:*/
 
    libname SQLSrvr 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 ;  
 
/*What if you want to get the data out of the database and into the SAS environment? Easy, just do the opposite in your datastep: */
 
    data SASData.the_data;
    set SQLSrvr.table_name;
    run;
 
/*You can get a listing of the tables by using this code from SAS Samples #1529:*/
 
    proc sql;
    connect to odbc(dsn=sqlserver uid=dbitest pwd=dbigrp1);
    create table list1
    as
    select * from connection to odbc(ODBC::SQLTables);
    create table list2
    as
    select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,);
    create table list3
    as
    select * from connection to odbc(ODBC::SQLColumns,,”DEPT”,”DNAME”);
    quit;