Help using Base SAS procedures

Different connections to databases

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

Different connections to databases

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

Accepted Solutions
Solution
‎03-06-2017 11:50 AM
Contributor
Posts: 63

Re: Different connections to databases

hi ,

 

Signon Rsubmit works

View solution in original post


All Replies
Solution
‎03-06-2017 11:50 AM
Contributor
Posts: 63

Re: Different connections to databases

hi ,

 

Signon Rsubmit works

Super User
Posts: 3,260

Re: Different connections to databases

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.

Super Contributor
Super Contributor
Posts: 444

Re: Different connections to databases

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?
Super User
Posts: 5,441

Re: Different connections to databases

Because ODBC requires ODBC drivers regardless of where your database is situated.
Data never sleeps
Super Contributor
Super Contributor
Posts: 444

Re: Different connections to databases

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?
Super User
Posts: 3,260

Re: Different connections to databases

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

Super Contributor
Super Contributor
Posts: 444

Re: Different connections to databases

 

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?

 

Community Manager
Posts: 2,956

Re: Different connections to databases

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.

Super Contributor
Super Contributor
Posts: 444

Re: Different connections to databases

[ Edited ]
Posted in reply to ChrisHemedinger

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?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 272 views
  • 4 likes
  • 5 in conversation