Proc download from Sql server?

Super Contributor
Posts: 418

Proc download from Sql server?

Hello everyone. I Have processes that move data from our sql server database into my local machine (to allow lots of table manipulation), and also re-insert data into Sql itself.

The issue is my sql server database is located on the other side of the United States (literally) from my computer and I run a local sas machine license. I only have base sas to do this and getting sas on a server is out of the question.

So based upon this, does anyone know a more efficient way to move a table from a sql server database on the other side of the country to a local machine?

I have been told that proc download is the fastest way to do this, but I do not know if you can use proc download a sql server database table, and when I tried it did not seem to work (although maybe there is an option I am missing?).

To be clear, I am trying to find the most efficient way to do the following.  I KNOW my network is the reason this code takes a long time, as the cpu time for sas is ~30 seconds while the clock time is > 2 hours. Also, if I do this code After copying the table from the server (aka If I copy it to work, and then re-create the table in the same manner pulling from the work table) it only takes about a minute. So i'm basically losing a 120 fold process efficiency by going over my network!

libname Myconne odbc dsn='mydsn' schema=dbo bulkload=yes dbmax_text=32000;

proc sql;

create table Mytable as

select * from mycone.Databasetable;



Thanks for your help!

Trusted Advisor
Posts: 3,215

Re: Proc download from Sql server?

Posted in reply to Anotherdream

You are using the ODBC access interface. That is you only connection to the SQL server by a local SAS process (you local machine).

By that Proc download SAs/connect is out of scope as they are focussed to a server-based SAS you do not have.

You pointing to the performance is almost complete, as you have:

- SAS/Access

- ODBC driver (local)

- Network

- SQL Server processing

Perhaps you could improve some things with parameters (buffsize / locking) using SAS/ACCESS to SQL-server options and doing copy once.
AS you ahve found the bulk-load option you are already aware of that. Try to calcualted the size of the dataset en your connection speed. When the network line usage will be up to 50% you are limited by that. 

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation