topic Steps to connect to SQL Server and load data into LASR table in Administration and Deployment

Steps to connect to SQL Server and load data into LASR table

L2Fly — Fri, 12 May 2017 00:01:04 GMT

Hello - I am looking for a document or help on how to setup a connection from SAS 9.4 on Windows Server to SQL Server database. I have gone to the SAS server and created an ODBC data source connection under System DSN tab to our sql server and connection is a success.

I am assuming I should create a Library under Data Library Manager (SAS Management Console) and then import my tables in that library. I would appreciated any help or document on this task.

Best

Re: Steps to connect to SQL Server and load data into LASR table

AnandVyas — Fri, 12 May 2017 07:03:24 GMT

Hi @L2Fly

This Blog Post is best for this question:

http://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/

If you wish your auth domain to be available in drop down under Register the DBMS server, screen 5
you can create that before starting to register the new server under user manager. Follow this link for the same.

http://support.sas.com/documentation/cdl/en/mcsecug/64770/HTML/default/viewer.htm#n11xfvoau0jli0n1cl6rtfh5idfx.htm

Hope this helps.

Anand!

Re: Steps to connect to SQL Server and load data into LASR table

SASKiwi — Fri, 12 May 2017 08:59:24 GMT

In addition to @AnandVyas's very helpful post you may wish to consider not using DSNs at all and instead define everything in a connection string. This post shows you how:

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Linux/m-p/349265#M10387

To define a connection string in SMC, just choose the CONNECTION STRING option instead of the DSN option when defining the ODBC server.

Also a good alternative to registering your tables in metadata is to add the an extra attribute to your LIBRARY definition - AssignMode = 2. This will ensure your tables get listed in EG without registering.

Re: Steps to connect to SQL Server and load data into LASR table

L2Fly — Fri, 12 May 2017 16:07:58 GMT

Thank you Anand-V and SASKiwi for your replies. SASKiwi - SAS did our installation and it looks like they used DSN for connection not sure why. Also I looked at the link you provided and if I do it that way then I need to place the username and password into the string and I am not sure who will be able to see that! I forgot to mention in my first but yes I want to register the tables in MetaData first but my issue is finding the steps to do it. I have looked and searched but they are mostly talking about importing data from Excel...

Here what I think I should be doing (if using the DSN).

1- I logged in into the server as a server admin user (who has access to the SQL Server) and created the ODBC connection in DSN.

2- Now what I need to do is to register tables into metadata (I am assuming when I do this it will create the library with the list of tables in it)

3- Load the data into LASR

Stpes two and three and perhaps for all of the above steps I need some guide and help.

Again thanks so much to both of you for your help

Regards

Re: Steps to connect to SQL Server and load data into LASR table

anja — Fri, 12 May 2017 19:48:10 GMT

take a look at the following doc:

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#n017taop8cx07in1r3awuwxlyb9m.htm

It will "land" on the Ora example. Look to the left and you'll find a list of all DBMS etc libs you can create in

SASMC.

Hope that helps

Thanks

Anja

Re: Steps to connect to SQL Server and load data into LASR table

SASKiwi — Sat, 13 May 2017 01:19:01 GMT

@L2Fly - you don't have to put your userid and password in the connection string. If you are using Windows Authentication to connect to SQL Server you can use this string:

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

The - Trusted Connection=yes - part of the string says use my Windows user account and password to authenticate with SQL Server.

I also suggest trying out the server connection in a SAS LIBNAME statement first as it is a lot easier to test:

libname test ODBC noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

@anja's link also will lead you to examples of using connection strings:

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#p1t9q2knb9tq5ln101dkrdsa9ymf.htm

Re: Steps to connect to SQL Server and load data into LASR table

L2Fly — Sat, 13 May 2017 03:35:52 GMT

Hi Anand_V, the link to the blog was great. I was able to go through it and it helped very much. Now when I right click and select Display LIBNAME Statement I get the following but the problem is that it is using my userID for user. What I want to do is use a service account I have created to be used for user=myserviceacct

LIBNAME LibMyLibName ODBC DATAsrc=SASSQL SCHEMA=DBO USER=MyID PASSWORD="{???}123456789012345678890" ;

I logged into the SAS Management console as sasadm and when I run the above statement it passes my userID but I want to use the fix service account.

Best and thanks

Re: Steps to connect to SQL Server and load data into LASR table

L2Fly — Wed, 17 May 2017 18:32:10 GMT

Hello everyone and thanks for your help and posts. After reading most of the links I was able to do the following. I would love and very much appreciated to know your thoughts and help on this:

Steps I took:

Created a domain service account called SASSQLCONNECT. This service account has access to SQL Server.
Logged in into the SAS OA server with this user (only once to setup the ODBC driver) and did setup ODBC connection to SQL Server.
I followed this blog http://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/ and as the result created an entry under [Server Manager] called [SQL Server: SQLDevDB] and also a Library under [Data Library Manager] called [SQL Server: SQLDevDB Library].

Here are my question:

I go and select and right click on [SQL Server: SQLDevDB Library] in Libraries and select Register Tables > Next > there is a log on windows pops up for SASAppOA, What user ID I need to log-in with? (at this time I just login with my own userID) and after I do this I get another log-on windows for [SQL Server: SQLDevDB Library] and again I use my own login (I am sure this is not correct)
Then I see the list of my tables from database, I select one and Location is set to /Shared Data > Next> Finish
I am assuming now I have registered the table in MetaData server.
Now I want to load the data into LASR table, how should I do that?
I can go and open SAS Enterprise Guide and under Servers SASAppOA and Explore the data.
I use SAS Enterprise Guide to Upload to LASR by finding the registered table but I get an error on a popup window for Upload to LASR saying “Unable to locate any LASR libraries. This task requires a LASR library”

Again thank you for your feedback

Regads

Re: Steps to connect to SQL Server and load data into LASR table

SASKiwi — Wed, 17 May 2017 20:26:04 GMT

If you would like to bypass registering tables but still have them available, add this to your library definition:

Re: Steps to connect to SQL Server and load data into LASR table

L2Fly — Thu, 18 May 2017 17:02:05 GMT

Thank you but this did not help me. I will contact SAS Support.

thanks again for all of your help.