Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2425
  • Last Modified:

Msg 102, level 15, state1, Line 2

SQL Server 2005 express, windows 32 bit xp

I am trying to execute a sql script to create a acme_pos database. I get MSG 102, level 15, state 1 line 2 error. Incorrect syntax error near '100' and near 'HONOR_BROKER_PRIORITY'

So I executed the two lines near where there isa syntax error and i got the same error

ALTER DATABASE [ACME_POS] SET COMPATIBILITY_LEVEL  = 100
GO

msg level 15, state 1, line 2
incorrect syntax near '100'


ALTER DATABASE [ACME_POS] SET HONOR_BROKER_PRIORITY OFF
GO
msg level 15, state 1, line 2
incorrect syntax near 'HONOR_BROKER_PRIORITY'


cant figure out what the syntax error. There are other lines similar to the above syntax and they compile fine. For example the below line compiles fine

ALTER DATABASE [ACME_POS] SET ANSI_PADDING OFF
GO

Please Advice I am very new to sql server. I am setting this database in SQL server so that I can use this as a non-oracle database as the source for my data warehousing exercise I am working on as outlined in a text book

Attached here is the complete code.

Thanks
USE [master]
GO


CREATE DATABASE [ACME_POS] ON  PRIMARY 
( NAME = 'ACME_POS', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ACME_POS.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = 'ACME_POS_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\\ACME_POS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [ACME_POS] SET COMPATIBILITY_LEVEL  = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ACME_POS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [ACME_POS] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [ACME_POS] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [ACME_POS] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [ACME_POS] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [ACME_POS] SET ARITHABORT OFF 
GO

ALTER DATABASE [ACME_POS] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [ACME_POS] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [ACME_POS] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [ACME_POS] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [ACME_POS] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [ACME_POS] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [ACME_POS] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [ACME_POS] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [ACME_POS] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [ACME_POS] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [ACME_POS] SET  DISABLE_BROKER 
GO

ALTER DATABASE [ACME_POS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [ACME_POS] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [ACME_POS] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [ACME_POS] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [ACME_POS] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [ACME_POS] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [ACME_POS] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [ACME_POS] SET  READ_WRITE 
GO

ALTER DATABASE [ACME_POS] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [ACME_POS] SET  MULTI_USER 
GO

ALTER DATABASE [ACME_POS] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [ACME_POS] SET DB_CHAINING OFF 
GO

Open in new window

0
gs79
Asked:
gs79
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Som TripathiDatabase AdministratorCommented:
It seems you are running a SQL Server 2008 statement in older version such as SQL Server 2005 or SQL Server 2000.

Please remove this line - this statement is not useful for older version. Database creation will not be affected with this.
0
 
HumpdyCommented:
you are trying to set a sql 2005 express database to compatibility of 100, which is sql 2008, which you can't do.
0
 
adamsjsCommented:
Compatibility level 100 is SQL Server 2008 compatibility level.  90 is the highest support level on SQL Server 2005.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Som TripathiDatabase AdministratorCommented:
Modify
Line#11
ALTER DATABASE [ACME_POS] SET COMPATIBILITY_LEVEL  = 90
GO

Remove this line -
Line#86
ALTER DATABASE [ACME_POS] SET HONOR_BROKER_PRIORITY OFF
GO

0
 
gs79Author Commented:
Thank you for your responses..

so it ok to remove the two lines from the script that are causing problems ie

ALTER DATABASE [ACME_POS] SET COMPATIBILITY_LEVEL  = 100
GO

and


ALTER DATABASE [ACME_POS] SET HONOR_BROKER_PRIORITY OFF
GO

Seem like execution of the script has already created acme_pos.

 Hope I can ignore the compile error of the above 2 lines?

 is there a sql express 2008 available to run on windows xp sp3 32 bit?

please let me know..

Thanks,
gs
0
 
gs79Author Commented:
@sommerville

still the same error with compatibility level set to 90

thanks
0
 
HumpdyCommented:
U can't use this query in 2005 . Delete it
0
 
Som TripathiDatabase AdministratorCommented:
Please use -

EXEC dbo.sp_dbcmptlevel @dbname=N'somdb', @new_cmptlevel=90

instead of -

ALTER DATABASE [ACME_POS] SET COMPATIBILITY_LEVEL  = 100
GO

However, if database is already there - Compatibility will be 90 itself.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now