Tuesday, February 14, 2012

Create database from code

Hi, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text...the debuger threw an sql exception...saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.

USE [master]
GO
/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/
CREATE DATABASE [estate_management] ON PRIMARY
( NAME = N'estate_management', FILENAME = N'D:\MSSQL\estate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'estate_management_log', FILENAME = N'D:\MSSQL\estate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[admin](
[admin_id] [int] IDENTITY(1,1) NOT NULL,
[log_in_id] [varchar](20) NOT NULL,
[password] [varchar](50) NOT NULL,
CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY],
CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED
(
[admin_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

Thanks for your time.

You'd better set the database file size to larger value, to host a copy of the model database. Except this, your script works fine when I tested it under sqlcmd, both on SQL2000 and SQL2005 the database can be created successfully.

No comments:

Post a Comment