Assessing SQL server from host machine

[chaitalip]

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.

[LinusH]

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

[Ksharp]

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 ;

[chaitalip]

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

[Ksharp]

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 .

[Ksharp]

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

[chaitalip]

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.

[Ksharp]

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         

[Ksharp]

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

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

[Ksharp]

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

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

[chaitalip]

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.

[Ksharp]

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;

[Ksharp · Accepted solution]

Should be :

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


It seems that I can't edit my post.