How to execute a large SQL Server script when views are not created in dependency order? - Stack Overflow

admin管理员组

文章数量:1352011

We're dealing with a large rollout involving a significant number of SQL Server views. I was able to reduce the scope to around 80 objects, but unfortunately, Redgate SQL Compare is no longer able to generate the CREATE VIEW statements in the correct order, that is, ensuring that all dependent views are created after the objects they rely on.

As a result, running the script fails because some views are referencing others that haven't been created yet.

I'm looking for a way to run the entire script successfully, even though the views are not listed in the correct dependency order. I was hoping for some sort of setting or trick to "defer" or suppress the dependency errors temporarily (like turning off constraint checks), but as far as I understand, SQL Server doesn’t offer a built-in way to suppress errors for invalid object references during view creation.

Is there a smart way to either:

• run the script in multiple passes (e.g. try to create all views once, ignore errors, and then re-run the failed ones),
• or somehow script around the dependency problem in a clean way?

We're dealing with a large rollout involving a significant number of SQL Server views. I was able to reduce the scope to around 80 objects, but unfortunately, Redgate SQL Compare is no longer able to generate the CREATE VIEW statements in the correct order, that is, ensuring that all dependent views are created after the objects they rely on.

As a result, running the script fails because some views are referencing others that haven't been created yet.

I'm looking for a way to run the entire script successfully, even though the views are not listed in the correct dependency order. I was hoping for some sort of setting or trick to "defer" or suppress the dependency errors temporarily (like turning off constraint checks), but as far as I understand, SQL Server doesn’t offer a built-in way to suppress errors for invalid object references during view creation.

Is there a smart way to either:

• run the script in multiple passes (e.g. try to create all views once, ignore errors, and then re-run the failed ones),
• or somehow script around the dependency problem in a clean way?

• sql
• sql-server
• rollout

Share Improve this question Follow edited Apr 1 at 9:25 Mark Rotteveel 110k229229 gold badges156156 silver badges223223 bronze badges asked Apr 1 at 7:08 Walter KüchenbergWalter Küchenberg 922 bronze badges 3

• 5 SQL Server Data Tools usually solves all this stuff without problem and it's free, have you tried it – siggemannen Commented Apr 1 at 7:09
• Usable? stackoverflow/a/41678117/1322268 – shawnt00 Commented Apr 1 at 9:14
• "Redgate SQL Compare is no longer able to generate the CREATE VIEW statements in the correct order" Admittedly, I've never encountered a scenario where it has been unable to achieve this. I assume you are running the most recent version? – Thom A Commented Apr 1 at 11:16

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

I have never used redgate tools, and I believe nowadays, there are much tools (paid and also freeware) that can automate things and make our misery much easier to swallow. But since I'm an old school, I mostly write my own scripts for these scenarios. It's faster for me (given the time spent to learn new tools features).

Anyhow, you need to handle view existence so you do not re-create what is already created by using something like

IF OBJECT_ID('dbo.VIEW_NAME', 'U') NOT NULL
  CREATE VIEW VIEW_NAME AS (....)
GO 

If your script does not already have that, then you can edit your script and search and replace CREATE VIEW with CREATE OR ALTER which will create the view if not exists, and alter it if exists (which is fine in your case). (P.S. Notepad++ is your best friend)

now, you can make use of EXEC command along with a TRY/CATCH to execute the script and catch any error. You can execute it manually until you have no errors, or you can use a retry logic with some handling to automate things something like this :

CREATE TABLE #Errors
(
    [Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
    [Number] [int] NOT NULL,
    [State] [int] NOT NULL,
    [Severity] [int] NOT NULL,
    [Line] [int] NOT NULL,
    [Procedure] [nvarchar](max) NULL,
    [Message] [nvarchar](max) NULL,
    [CreatedDateTime] [datetime] DEFAULT (getdate()) NOT NULL
)   

DECLARE     
    @MAX_RETRAY INT = 3    -- maximum number of retries
,   @COUNT INT = 0         -- counter
,   @SQL NVARCHAR(MAX)    

START_LABEL: 
    BEGIN TRY
       SET @COUNT+=1
       EXEC(@SQL);
    END TRY
    BEGIN CATCH
       INSERT INTO #Errors([Number],[State],[Severity],[Line],[Procedure],[Message]) VALUES (ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE())     
                
       IF @COUNT >= @MAX_RETRAY
        GOTO END_LABEL

       GOTO START_LABEL
    END CATCH
END_LABEL: 
    SELECT * FROM #Errors

In the above example, the script will create a temp table named #Errors which will store the errors of each try. The script will try to execute the given stored commands in @SQL and catch errors, if such error happens, it will catch it, and store it in #Errors temp table, and if it reaches the retry threshold, or there is no error occurs, it will show the #Erros temp table records.

Note that this will work just fine if each create view command is handled by either If statement or CREATE OR ALTER command.

Note that no matters how big your script is, as long as you separate commands by GO sql server will execute them in batches (each GO is an sql server batch command) (it's not a T-SQL statment, but rather an SQL SERVER thingy).

Hope my old schooling helps ;)

The only way to create different, interdependent database objects in any order and ensure consistency is to use the CREATE SCHEMA macro command and incorporate all the objects that belong to the same schema. This assumes you haven't yet created this SQL schema, or you're creating one specifically for this purpose, and then transferring all the objects created in this schema into the final target schema. But this is limited to views and table and no views can have a reference to another view.

Demonstration:

CREATE SCHEMA S_ANY_ORDER

CREATE VIEW V1
AS
SELECT * FROM T0

CREATE TABLE T0
(ID INT PRIMARY KEY);

Of course you can finally transfer all these objects to another SQL schema (here dbo):

DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + N'ALTER SCHEMA dbo TRANSFER ' 
                   + QUOTENAME(s.name) + N'.' 
                   + QUOTENAME(o.name) + N';'
FROM   sys.objects AS o
       JOIN sys.schemas AS s
          ON o.schema_id = s.schema_id
WHERE  type IN ('U', 'V')
       AND s.name = 'S_ANY_ORDER';
EXEC (@SQL);

This is a standard ISO SQL feature that ir rarely known...

本文标签： How to execute a large SQL Server script when views are not created in dependency orderStack Overflow