topic Re: ms sql in SAS Programming

ms sql

milts — Thu, 07 May 2009 13:49:54 GMT

hello,

what are the different ways SAS can connect to a MSSQL source?

In DI Studio how can you create libraries for MSSQL?

thanks!

Re: ms sql

Doc_Duke — Thu, 07 May 2009 16:49:58 GMT

The MIcrosoft preferred method to connect to SQL Server is via ODBC and that is the route that SAS supports.

If you still have SAS 8.2, you can connect via SAS/Access to Sybase.

Sorry, don't have DI Studio access.

Doc Muhlbaier
Duke

Re: ms sql

LinusH — Fri, 08 May 2009 09:33:37 GMT

You can also use ACCESS to OLEDB.
In DI Studio just run the New Library Wizard (accessed via Source/Target table Designer wizards).

In management console this can be done in the Data Library Manager plugin.

/Linus

Re: ms sql

milts — Sat, 09 May 2009 10:18:29 GMT

which library type will i use? ODBC library or Microsoft SQL Server for Unix Hosts?

thanks!

Re: ms sql

LinusH — Sun, 10 May 2009 07:11:03 GMT

Can't say that I had an opportunity to compare the both. I might go for ODBC beacuse it would give some flexibility to read other sources. And I believe that nativity is not a problem since ODBC and SQL Server are from the same factory...
You might want to contact the institute for a second opinion.

/Linus

Re: ms sql

CameronLawson — Sun, 10 May 2009 09:46:28 GMT

two ways you can assign the library;
1. Via smc: you will need appropriate access. With this method you can control security etc.
2. The easiest way is to place the libref in the application server autoexec.sas file. Whenever a session opens, the library will be assigned. It will make it more difficult however to control security etc.

Re: ms sql

milts — Tue, 12 May 2009 08:23:41 GMT

is there a step by step guide/document for assigning the library via smc?

thanks!

Re: ms sql

LinusH — Tue, 12 May 2009 08:55:49 GMT

If you cared to search support.sas.com you'd probably come up with this doc:

http://support.sas.com/techsup/technote/ts726.pdf

among others.

Linus