Solved

Msg 102, level 15, state1, Line 2

Posted on 2010-11-29
8
2,110 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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 …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

867 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

21 Experts available now in Live!

Get 1:1 Help Now