Solved

Msg 102, level 15, state1, Line 2

Posted on 2010-11-29
8
2,179 Views
Last Modified: 2013-11-05
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
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Accepted Solution

by:
Som Tripathi earned 500 total points
ID: 34234293
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
 
LVL 10

Expert Comment

by:Humpdy
ID: 34234312
you are trying to set a sql 2005 express database to compatibility of 100, which is sql 2008, which you can't do.
0
 
LVL 5

Expert Comment

by:adamsjs
ID: 34234363
Compatibility level 100 is SQL Server 2008 compatibility level.  90 is the highest support level on SQL Server 2005.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Som Tripathi
ID: 34234367
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
 

Author Comment

by:gs79
ID: 34234392
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
 

Author Comment

by:gs79
ID: 34234406
@sommerville

still the same error with compatibility level set to 90

thanks
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34234419
U can't use this query in 2005 . Delete it
0
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 34234446
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

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question