topic Re: Assessing SQL server from host machine in SAS Programming

Assessing SQL server from host machine

chaitalip — Wed, 15 Jun 2016 06:51:36 GMT

I am using SAS University edition and want to access the data from SQL server on my host machine(Windows 8.1 64 bit) in my SAS code. Virtual Machine(Oracle VM Virtualbox) OS is also Windows 8.1 64 bit. I am getting following error.

ERROR: The product with which the engine ODBC is associated is either not licensed for your system or the product license has

expired. Please contact your SAS installation representative.

Re: Assessing SQL server from host machine

LinusH — Wed, 15 Jun 2016 06:58:17 GMT

Generally, ODBC connections are not supported in SAS UE - there are many posts that concludes that.

But, @Ksharp has put forward a hack for a work-around - see if that is applicable to your situation:

https://communities.sas.com/t5/SAS-Analytics-U/Connect-to-SQL-Server-Oracle-Database-by-SAS-University-Edition/m-p/273857/highlight/true#M3724

Re: Assessing SQL server from host machine

Ksharp — Wed, 15 Jun 2016 07:08:25 GMT

HaHa.Yes. UE can talk to DB .

Also try IP Address :

LIBNAME z PCFILES SERVER='192.168.56.1' DSN=ksharp USER=xxxxxxx  PASSWORD=xxxxxxx SCHEMA=dbo readbuff=10000 access=readonly ;

Re: Assessing SQL server from host machine

chaitalip — Fri, 17 Jun 2016 10:54:14 GMT

Hi ksharp,

As per your guidelines I have installed PCFiles and configured the ODBC connection. But, when trying to execute following statements in SAS University Edition

libname MyLib '/folders/myfolders/';
LIBNAME MyLib PCFILES SERVER=SERVER DSN=SAS USER='sa' PASSWORD='database@1' ;

I am getting following errors.

- Libref MyLib is not assigned.

- Error in LIBNAME Statement.

- Invalid Option Name.

Also the Library created by "libname MyLib '/folders/myfolders/';" statement disappears.

Pl. help.

Thanks, in advance

Re: Assessing SQL server from host machine

Ksharp — Sat, 18 Jun 2016 06:40:51 GMT

You also need specify SCHEMA and Try IP address if you are using Oracle Visual Box .
Copy my code and change SERVER='your IP address'


"Also the Library created by  "libname MyLib '/folders/myfolders/';" statement disappears."
Because you use the same library name MYLIB in libname, try other name Like Z .

Re: Assessing SQL server from host machine

Ksharp — Sat, 18 Jun 2016 06:44:46 GMT

Also when you configured the ODBC connection, make sure test it at the last step and it shows success! .

Re: Assessing SQL server from host machine

chaitalip — Mon, 20 Jun 2016 06:35:42 GMT

Hi,

As suggested by you i have followed the entire procedure as below,

LIBNAME z PCFILES SERVER='My IP Address' DSN=SAS USER='My System user' PASSWORD='MySystem password' SCHEMA=dbo readbuff=10000 access=readonly ;

but, I am getting following error :

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER=<username> SERVERPASS=<password> on

libname statement).

ERROR: Error in the LIBNAME statement.

When I searched for this error I was directed to "http://support.sas.com/kb/45/460.html" link. which suggested

- setting "Logon as a Batch Job" privilege in the local security policy on the Windows server.

Still i am getting the above error.

Pl. Help.

Thanks in Advance.

Re: Assessing SQL server from host machine

Ksharp — Mon, 20 Jun 2016 07:39:18 GMT

1) Did you start PC FILES by hand ? Not as a system service to start ?

2)Did you set up a password for your PC FILES Server?

3)My PCFILES Server and UE are on the same PC(laptop), OS is Windows 7 Professional , UE is download before two weeks .

Can you change your OS or PC . Just a normal computer , not Server Configure .

Here is my LOG:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 51         
 52         LIBNAME z PCFILES SERVER='192.168.56.1' DSN=ksharp USER=cbcuser001  PASSWORD=XXXXXXXX SCHEMA=dbo readbuff=10000
 52       ! access=readonly ;
 NOTE: 已成功分配逻辑库引用名“Z”，如下所示: 
        引擎:        PCFILES 
        物理名: ksharp
 53         
 54         
 55         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 65

Re: Assessing SQL server from host machine

Ksharp — Mon, 20 Jun 2016 07:43:30 GMT

Is that IP is your real IP of PC FILES Server?

Start->type CMD at run line -> type ipconfig /all to your IPv4 Address.

Re: Assessing SQL server from host machine

Ksharp — Mon, 20 Jun 2016 07:51:43 GMT

USER and PASSWORD is for DSN when you set up a connection to DB.

NOT 'My System user' 'My System PW'.

Re: Assessing SQL server from host machine

chaitalip — Mon, 20 Jun 2016 10:31:02 GMT

Hi KSharp,

Thanks for your reply. It helped. Now, I am able to access the SQL Server but, have one more query.

How do i get the data from specific table of my sql database which i have selected as default database in my DSN.

CODE :

libname MyLib '/folders/myfolders/';
LIBNAME z PCFILES SERVER='MyIP' DSN=SAS USER=sa PASSWORD=database@1
SCHEMA=dbo readbuff=10000 access=readonly;
%let path = /folders/myfolders/;
libname strat "&path";

proc sql ;
connect to PCFILES(SERVER='my IP' DSN=SAS USER=sa PASSWORD=database@1);
create table work.stud as select* from connection to PCFILES
(PCFILES:: SQLTables "", "", "", "");
quit;

proc print data=work.stud;
run;

OUTPUT: Attached.

Re: Assessing SQL server from host machine

Ksharp — Tue, 21 Jun 2016 01:02:41 GMT

Once you successfully  connect to DB , that is going to be easy to pull data out from them.
You can do the same thing as I post :


%let path = /folders/myfolders/;
libname strat "&path";

data strat.MarksDtls;
 set MyLib.MarksDtls;
run;



Or if you need pull out many tables. Try this:

data _null_;
 set  work.stud (where=(table_schem='dbo'));
 call execute(cats('data strat.',table_name,'; set MyLib.',table_name,';run;'));
run;

Re: Assessing SQL server from host machine

Ksharp — Tue, 21 Jun 2016 01:08:10 GMT

Should be :

data strat.MarksDtls;
 set  Z.MarksDtls;
run;


It seems that I can't edit my post.