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 15 total)
You must be logged in to reply to this topic. Login to reply