I am trying to deploy my database-driven ASP.NET application to my web server. I have hosting through godaddy.com and they require that you run a script through their SQL admin Query Analyzer to create the database. After I attach the database in the Management Studio, I generate the script, copy and paste it into the QA and get the following error. Any help?
Error -2147217900
Line 18: Incorrect syntax near '('.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Students]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Students](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](20) NULL,
[LastName] [nvarchar](20) NULL,
[PhoneNumber1] [nvarchar](15) NULL,
[PhoneNumber2] [nvarchar](15) NULL,
[Instrument] [nchar](20) NULL,
[Interests] [nvarchar](50) NULL,
[Active] [bit] NULL,
[TimeID] [int] NULL,
[UserID] [nchar](20) NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lessons]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Lessons](
[LessonID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CurrentNotes] [nvarchar](250) NULL,
[FutureNotes] [nvarchar](250) NULL,
[DateTime] [datetime] NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Lessons] PRIMARY KEY CLUSTERED
(
[LessonID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IFNOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =OBJECT_ID(N'[dbo].[FK_Lessons_Students]') AND parent_object_id =OBJECT_ID(N'[dbo].[Lessons]'))
ALTER TABLE [dbo].[Lessons] WITH CHECK ADD CONSTRAINT [FK_Lessons_Students] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Lessons] CHECK CONSTRAINT [FK_Lessons_Students]
That's because you the script is generated for a SQL2005 table in Management Studio, so it contains some new elements in SQL2005 which do not exist in SQL2000. Then when you tried to execute it in Query Analyzer (which uses SQL2000 T-SQL rules), QA failed to parse some new T-SQL syntax (I see it is the WITH block following the CONSTRAINT definition here).
For more information about T-SQL enhancement in SQL2005, you can refer to:
http://msdn.microsoft.com/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp?
BTW, system schemas also have been changed in SQL2005, so if you try to refer to 'sys.objects' as in the script in SQL2000, an 'object not exist' error will be raised. For information, please refer to:
http://msdn2.microsoft.com/en-us/library/ms187997.aspx
