Help using Base SAS procedures

Microsoft SQL Server

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

Microsoft SQL Server

hi,

this below runs on pc sas,no issues but it does not on EG SAS (Remote) :

ERROR: The SQLSERVR engine cannot be found.

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


proc sql;
connect to sqlservr as RFM (user=&uid password=&pwd server=&srvr database=&DB );
create table a as
 select * from connection to RFM
 (select Branch AS BRANCH_NUM,
   District,
   Region,
   "Branch Name" as BRANCH_NAM,
   "District Name" as DISTRICT_N,
   "Region Name" as REGION_NAM
 from vw_RetailBranchListing);
 disconnect from RFM;
quit;

 

and  from the  logs i can see that PC SAS is not licenced for a SQLSERV access :

 

---SAS/ACCESS Interface to PC Files
---SAS/ACCESS Interface to ODBC
---SAS/ACCESS Interface to OLE DB

 

but SAS UNIX is and still cannot run that above query


---SAS/ACCESS Interface to DB2                                                                         
---SAS/ACCESS Interface to Oracle                                                                       
---SAS/ACCESS Interface to PC Files                                                                      
---SAS/ACCESS Interface to ODBC                                                                         
---SAS/ACCESS Interface to Microsoft SQL Server       

 

Can anyone  explain this to me please?

Thx                                                   


Accepted Solutions
Solution
‎04-19-2017 08:25 PM
Super User
Posts: 3,101

Re: Microsoft SQL Server

[ Edited ]

@Tal - Of course the choice is yours if you want to use DSNs or not. The big advantage of DSN-less connections is not having to define your connections in two places which means maintaining connections in both your program and the odbc.ini. I just prefer doing it in one place not two. Also DSN-less is better if you don't have the user privileges to change odbc.ini

 

This SAS Note might give you a better idea of how to set up connections under Unix:

 

http://support.sas.com/kb/48/515.html

 

 

View solution in original post


All Replies
Contributor
Posts: 55

Re: Microsoft SQL Server

[ Edited ]

When you run om EG you use the server connections. It Seems to show that you are NOT allowed to use that connection(credentials) or the name og the connection is different on the Remote server connection ? When you run base SAS you run Locally .

Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

Are u saying that i might need the windows server name and a port since the DB is on a different machine?
Super User
Posts: 3,101

Re: Microsoft SQL Server

Here is what I guess is happening. In PC SAS SQLSERVR refers to a DSN (Data Source Name) set up in the Windows ODBC Administrator. This means your program runs OK because it is using SAS/ACCESS to ODBC not SAS/ACCESS to SQL Server.

 

In EG your job is running on the remote SAS server where there is no DSN SQLSERVR set up so SAS assumes it is a database engine. There is no such engine hence you get the error: engine not found. The correct engine name you should be using is SQLSVR:

 

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p09s44hpea09stn1pu...

 

 

Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

thx SASKiwi,

you are actually right. Ran(on PC SAS) the same  query with "odbc" and it works too,also  tried the libname engine ,that works as well

 

LIBNAME Mylib ODBC DSN=RFM user=&uid pw=&pwd;

 

proc sql;

connect to odbc as RFM (user=&uid password=&pwd dsn=RFM);

create table temp_postal1 as

 

Now when  i tried running the above query from UNIX:

 

proc sql;

connect to SQLSVR as RFM (dsn=RFM user=&uid password=&pwd);

 

i am getting this:

 

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

 

but i am positive this has to do with the odbc.ini file which the support team said would update with my SQL SERVER connection strings

 

Thanks  again

Super User
Posts: 3,101

Re: Microsoft SQL Server

[ Edited ]

If you use the DSN= option in your connect statement then that would need to be defined in your odbc.ini file. You may be able to define a complete connection in SAS using the NOPROMPT= option. Check this post out for an example:

 

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

 

 

 

Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

Thx SASKiwi but i think i"d go with the dsn connection . shorter n quicker. One silly question though. If i decide to do the dns-less connection to sqlsvr or connection to odbc i would not need the odbc.ini?
And the syntax(inside the brackets) for both would be the same ?
Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

And i wont need to include any windows server login credentials in that syntax?
Solution
‎04-19-2017 08:25 PM
Super User
Posts: 3,101

Re: Microsoft SQL Server

[ Edited ]

@Tal - Of course the choice is yours if you want to use DSNs or not. The big advantage of DSN-less connections is not having to define your connections in two places which means maintaining connections in both your program and the odbc.ini. I just prefer doing it in one place not two. Also DSN-less is better if you don't have the user privileges to change odbc.ini

 

This SAS Note might give you a better idea of how to set up connections under Unix:

 

http://support.sas.com/kb/48/515.html

 

 

Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

thanks SASKiwi,

libname sqwireno sqlsvr noprompt='Driver=SQLServer;Address=machine1.reg.company.com,1433;Database=users;
       UID=myuser;PWD=mypass;' schema=myschema;

 but i go with the dsn-less then i'd need the driver's name and it is not always SQLServer and if i dont  have the odbc.ini file  how would i know  it(the dirver's name)?

Super User
Posts: 3,101

Re: Microsoft SQL Server

Ask your SAS or Unix admin. In Windows its easy - just go into the Windows ODBC Administrator and the drivers installed are listed there.

Super Contributor
Super Contributor
Posts: 440

Re: Microsoft SQL Server

Thx. Might be able to find it myself on the unix directories. Will try the connection on Monday. Cant wait for the odbc.ini to get updated. Takes 3 weeks they say. Besides the db credentials will i also need the windows login included in the connection string?
Super User
Posts: 3,101

Re: Microsoft SQL Server

[ Edited ]

Depends on how your database is set up. If you have a separate database username and password then you need to use that. If you are not sure check with your database administrator regarding login credentials.

 

According to Mr Google you can find out what ODBC drivers you have installed on Unix by looking in the odbcinst.ini file(s).

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 317 views
  • 0 likes
  • 3 in conversation