CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32808

How can I continue past an error in a SQL script to execute other statements in the batch?

I am using the New Query window in Visual Studio 2012 Server Explorer. Here is the script I am trying to execute:

ALTER TABLE [dbo].[webpages_UsersInRoles] DROP CONSTRAINT fk_UserId;
ALTER TABLE [dbo].[webpages_UsersInRoles] DROP CONSTRAINT fk_RoleId;

DROP TABLE [dbo].[ExtraUserInformation];
DROP TABLE [dbo].[UserProfile];
DROP TABLE [dbo].[webpages_Membership];
DROP TABLE [dbo].[webpages_OAuthMembership];
DROP TABLE [dbo].[webpages_Roles];
DROP TABLE [dbo].[webpages_UsersInRoles];

CREATE TABLE [dbo].[ExtraUserInformation] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [UserId]   INT            NOT NULL,
    [FullName] NVARCHAR (MAX) NULL,
    [Link]     NVARCHAR (MAX) NULL,
    [Verified] BIT            NULL,
    CONSTRAINT [PK_dbo.ExtraUserInformation] PRIMARY KEY CLUSTERED ([Id] ASC)
);

There is currently no webpages_UsersInRoles table and it gives me an error message saying:

Msg 4902, Level 16, State 1, Line 5
Cannot find the object "dbo.webpages_UsersInRoles" because it does not exist or you do not have permissions.

After this message the script seems to stop and it fails to create the other tables after this. Is there away that I can make the script continue after it passed the expected error?

Upvotes: 1

Views: 2187

Answers (2)

Jake H
Jake H

Reputation: 1740

There are a few options here, its really a matter of what you think is right for your script.

  • You can put statements in try/catch blocks, and just swallow the error.
  • You can do what SMO does when it generates T-SQL and wrap your statements in IF statements that ensure the object in question exists.
  • You should be able to seperate out your 'sections' with GO statements, since these are sent as individual batches to the database.

Upvotes: 1

Richard
Richard

Reputation: 30628

Better than continuing past the error is to test for the failure condition in the script, for example only dropping a table if it exists.

Instead of:

DROP TABLE [dbo].[webpages_UsersInRoles];

try:

IF OBJECT_ID('dbo.webpages_UsersInRoles', 'U') IS NOT NULL
  DROP TABLE dbo.webpages_UsersInRoles

(from https://stackoverflow.com/a/7887033/163495)

Upvotes: 1

Related Questions