July 11, 2011 at 4:19 pm
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
July 11, 2011 at 4:27 pm
Looks fine. Try compiling all the code that comes before that line.
July 11, 2011 at 4:47 pm
Thanks for quick response. I ran all the code just before this line and it completes successfully.
July 11, 2011 at 5:00 pm
Works fine here.
What's the error?
Can you post the full script?
July 11, 2011 at 5:01 pm
What version of sql server are you running this on?
July 11, 2011 at 5:29 pm
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
---------------------------------------------------------------------------
July 11, 2011 at 5:59 pm
It works on my system (express 2008 R2). What error are you getting?
July 11, 2011 at 7:08 pm
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'."
July 11, 2011 at 8:01 pm
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.
July 11, 2011 at 8:43 pm
Is SQL Server 9.0.5000 the same as SQL Server Express 2008 R2? I think that's what I got with VS 2010.
July 11, 2011 at 10:26 pm
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.
July 12, 2011 at 2:29 am
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.
July 12, 2011 at 9:07 am
So what would it be for 2008 R2?
July 12, 2011 at 9:26 am
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 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy