topic Re: MS SQL ODBC connection works fine, but how to update the LASR data automatically? in SAS Data Management

MS SQL ODBC connection works fine, but how to update the LASR data automatically?

theGull — Thu, 26 Jan 2017 17:37:00 GMT

We are newcomers to SAS VA, and have a single Windows Server based SAS VA installation - SAS 9.4_M3 and VA Server 7.3.

Our database servers are MS SQL and I have successfully connected to the required database and tables using ODBC.

First record set is imported into LASR and queryable etc, but these SQL databases are bulk updated on a daily basis and so I need to now the steps involved in grabbing the newly updated data, preferably on a daily basis.

I can manually upload the tables each day, but this is not really what I'm looking for.

Any help or suggestions would be very much appreciated. If these involve 'work' at the SQL Server end then this is just as accessible to me as I have good SQL and developer availability.

Many thanks

Re: MS SQL ODBC connection works fine, but how to update the LASR data automatically?

ankit___gupta — Fri, 27 Jan 2017 09:51:34 GMT

Hi theGull,

We have a simillar structure in our company. what you need to do. is the following.

Step1: Using SAS Enterprise Guide establish a connection to you SQL server and start pulling the data to your sas serverver node or the hadoop structure.

Step 2: Open VA in your browser. In the Data Preparation tab. do the following:
---> create a new query.

---> drag drop the data from the libraray from the left side panel to the work area.

---> now right click the table, and select "add all columns".

---> then on the right hand side go and mention the output table name or the LASR table name.

---> Save the query and run it.

---> [How to schedule SOLUTION] on the top bar there is a clock sign. click it. Select daily option select the time in which you want it to happen and it will be scheduled.

hope it helps..

Re: MS SQL ODBC connection works fine, but how to update the LASR data automatically?

mhamlett — Sun, 05 Mar 2017 16:16:59 GMT

i use enterprise guide to load tables daily..about 8 gig of data.

we do a complete clean and replace concept. so yes the 1 gig table i load taday is completely reloaded tomorrow plus any added records. some of our data is updated in ms sql table so simply doing an ADD of new records is not feasible for all our tables.

however, what was pointed out to me was and ADD feature in the data preparation tool on the va hub.

it somehow will only add new records, but i havent researched it.