DDL not executing properly inside of an IF NOT EXISTS statement on SQL Server 2005

Question

I have a fairly complex database that needs to be deployed to a variety of servers which may or may not potentially have existing parts of the DB already implemented on it. To work around this contingency I have setup the following test:

USE [testDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wcSites]') AND type in (N'U'))
begin
    CREATE TABLE [dbo].[wcSites](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [siteCSS] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [masterTemplate] [int] NULL,
     [errorPage] [int] NULL,
     [homePage] [int] NULL,
     [addressProduction] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [addressTest] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [routeHandler] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [publish] [bit] NOT NULL CONSTRAINT [DF_wcSites_publish]  DEFAULT ((0)),
     [publicAccess] [bit] NOT NULL CONSTRAINT [DF_wcSites_publicAccess]  DEFAULT ((1)),
     [siteAccessPermission] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [contentOwner] [int] NULL,
     [navStyle] [int] NULL,
     [incScripts] [int] NULL,
     [boxW] [int] NULL,
     [boxH] [int] NULL,
     [columns] [int] NULL,
     [rows] [int] NULL,
     CONSTRAINT [PK_wcSites] PRIMARY KEY CLUSTERED 
    (
     [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [IX_wcSites_Unique_Address] UNIQUE NONCLUSTERED 
    (
     [addressProduction] ASC,
     [addressTest] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ensure unique addresses in the addressProduction, addressTest fields' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'wcSites', @level2type=N'CONSTRAINT',@level2name=N'IX_wcSites_Unique_Address'
end

And the end result is always:

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'PRIMARY'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'end'.

If I test the IF statement, it works correctly:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wcSites]') AND type in (N'U'))
begin
    PRINT 'Table does not exist'
end

And likewise when I test the CREATE TABLE script. But when I put the CREATE TABLE script inside of the begin..end block it fails every time. And I have the problem across the board, almost every table that uses this method fails.

Answer 1

Try removing the 'go' statements within the begin..end block, see if that helps.

Answer 2

I'm a bit hazy on the SQL Server syntax but doesn't the GO statement try to execute the Block? What happens if you replace the go with a semi-colon?

Also, the Set Ansi_padding off being in the If block while the set Ansi_padding on is outside looks odd to me, are you sure thats correct?