questions about SAS ODBC connection to SQL server data

[Jade_SAS]

Hi All,

    I have some questions about SAS ODBC connection to SQL data.

    1) When set up the ODBC.ini file, do you have to set up separate entry for every database you want to access?

       Or can you do it at server level, so you can access all the databases under that server?

        I think we only can set up ODBC.ini file at database level, correct me if I am wrong.

    2) When you try to access the SQL data via libname in SAS, can you see all the tables under the libref in SAS libraries?

      for example,

LIBNAME test odbc Password=*** User=*** DATASRC=*** schema=sas1;

Should I see all the tables of schema sas1 in library "test" in SAS?

Thank you!

Jade

[SASKiwi]

You can set up an ODBC connection at the server level and then use SQL passthru to identify which database, schema and table you want. It would look something like this:

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable]
   )
  ;
  disconnect from odbc;
quit;

[Jade_SAS]

Thank you SASKiwi!

If I set up the libname at server level,

LIBNAME test odbc Password=*** User=*** DATASRC=***;

Will I be able to see all the datasets under "test" library? Thanks!

[SASKiwi · Accepted solution]

Unfortunately I don't think so. Your DATASRC definition could include just the server name, but then you will have to add the DATABASE= and SCHEMA= options to your LIBNAME statement for the tables to be visible. Check out these examples:

http://support.sas.com/kb/52/777.html

Also be aware if you have any table names longer than 32 characters, they wont show in SAS - this is a current SAS limitation. You have to use PASSTHRU to read those.

[Jade_SAS]

Thank you for the help!