BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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                                                   

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

@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

12 REPLIES 12
ANLYNG
Pyrite | Level 9

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 .

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9
Are u saying that i might need the windows server name and a port since the DB is on a different machine?
SASKiwi
PROC Star

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...

 

 

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

SASKiwi
PROC Star

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...

 

 

 

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9
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 ?
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9
And i wont need to include any windows server login credentials in that syntax?
SASKiwi
PROC Star

@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

 

 

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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)?

SASKiwi
PROC Star

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9
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?
SASKiwi
PROC Star

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).

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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