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
@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
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 .
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:
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
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:
@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
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)?
Ask your SAS or Unix admin. In Windows its easy - just go into the Windows ODBC Administrator and the drivers installed are listed there.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.