Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Unanswered: SQL Server 2000 Hangs on a View

    I am working in Powerbuilder 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

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    First to check the view owner identity, use the following SQL statement:
    IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_NAME = 'view_selection_list')
    DROP VIEW view_selection_list
    I mean change the following:
    ls_sql="If Exists ( SELECT name FROM sysobjects WHERE name = 'view_selection_list' AND type = 'V') DROP VIEW view_selection_list "
    To:
    ls_sql="IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_NAME = 'view_selection_list')
    DROP VIEW view_selection_list"
    If you have
    "Cannot drop the view 'TrailCommissionTest', because it does not exist in the system catalog."
    as error message, then the view has been generated under specific owner not under dbo.

    This is a good starting point to check.
    If this is not the case, then we can find something else.

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    I tried your suggested code. It also hangs on another user in PB or in Query Analyzer.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by stan_mich
    I am working in Powerbuilder and SQL Server 2000. Within the application I dynamically Drop then recreate a view named view_selection_list.
    Anytime your application code drops and creates objects as part of its normal operation (not the initial setup...) it is a bad idea. I strongly suggest you rethink your design. Database objects should be static and their structure must be reliable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2007
    Posts
    5
    The application has been successfully working with the dynamic view for 9 years. Several months ago it was migated to Sql Server 2000 from Oracle. That is when the problems started due to SQL Server 2000 way of handling locks.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then it has been a bad design for 9 years. Instead of relying on ANSI standards and good programming principles, it has exploited peculiar properties and features of Oracle. I'm not saying that in itself is a bad thing. I often code using functions and system particuliars that are unique to SQL Server. But in such cases I would not be suprised if the code, or even the entire application design, was not portable to another database platform.

    I would hope that any decent Oracle developer/dba would also frown on creating and dropping views willy-nilly. Why can't you leave this as a permanent view?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looking at your code, this should be implemented as three separate stored procedures that accept parameters for filtering, rather than as a single view created and called dynamically.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2007
    Posts
    5
    For now a rewrite is not a short term solution. I included only a portion of the filters. There are 8-10 filters on about 15 reports.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are all users using different connections? If they are using the same connection, I can see conflicts occuring when two users create different views under the same name and ownership. Perhaps this is what is going on?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2007
    Posts
    5
    Yes, I will be switching in the near future to NT authentication. And I am following your suggestion to create a stored procedure ASAP.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you switch to NT Authentication and ensure that the views are being created under the user's ownership, this may solve your immediate problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2005
    Posts
    48
    Besides, a view (not indexed view) is (re)complied everytime it is called; in an SP you could have the benefit of plan cache reuse besides not (re)creating the SP's everytime they are to be used. SP's are the best in this case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •