Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Error creating Database

Author  Topic 

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-22 : 04:33:27
I have this script for creating DB and ALTER it that gives me errors:

Script:
CREATE DATABASE [SQLDevTest]
--CONTAINMENT = NONE
ON PRIMARY
( NAME = N'SQLDevTest', FILENAME = N'C:\Databases\SQLDevTest.mdf' , SIZE = 361472KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQLDevTest_log', FILENAME = N'C:\Databases\SQLDevTest.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SQLDevTest] SET COMPATIBILITY_LEVEL = 100
GO


ALTER DATABASE [SQLDevTest] SET HONOR_BROKER_PRIORITY OFF
GO

ERRORS:
1. Directory lookup for the file "C:\Databases\SQLDevTest.mdf" failed with the operating system error 2(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

2. Incorrect syntax near '100'
3. Incorrect syntax near 'HONOR_BROKER_PRIORITY'.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-10-22 : 06:59:25
See here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5066370b-e32e-4fbd-a65b-4f4c4b997c96/incorrect-syntax-near-honorbrokerpriority

http://social.msdn.microsoft.com/forums/sqlserver/en-US/c2840814-5165-4e00-a0a9-36df521ad429/alter-database-testdb-set-compatibilitylevel-100-geting-error-incorrect-syntax-near-



SZ1
Please help me to enable me to help others!
Go to Top of Page

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-22 : 09:27:23
I am using 2012 but I'm still getting the same error
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-10-22 : 09:50:17
You may be using the latest 2012 version of SSMS but what db version are you connecting to? it may be 2005, its the db itself that needs to be version 2008, 2012.

I get the same error when trying to run the code on a 2005 db server but its fine on one of my 2012 servers.

Can you double check the db source is in actual fact a 2012 db?

SZ1
Please help me to enable me to help others!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 10:09:55
try this and post back result

SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'Your database name'
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -