Different connections to databases

[Tal]

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

[SAS_INFO · Accepted solution]

hi ,

Signon Rsubmit works

[SASKiwi]

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.

[Tal]

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?

[LinusH]

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

[Tal]

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?

[SASKiwi]

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

[Tal]

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?

[ChrisHemedinger]

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.

[Tal]

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?