Hello All,
I am using SAS EG 7.1 (32 bit), I am connecting to a SQL Server via ODBC Data Source Administrator (using the SQL Server Driver).
Using the above I run into issues where when I run a Program, it locks the entire DB I am connecting to...I do use a NOLOCK option in my library statement
i.e. READ_LOCK_TYPE = nolock;
Even with this statement I sometimes end up locking the entire DB, is there something that I am missing? Is there a statement that I need to add in the Program itself to prevent this from happening?
Thanks for all your Help
Hi
Anybody has any solution for these? Much Appreciated!
Thanks
Hi @SNG1
When you say "entire DB" do you mean the table(s) you are dealing with or the entire MS SQL Server database?
Best wishes,
Jeff
My Apologies, its the table that I am connecting to that gets locked.
Thanks for the Quick Reply:
I have the following in THE LIBRARY Statement -
libname wc13b odbc dsn='wc13b' readbuff=3000 insertbuff=3000 dbcommit=3000 READ_LOCK_TYPE=NOLOCK;
I have also tried it with -
libname wc13b odbc dsn='wc13b' READ_LOCK_TYPE=NOLOCK;
My program to the pull the data is very simple -
Proc Sql;
create table SASUSER.clients_B
as
select distinct
Power_ID,
input(compress(phonecell),10.) as Cell_Phone,
LSTNAME,
FIRSTNAME,
input(HAREA, 10.) as HAREA,
input(HPHONE, 10.) as HPHONE,
input(catx('',HAREA, HPHONE),10.) AS Home_Phone
from wc13.clients
;quit;
The clients table gets locked
Hi @SNG1
Which version of SAS are you using?
Are you viewing the table in an EG data grid (not sure exactly what it is called) while you are running this code?
Best wishes,
Jeff
Its SAS Enterprise GUIDE 7.1 and I am viewing the data in the EG Grid.
Hi @SNG1
I am looking for something like SAS 9.4M6 for the version of SAS that you are connecting to.
Best wishes,
Jeff
Thanks
the version Is SAS i see if SAS 9.4
Hi @SNG1
Try your program while not viewing data in the grid. This should stop the locking issue.
I have researched this problem. Opening a table in the Data Grid creates a READ lock on the table. According to the developers, it "is a SAS thing, and not an EG thing." I am currently trying to find out what this means. I am looking into this.
Here are some things that may help, but probably won't:
Best wishes,
Jeff
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.