DATA Step, Macro, Functions and more

Assessing SQL server from host machine

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Assessing SQL server from host machine

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.
 

Accepted Solutions
Solution
‎06-21-2016 06:26 AM
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

Should be :

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


It seems that I can't edit my post.

View solution in original post


All Replies
Super User
Posts: 5,257

Re: Assessing SQL server from host machine

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

Data never sleeps
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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 ;
Occasional Contributor
Posts: 9

Re: Assessing SQL server from host machine

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

Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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 .




Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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

Occasional Contributor
Posts: 9

Re: Assessing SQL server from host machine

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.
 
 
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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?

x.png

 

 

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         
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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

 

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

Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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

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

Occasional Contributor
Posts: 9

Re: Assessing SQL server from host machine

[ Edited ]

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.

 


OP.png
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

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;



Solution
‎06-21-2016 06:26 AM
Super User
Posts: 9,681

Re: Assessing SQL server from host machine

Should be :

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


It seems that I can't edit my post.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 454 views
  • 2 likes
  • 3 in conversation