CREATE VIEW - wrong name

olesaolesa Posts: 3
edited February 4, 2007 7:01PM in SQL Toolkit Previous Versions
I have two very similar views. Scripted by Management Studio they are;

/****** Object: View [dbo].[vwEpisodeFile] Script Date: 02/03/2007 23:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwEpisodeFile]
AS
SELECT E.ShowID, S.Title AS ShowTitle, E.Season, E.Episode, E.Title, E.AirDate, E.Summary, E.Rating, EF.DoubleEpisode, EF.Quality, EF.DateAdded,
EF.zOliver, EF.zCarina, EF.Export, EF.Archived, EF.Filename
FROM dbo.Episode AS E INNER JOIN
dbo.Show AS S ON E.ShowID = S.ID INNER JOIN
dbo.EpisodeFile AS EF ON EF.ShowID = E.ShowID AND EF.Season = E.Season AND EF.Episode = E.Episode


/****** Object: View [dbo].[vwEpisodeInfo] Script Date: 02/03/2007 23:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwEpisodeInfo]
AS
SELECT E.ShowID, S.Title AS ShowTitle, E.Season, E.Episode, E.Title, E.AirDate, E.Summary, E.Rating, EF.DoubleEpisode, EF.Quality, EF.DateAdded,
EF.zOliver, EF.zCarina, EF.Export, EF.Archived, EF.Filename
FROM dbo.Episode AS E INNER JOIN
dbo.Show AS S ON E.ShowID = S.ID LEFT JOIN
dbo.EpisodeFile AS EF ON EF.ShowID = E.ShowID AND EF.Season = E.Season AND EF.Episode = E.Episode


The scripts generated by SQL Compare are;

PRINT N'Creating [dbo].[vwEpisodeFile]'
GO
CREATE VIEW dbo.vwEpisodeInfo
AS
SELECT E.ShowID, S.Title AS ShowTitle, E.Season, E.Episode, E.Title, E.AirDate, E.Summary, E.Rating, EF.DoubleEpisode, EF.Quality, EF.DateAdded,
EF.zOliver, EF.zCarina, EF.Export, EF.Archived, EF.Filename
FROM dbo.Episode AS E INNER JOIN
dbo.Show AS S ON E.ShowID = S.ID INNER JOIN
dbo.EpisodeFile AS EF ON EF.ShowID = E.ShowID AND EF.Season = E.Season AND EF.Episode = E.Episode


PRINT N'Creating [dbo].[vwEpisodeInfo]'
GO
CREATE VIEW [dbo].[vwEpisodeInfo]
AS
SELECT E.ShowID, S.Title AS ShowTitle, E.Season, E.Episode, E.Title, E.AirDate, E.Summary, E.Rating, EF.DoubleEpisode, EF.Quality, EF.DateAdded,
EF.zOliver, EF.zCarina, EF.Export, EF.Archived, EF.Filename
FROM dbo.Episode AS E INNER JOIN
dbo.Show AS S ON E.ShowID = S.ID LEFT JOIN
dbo.EpisodeFile AS EF ON EF.ShowID = E.ShowID AND EF.Season = E.Season AND EF.Episode = E.Episode


In other words, the script to create vwEpisodeFile is scripted using the name of another view.
PRINT N'Creating [dbo].[vwEpisodeFile]'
CREATE VIEW dbo.vwEpisodeInfo

Comments

  • I'm comparing against an empty database.

    The options I use are
    C.Options.Default | C.Options.IgnoreExtendedProperties | C.Options.IgnoreComments

    The exact same scripts are generated by running SQL Compare (the UI app) using default options.

    Can anyone help?
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    This can happen if you rename a view, for instance in Management Studio or by executing sp_rename. When a view is renamed in this way, SQL Server updates its' system tables, but not the text of the view stored in the syscomments table. This is why SQL Compare echoes the table name correctly, but creates it under the old name.

    Sory to say the only workaround for you is to script the view, drop it, and re-create it.
Sign In or Register to comment.