Creating database error: "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '90'."

  • Hi,

    I took an existing database in SQL Server Express and had the database engine create the script that would create that database. I then changed the name of the database to be created by the script. I wanted to make a copy of the database with a different name. When I run the script, I get a syntax error at the following line:

    ALTER DATABASE [SecureSchool] SET COMPATIBILITY_LEVEL = 90

    GO

    Any idea why?

    Thanks,

    Mike

  • Looks fine. Try compiling all the code that comes before that line.

  • Thanks for quick response. I ran all the code just before this line and it completes successfully.

  • Works fine here.

    What's the error?

    Can you post the full script?

  • What version of sql server are you running this on?

  • Here is the script. Version is SQLEXPRESS (SQL Server 9.0.5000) also (SQL Server 10.50.1617) I tried both.

    ---------------------------------------------------------------------------------------------------------

    USE [master]

    GO

    /****** Object: Database [SecureSchool] Script Date: 07/11/2011 11:54:11 ******/

    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'SecureSchool')

    DROP DATABASE [SecureSchool]

    GO

    USE [master]

    GO

    /****** Object: Database [SecureSchool] Script Date: 07/11/2011 11:54:12 ******/

    CREATE DATABASE [SecureSchool] ON PRIMARY

    ( NAME = N'SecureSchool', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SecureSchool.mdf' , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

    LOG ON

    ( NAME = N'SecureSchool_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SecureSchool_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    ALTER DATABASE [SecureSchool] SET COMPATIBILITY_LEVEL = 90

    GO

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

    begin

    EXEC [SecureSchool].[dbo].[sp_fulltext_database] @action = 'enable'

    end

    GO

    ALTER DATABASE [SecureSchool] SET ANSI_NULL_DEFAULT OFF

    GO

    ALTER DATABASE [SecureSchool] SET ANSI_NULLS OFF

    GO

    ALTER DATABASE [SecureSchool] SET ANSI_PADDING OFF

    GO

    ALTER DATABASE [SecureSchool] SET ANSI_WARNINGS OFF

    GO

    ALTER DATABASE [SecureSchool] SET ARITHABORT OFF

    GO

    ALTER DATABASE [SecureSchool] SET AUTO_CLOSE ON

    GO

    ALTER DATABASE [SecureSchool] SET AUTO_CREATE_STATISTICS ON

    GO

    ALTER DATABASE [SecureSchool] SET AUTO_SHRINK OFF

    GO

    ALTER DATABASE [SecureSchool] SET AUTO_UPDATE_STATISTICS ON

    GO

    ALTER DATABASE [SecureSchool] SET CURSOR_CLOSE_ON_COMMIT OFF

    GO

    ALTER DATABASE [SecureSchool] SET CURSOR_DEFAULT GLOBAL

    GO

    ALTER DATABASE [SecureSchool] SET CONCAT_NULL_YIELDS_NULL OFF

    GO

    ALTER DATABASE [SecureSchool] SET NUMERIC_ROUNDABORT OFF

    GO

    ALTER DATABASE [SecureSchool] SET QUOTED_IDENTIFIER OFF

    GO

    ALTER DATABASE [SecureSchool] SET RECURSIVE_TRIGGERS OFF

    GO

    ALTER DATABASE [SecureSchool] SET ENABLE_BROKER

    GO

    ALTER DATABASE [SecureSchool] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    GO

    ALTER DATABASE [SecureSchool] SET DATE_CORRELATION_OPTIMIZATION OFF

    GO

    ALTER DATABASE [SecureSchool] SET TRUSTWORTHY OFF

    GO

    ALTER DATABASE [SecureSchool] SET ALLOW_SNAPSHOT_ISOLATION OFF

    GO

    ALTER DATABASE [SecureSchool] SET PARAMETERIZATION SIMPLE

    GO

    ALTER DATABASE [SecureSchool] SET READ_COMMITTED_SNAPSHOT OFF

    GO

    ALTER DATABASE [SecureSchool] SET READ_WRITE

    GO

    ALTER DATABASE [SecureSchool] SET RECOVERY SIMPLE

    GO

    ALTER DATABASE [SecureSchool] SET MULTI_USER

    GO

    ALTER DATABASE [SecureSchool] SET PAGE_VERIFY CHECKSUM

    GO

    ALTER DATABASE [SecureSchool] SET DB_CHAINING OFF

    GO

    ---------------------------------------------------------------------------

  • It works on my system (express 2008 R2). What error are you getting?

  • At:

    ALTER DATABASE [SecureSchool] SET COMPATIBILITY_LEVEL = 90

    GO

    I get the following message:

    "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '90'."

  • I don't know what else to tell you. Works fine on my 2008 R2 Machine.

    The only thing I can think of is that you are trying to run this on a sql 2000 machine or that there's something I don't see in your script.

  • Is SQL Server 9.0.5000 the same as SQL Server Express 2008 R2? I think that's what I got with VS 2010.

  • SQL Mgr Studio does not clearly label the DB engine as 2008 or R2 - it's labeled as SQL Server 10.50.1617. I had to redo the path specified. It then worked.

    Thanks for your help.

  • MikeAngelastro-571287 (7/11/2011)


    Is SQL Server 9.0.5000 the same as SQL Server Express 2008 R2? I think that's what I got with VS 2010.

    Nope that's sql 2005.

  • So what would it be for 2008 R2?

  • select @@version

    Response:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Mine is a 64Bit version

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply