February 25, 2011 at 4:39 pm
We were trying to generate a script to create our database and it fails because of the order it is trying to generate 2 views. View A is used to create View B. And it is trying to create view B first! We figured out that if you view the dependancies for the first view you get a bunch of tables, but then when you view the dependancies for the second view you get nothing. Is there a reason this is happening?
I tried to replicate the issue with some fake test data but it does map the dependancies correctly. It just seems to be with my views which are based on like 20 tables and the views are based on views being unioned.
Has this ever happened to anyone? I can't really replicate the issue with sample data, but I'll provide my sample data below. It works in this example, but in what would be my View_second, it doesn't so any dependancies at all..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Dropping table if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
BEGIN DROP TABLE [dbo].[MyTable] END
CREATE TABLE [dbo].[MyTable]
(
ID INT IDENTITY(1,1),
SomeNum INT,
SomeChar NVARCHAR(1),
SomeDate DATETIME
)
INSERT INTO [dbo].[MyTable] (SomeNum,SomeChar,SomeDate)
SELECT 1,'a','1900-01-01' UNION ALL
SELECT 2,'a','1925-01-01' UNION ALL
SELECT 3,'b','1945-01-01' UNION ALL
SELECT 4,'b','1986-01-01' UNION ALL
SELECT 5,'b','1918-01-01' UNION ALL
SELECT 6,'c','1926-01-01'
GO
-- Conditional drop
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_First]'))
BEGIN DROP VIEW [dbo].[View_First] END
GO
CREATE VIEW [dbo].[View_First]
AS
SELECT
SomeChar,
[TotalInt] = SUM(SomeNum),
[MaxDate] = MAX(SomeDate)
FROM
[dbo].[MyTable]
GROUP BY
SomeChar
GO
-- Conditional drop
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_Second]'))
BEGIN DROP VIEW [dbo].[View_Second] END
GO
CREATE VIEW [dbo].[View_Second]
AS
SELECT
SomeChar,
TotalInt,
MaxDate
FROM
[dbo].[View_First]
WHERE
MaxDate > '1930-01-01'
GO
Also, here is clean up code:
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_Second]'))
BEGIN DROP VIEW [dbo].[View_Second] END
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_First]'))
BEGIN DROP VIEW [dbo].[View_First] END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
BEGIN DROP TABLE [dbo].[MyTable] END
GO
February 25, 2011 at 4:58 pm
Ugh.. I should really search more before I ask this sorta question. Turns out it had something to do with ALTERing the views after they are made and moving around things that could break the linking of how the dependancies were first set out. You can fix those dependencies for views using the following SQL Sever stored proc:
sp_refreshview <viewname>
I devised a fast and dirty way to refresh all of my views using a WHILE loop. Probably bad practise but I was in a rush. Heres my solution if anyone is curious.
DECLARE @AllViews TABLE
(
ID INT IDENTITY(1,1),
ViewName NVARCHAR(100)
)
INSERT INTO @AllViews (ViewName)
SELECT name FROM sys.views
DECLARE @Counter INT, @CounterMax INT, @ViewName NVARCHAR(100)
SET @Counter = 1
SET @CounterMax = (SELECT MAX(ID) FROM @AllViews)
WHILE @Counter <= @CounterMax
BEGIN
SET @ViewName = (SELECT TOP 1 ViewName FROM @AllViews WHERE ID = @Counter)
EXEC sp_refreshview @ViewName
SET @Counter = @Counter+1
PRINT 'Refreshing: ' + @ViewName
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply