SQL Server 2000 Hangs on a View

[stanmich]

I am working in Powerbuilder V10.1 and SQL Server 2000. Within the application I dynamically Drop then recreate a view named view_selection_list. When another user accesses any screen using view_selection_list the screen will hang on the statement "If Exists (SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list".
I also went directly onto the database ran select * from view_selection_list from Query Analyzer. It hangs when the original user creating the view is still active. I know that the issue is locking. I don't know how to fix it.

For example ;
String ls_sql
ls_sql="If Exists ( SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list "
Execute Immediate :ls_sql;

ls_sql="Create View view_selection_list as "
Case 'State'
ls_sql+=" Select distinct proj_id,'State - '+proj_state title from project where proj_state='"+is_data+"'"

Case 'Project'
ls_sql+=" Select distinct proj_id,'Project - '+proj_nam title from project where proj_id='"+is_data+"'"

Case 'All Active Projects'
ls_sql+=" Select proj_id,'Project -' +proj_nam title from project where proj_status = 4 and signed_acq_agmt = 'Y' "

End Choose

Execute Immediate :ls_sql;

The SQL Server connection in the application is:
SQLCA.DBMS = "OLE DB"
SQLCA.ServerName="acq"
SQLCA.LogPass ="*******"
SQLCA.LogId = "acq"
SQLCA.Lock = "RU"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PROVIDER='SQLOLEDB',DATASOURCE='FSRFIN103'"

Thank You
Stanley

 

[mbalent]

Have you tried the '(nolock)' option in your statements? e.g. 'select col from table (nolock) where a = b'

Matt

"Nature forges everything on the anvil of time

 

[stanmich]

The reason that the appliaction was hanging was
SQLCA.AutoCommit = False
I added Commit; after Execute Immediate :ls_sql;
Commit is also needed after a stored procedure.

 

[stanmich]

I wanted to follow up with the simple solution. Execute After
Immediate :ls_sql;
Add
Commit;