?
Solved

Msg 102, level 15, state1, Line 2

Posted on 2010-11-29
8
Medium Priority
?
2,246 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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