CREATE VIEW - wrong name
olesa
Posts: 3
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
/****** 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
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?
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.