SAS Connection to the ODBC Server - Locks the Database

[SNG1]

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

[SNG1]

Hi

Anybody has any solution for these? Much Appreciated!

Thanks

[JBailey]

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

[SNG1]

My Apologies, its the table that I am connecting to that gets locked.

[JBailey]

Hi @SNG1 

That's good. Can you share the code that you are running?

Best wishes,

Jeff

[SNG1]

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 

[JBailey]

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

[SNG1]

Its SAS Enterprise GUIDE 7.1 and I am viewing the data in the EG Grid.

[JBailey]

Hi @SNG1 

I am looking for something like SAS 9.4M6 for the version of SAS that you are connecting to.

Best wishes,

Jeff

[JBailey]

Hi @SNG1 

Try your program while not viewing data in the grid.

Best wishes,

Jeff

[SNG1]

Thanks

the version Is SAS i see if SAS 9.4

[JBailey]

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:

• Using SAS/SHARE can reduce the locking to record-level (I know, this is a non-solution).
• Enterprise Guide 8.1 holds the lock for a much shorter time (3 minutes of inactivity, can be set to a minimum of 1 minute). Previous releases of EG close the data grid after 30 minutes.

Best wishes,

Jeff