topic Re: Different connections to databases in SAS Procedures

Different connections to databases

Tal — Fri, 24 Feb 2017 01:42:18 GMT

Hi,
If i use windows sas or unix to access databases on SQL Server what are the different type of connections besides ODBC that i could use?
Thx

Re: Different connections to databases

SAS_INFO — Fri, 24 Feb 2017 03:53:42 GMT

hi ,

Signon Rsubmit works

Re: Different connections to databases

SASKiwi — Fri, 24 Feb 2017 03:58:43 GMT

I believe OLEDB is an option under Windows but not Unix - you would need SAS/ACCESS to OLEDB for that. I'm fairly sure that SAS/ACCESS to SQL Server also uses ODBC behind the scenes. Why the question? I recommend ODBC with SAS/ACCESS to ODBC as it can be used with a variety of databases.

Re: Different connections to databases

Tal — Sat, 25 Feb 2017 05:37:44 GMT

I tried running pc sas n also EG SAS locally n remotely and i keep gettin the CLI error:wrong userid/password or ODBC drivers not installed
I mean sql server n pc sas both run on windows. Why do i still need odbc drivers?

Re: Different connections to databases

LinusH — Sat, 25 Feb 2017 06:02:56 GMT

Because ODBC requires ODBC drivers regardless of where your database is situated.

Re: Different connections to databases

Tal — Sat, 25 Feb 2017 14:13:39 GMT

But even when i run sas on unix i get the same error and i checked the odbc.ini file. Looks all good
Do i need to be registered in the SAS metadata by a SAS admin or something?

Re: Different connections to databases

SASKiwi — Sat, 25 Feb 2017 22:13:50 GMT

If you want help with your ODBC connection problems then please post a log of your program including error messages.

Re: Different connections to databases

Tal — Fri, 03 Mar 2017 16:33:34 GMT

when running the following query on PS SAS ,EG(Remote) and UNIX with including "rsubmit" statement i am ok :

rsubmit;

proc sql;

connect to sqlsvr as mydb (datasrc=DM_UserDB user =xxx password=yyy readbuff=32767);

create table test(compress = no) as select * from connection to mydb

(SELECT COUNT(*) FROM table_name );

disconnect from mydb;

quit;

endrsubmit;

but when running it on UNIX lets say without the "rsubmit" then i get this:

ERROR: The SAS/ACCESS Interface to SQLSRV cannot be loaded. The SASSQSRV code appendage could not be loaded.

ERROR: A Connection to the sqlsvr DBMS is not currently supported, or is not installed at your site.

I thought the ODBC.ini file would take care of that. So why still the need of rsubmit if i am running this on the remote server?

Re: Different connections to databases

ChrisHemedinger — Fri, 03 Mar 2017 16:52:41 GMT

With the RSUBMIT, your program is being sent to a different SAS node for processing (exactly which node depends on your SAS/CONNECT and signon statements). It's that node that must have the SQL Server connectors configured and the SAS/ACCESS product installed/licensed.

Re: Different connections to databases

Tal — Fri, 03 Mar 2017 17:00:17 GMT

i see.thanks

but at my previous company we had the databases on oracle and we never used the "rsubmit" statement to make the connections so i am trying to sense whether this does not apply to Oracle or not

or does that mean they might had the sas/access interface to oracle there but they dont have the sas/access to sqlserver here?