Results 1 to 6 of 6

Thread: Code Erroring

  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Code Erroring

    I have a script that creates a temp table of all views that contain certain information. Well this worked great, UNTIL last week when several databases were disconnected but the view associated with that database was left. So now I am left with a procedure that produces error after error bc the database no longer exists. Is there a way to tweak my current syntax to ignore or skip if the source database does not exist so that I still get the desired outcome?
    Code:
    CREATE TABLE #Views (ID INT IDENTITY NOT NULL, TableNames VARCHAR(1000))
    CREATE TABLE #FinalResults (TableNames VARCHAR(1000))
    INSERT INTO #Views (TableNames)
    SELECT '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']' TableName
    FROM INFORMATION_SCHEMA.COLUMNS C
    JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG   AND
    C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
    C.TABLE_NAME = T.TABLE_NAME
    WHERE T.TABLE_TYPE = 'View'
    AND COLUMN_NAME = 'State'
    GROUP BY '['+C.TABLE_CATALOG+'].['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']'
    DECLARE @N INT, @Str NVARCHAR(2000), @TableName NVARCHAR(2000), @MAXID INT
    SET @N = 1
    SELECT @MAXID = MAX(ID)
    FROM #Views
    WHILE (@N<=@MAXID)
    BEGIN
                SELECT @TableName = TableNames
                FROM #Views
                WHERE ID = @N
                SET @Str =
                ' INSERT INTO #FinalResults(TableNames)
                SELECT TOP 1 '''+@TableName+'''
                FROM '+@TableName+'
                WHERE EXISTS(SELECT 1 FROM '+@TableName+' WHERE [State] LIKE ''%AL%'') '
                EXEC SP_EXECUTESQL @Str
                SET @N = @N + 1
    END
    SELECT *
    FROM #FinalResults
    DROP TABLE #Views, #FinalResults

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you saying objects in dropped databases are still showing up in the system catalogs?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by blindman View Post
    Are you saying objects in dropped databases are still showing up in the system catalogs?
    I believe so, the actual database has been dropped, but the view which exists in a different database is still 'existing'.

    For example all the data exists on FireFox1, but the actual Master_FF table has been dropped, however I am trying to query the view that exists on LL so I get an error. Does that make more sense as to what my issue is?

    Server Name: FireFox1
    Database Name: FF
    Table Name: Master_FF
    Database Name: LL
    View Name: ViewOfMaster_FF

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So the item that the view is referencing no longer exists?

    E.g.
    Code:
    USE db1
    GO
    
    CREATE VIEW foo
      AS
    SELECT stuff
    FROM   db2.dbo.bar
    GO
    
    DROP DATABASE db2
    GO
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by gvee View Post
    So the item that the view is referencing no longer exists?
    Yes! Glad you were able to understand me

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The simplest solution might be to use TRY...CATCH: http://msdn.microsoft.com/en-us/library/ms175976.aspx
    George
    Home | Blog

Posting Permissions

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