topic Re: Microsoft SQL Server in SAS Procedures

Microsoft SQL Server

Tal — Tue, 11 Apr 2017 19:43:17 GMT

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

Re: Microsoft SQL Server

ANLYNG — Tue, 11 Apr 2017 21:09:22 GMT

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 .

Re: Microsoft SQL Server

Tal — Tue, 11 Apr 2017 21:17:02 GMT

Are u saying that i might need the windows server name and a port since the DB is on a different machine?

Re: Microsoft SQL Server

SASKiwi — Wed, 12 Apr 2017 02:08:20 GMT

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#p09s44hpea09stn1puyx1l5vcwwt.htm

Re: Microsoft SQL Server

Tal — Wed, 12 Apr 2017 14:49:16 GMT

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

Re: Microsoft SQL Server

SASKiwi — Wed, 12 Apr 2017 20:24:55 GMT

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-Linux/m-p/349472#M10389

Re: Microsoft SQL Server

Tal — Thu, 13 Apr 2017 01:12:54 GMT

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 ?

Re: Microsoft SQL Server

Tal — Thu, 13 Apr 2017 01:27:19 GMT

And i wont need to include any windows server login credentials in that syntax?

Re: Microsoft SQL Server

SASKiwi — Fri, 14 Apr 2017 00:44:27 GMT

@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

Re: Microsoft SQL Server

Tal — Fri, 14 Apr 2017 21:34:52 GMT

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

Re: Microsoft SQL Server

SASKiwi — Fri, 14 Apr 2017 23:07:15 GMT

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

Re: Microsoft SQL Server

Tal — Fri, 14 Apr 2017 23:12:50 GMT

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?

Re: Microsoft SQL Server

SASKiwi — Sun, 16 Apr 2017 00:30:10 GMT

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