Solved

Msg 102, level 15, state1, Line 2

Posted on 2010-11-29
8
2,080 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now