Create database syntax error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Create database syntax error

Author

Topic

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-09-01 : 05:04:16

I am trying to create a database and I manage to get the code right for dropping the database if it already exists and then createing the data file and log file. After this I try to set some options for the database with the following code

ALTER DATABASE MyDataWarehouse
SET RECOVERY SIMPLE,
ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
QUOTED_IDENTIFIER ON,
NUMERIC_ROUNDABORT OFF,
PAGE_VERIFY CHECKSUM,
ALLOW_SNAPSHOT_ISOLATION ON;
GO

But I get an error saying Incorrect syntax near 'CHECKSUM'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 05:20:35

Is PAGE_VERIFY a valid database option? I didn't heard of it? What does it do?

Harsh Athalye
India.
"Nothing is Impossible"

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-09-01 : 05:28:48

I am pretty new to SQL Server and found this code on the web. I am not sure exactly what it does but I found this explanation,

PAGE_VERIFY specifies three options to discover incomplete I/O transactions caused by disk I/O errors.

I have just taken this piece of code out of the script and it works fine now.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:08:44

Where did you find the code? Is the code for MS SQL Server?

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-01 : 10:04:59

Not SQL2k at any rate. From BOL:

quote:

Syntax
ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

< filespec > ::=

( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )

< optionspec > ::=

< state_option >
| < cursor_option >
| < auto_option >
| < sql_option >
| < recovery_option >

< state_option > ::=
{ SINGLE_USER | RESTRICTED_USER | MULTI_USER }
| { OFFLINE | ONLINE }
| { READ_ONLY | READ_WRITE }

< termination > ::=
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

< cursor_option > ::=
CURSOR_CLOSE_ON_COMMIT { ON | OFF }
| CURSOR_DEFAULT { LOCAL | GLOBAL }

< auto_option > ::=
AUTO_CLOSE { ON | OFF }
| AUTO_CREATE_STATISTICS { ON | OFF }
| AUTO_SHRINK { ON | OFF }
| AUTO_UPDATE_STATISTICS { ON | OFF }

< sql_option > ::=
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
| CONCAT_NULL_YIELDS_NULL { ON | OFF }
| NUMERIC_ROUNDABORT { ON | OFF }
| QUOTED_IDENTIFIER { ON | OFF }
| RECURSIVE_TRIGGERS { ON | OFF }

< recovery_option > ::=
RECOVERY { FULL | BULK_LOGGED | SIMPLE }
| TORN_PAGE_DETECTION { ON | OFF }

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources