Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Red face Unanswered: Disappearing user view

    Client USR1 owns schema USRAPP. Creates a view USRAPP.myview2
    It creates successfully. We see it in list of SSMS 2005 database Views for USRAPP.myview2.
    Within a minute, the view has disappeared from SSMS database Views listing, and when we look for that view and try to use it via select * from USRAPP.myview2 we get the error.
    Msg 208, Level 16, State 1, Line 2
    Invalid object name 'USRAPP.myview2'.
    No one deleted it. Several of us have tried to create it and get it to remain. Trace isn't helping much at the moment.
    Any ideas?

  2. #2
    Join Date
    Dec 2010
    Posts
    6

    disappearing view is in sql server 2008

    This is a sql 2008 instance. If I move fast, before the "view disappears" we can get 113,208 rows in 19 seconds. The view works. Then, the view is gone. We ran a profiler trace - nothing other than create & my select. View is gone. sigh.

  3. #3
    Join Date
    Dec 2010
    Posts
    6

    disappearing view - more things we tried

    created view as dbo.myview2 == works -stays resident completely
    created view as dbo.myview3 & then sp_changedobjectowner to client schema and it worked - stays resident
    took the 'sticking' dbo.myview' & changed owner to client schema & it disappears.
    Is this a dynamic user view? how can we tell?

    So, it must be something bad or invalid or resident in some system catalog table that makes the USRAPP.myview become invalid, and it is quickly removed, unseen by us, profiler, etcetera.

    This is a 3rd party vendor app, so we can't change their view name on them to make it stick.

    I don't find anything in any of the obvious system views. Nothing under the "offending-disappearing" view name:

    select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME like 'myvie%'
    select * from sys.views where name like 'myvie%'
    select * from sys.all_views where name like 'myvie%'
    select * from INFORMATION_SCHEMA.TABLES where table_name like 'myvie%'

    puzzled and pondering still.....

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is not something being initiated by SQL Server or its catalogs. This is a glitch in the vendor application. I suspect that the application drops and recreates the view on the fly, but the vendor did not hardcode the 'dbo' schema into their drop statement, so it acts on the current user's schema.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Dec 2010
    Posts
    6

    Smile disappearing view is in sql server 2008 SOLVED

    Solved!
    We found that some client-vendor scripts were concatenated by clients. There was a missing 'go' at the end of one of them, and the next script dropped this view and created it (now one large stored proc instead of two.) The database compare we performed against their development and their production databases showed same error introduced, but one of their developers ran the vendor script separately, and cleanly created separate scripts. But still had that one proc with both activities in the same script. There was no development fully-clean (the way the vendor intended the procs to appear).

    Now, in production, sure enough, that function #1 was rarely run, but just enough that when it went out to do one particular function it dropped our USR1.myview.

    Only someone knowing the functionality of their application in depth could identify what was happening whenever they ran a particular set of code.

    SQL Server is stable. We thought that, but we wondered if we had corrupted something in system catalogs.

    Recognizing, now, that it was a procedural difference between the way the 35 vendor scripts were run (one at a time by developers) and the script they provided to the DBAs to execute (one concatenated, long script). Yet, after they created the individual runs, they then tested their concatenation script, introducing the error of the view #1 with the drop in it. Thus, their dev script contained an old original USR1.myview and the bad USR1.myview#1 with drop. Thus, the SQL Compare was equivalent except the USR1.myview existed in development with 'older timestamp' from first run of vendor scripts (left over). In their testing in development the two developers just never executed the bit of functionality that would have dropped their USR1.myview.

    We thought there could be a drop, but it was not evident in our retry method. We didn't take into account that this was an active production database, and there were lots of users hitting lots of code, and this procedure was likely to get dropped via that section of code.

    Nor did we know that the method used to create these objects differed substantially between the way the developers executed the code, and the script provided to the DBA team.

    Thanks everyone for suggestions. Very frustrating for our team and the clients.

    It was the developer who recognized the code behavior last night. He temporarily has db_owner on their production database (yeah, we know) but it was just to assist with this migration upgrade effort.

  6. #6
    Join Date
    Dec 2010
    Posts
    6

    next time - script out stored procs and search for drop

    It also occurred to me (now) that I could have scripted out all of the stored procedures into one file (or individual files) and searched for 'if exists' or 'drop ' and seen if (where!) this drop was happening. That way, we would not have had to rely on the knowledge of the application usage, but been able to ask the developer & vendor if the following x, y, and z stored procedures were supposed to be dropping this view? lesson learned.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The DROP VIEW still should have shown up in SQL Profiler.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2010
    Posts
    6

    dropped user view not in profiler

    I'm going to try to recreate this tomorrow. We directed the profiler results to a sql table yesterday while we were trying to figure out what was happening, and I performed a search for 'drop' in the Text column. No results found in the profile results table.

    This was about a 2 minute start of the profiler when I ran the create, a short select using the view. Then, when I noted the view was gone, we stopped the profile run.

    My coworker defined the profiler parameters, and he's quite familiar with profiler so I think we would have captured something with his settings.

Posting Permissions

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