Solved

Creat Database using .sql script file

Posted on 2006-11-23
2
180 Views
Last Modified: 2010-04-16
Hello,
I want to know how to creat a database using a .sql script file i have.

I tryed this code:
             SqlConnection cn = new SqlConnection();
             cn.ConnectionString = "Data Source=(local);Initial Catalog=master;Integrated Security=True";
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cn.Open();
            cmd.CommandText = @"[LONG SCRIPT TEXT]";


But it gave me this error when reach the last line:
In Many Lines: Incorrect syntax near 'GO'
Could not locate entry in sysdatabases for database 'StoreBookKeeping'. No entry found with that name. Make sure that the name is entered correctly.


StoreBookKeeping is the name of the database i will creat.

Please help me.

Thanks
0
Comment
Question by:amrelgarhy81
2 Comments
 
LVL 11

Accepted Solution

by:
Agarici earned 50 total points
ID: 18002695
most likely your script is not correct.
you could post it so we can take a look.

anyway, it should be like:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'StoreBookKeeping')
      DROP DATABASE [StoreBookKeeping]
GO

CREATE DATABASE [IxLoadDb]  ON (NAME = N'StoreBookKeeping', FILENAME = N'C:\StoreBookKeeping.MDF' , SIZE = 50, FILEGROWTH = 10%) LOG ON (NAME = N'StoreBookKeeping_Log', FILENAME = N'C:\StoreBookKeeping_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

use [StoreBookKeeping]
GO

/****** Now create the tables. ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SomeTableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SomeTableName]
GO

CREATE TABLE [dbo].[SomeTableName] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



hth,
A.
0
 
LVL 1

Author Comment

by:amrelgarhy81
ID: 18002709
I created it using the enterprise manager and used it before to creat the database and worked fine, by the way here is the script:




/****** Object:  Database StoreBookKeeping    Script Date: 11/23/2006 1:17:51 PM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'StoreBookKeeping')
      DROP DATABASE [StoreBookKeeping]
GO

CREATE DATABASE [StoreBookKeeping]  ON (NAME = N'StoreBookKeeping_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\StoreBookKeeping_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'StoreBookKeeping_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\StoreBookKeeping_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

exec sp_dboption N'StoreBookKeeping', N'autoclose', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'bulkcopy', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'trunc. log', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'torn page detection', N'true'
GO

exec sp_dboption N'StoreBookKeeping', N'read only', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'dbo use', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'single', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'autoshrink', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'ANSI null default', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'recursive triggers', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'ANSI nulls', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'concat null yields null', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'cursor close on commit', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'default to local cursor', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'quoted identifier', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'ANSI warnings', N'false'
GO

exec sp_dboption N'StoreBookKeeping', N'auto create statistics', N'true'
GO

exec sp_dboption N'StoreBookKeeping', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
      exec sp_dboption N'StoreBookKeeping', N'db chaining', N'false'
GO

use [StoreBookKeeping]
GO

/****** Object:  Table [dbo].[Accounts]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Accounts]
GO

/****** Object:  Table [dbo].[Daily]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Daily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Daily]
GO

/****** Object:  Table [dbo].[DailyDef]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DailyDef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DailyDef]
GO

/****** Object:  Table [dbo].[Expenses]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Expenses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Expenses]
GO

/****** Object:  Table [dbo].[ExtraIncome]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExtraIncome]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ExtraIncome]
GO

/****** Object:  Table [dbo].[Purchases]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Purchases]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Purchases]
GO

/****** Object:  Table [dbo].[Purchases_Vendors]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Purchases_Vendors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Purchases_Vendors]
GO

/****** Object:  Table [dbo].[Station]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Station]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Station]
GO

/****** Object:  Table [dbo].[Trans]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trans]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Trans]
GO

/****** Object:  Table [dbo].[TransDetails]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransDetails]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

/****** Object:  Table [dbo].[Vendors]    Script Date: 11/23/2006 1:17:52 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vendors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Vendors]
GO

/****** Object:  Login AMR\ASPNET    Script Date: 11/23/2006 1:17:51 PM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'AMR\ASPNET')
      exec sp_grantlogin N'AMR\ASPNET'
      exec sp_defaultdb N'AMR\ASPNET', N'master'
      exec sp_defaultlanguage N'AMR\ASPNET', N'us_english'
GO

/****** Object:  Login pda_verifacts_user    Script Date: 11/23/2006 1:17:51 PM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'pda_verifacts_user')
BEGIN
      declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'pda_verifacts', @loginlang = N'us_english'
      if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
            select @logindb = N'master'
      if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
            select @loginlang = @@language
      exec sp_addlogin N'pda_verifacts_user', null, @logindb, @loginlang
END
GO

/****** Object:  User dbo    Script Date: 11/23/2006 1:17:51 PM ******/
/****** Object:  User guest    Script Date: 11/23/2006 1:17:51 PM ******/
if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)
      EXEC sp_grantdbaccess N'guest'
GO

/****** Object:  Table [dbo].[Accounts]    Script Date: 11/23/2006 1:17:54 PM ******/
CREATE TABLE [dbo].[Accounts] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Name] [nvarchar] (50) NULL ,
      [Notes] [nvarchar] (255) NULL ,
      [DailyColumns] [nvarchar] (512) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Daily]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[Daily] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Sales] [numeric](18, 0) NULL ,
      [SalesTax] [numeric](18, 0) NULL ,
      [Pub] [numeric](18, 0) NULL ,
      [Lotto] [numeric](18, 0) NULL ,
      [Tickets] [numeric](18, 0) NULL ,
      [ExtraIncome] [numeric](18, 0) NULL ,
      [Machine] [numeric](18, 0) NULL ,
      [Money] [numeric](18, 0) NULL ,
      [MoneyTsfr] [numeric](18, 0) NULL ,
      [TotalDebit] [numeric](18, 0) NULL ,
      [Coupon] [numeric](18, 0) NULL ,
      [PubPaidOut] [numeric](18, 0) NULL ,
      [LottoCash] [numeric](18, 0) NULL ,
      [TicketsCash] [numeric](18, 0) NULL ,
      [PubCredit] [numeric](18, 0) NULL ,
      [StoreCredit] [numeric](18, 0) NULL ,
      [TotalCredit] [numeric](18, 0) NULL ,
      [Bank] [numeric](18, 0) NULL ,
      [Deposit] [numeric](18, 0) NULL ,
      [DepositCheck] [numeric](18, 0) NULL ,
      [Date] [smalldatetime] NULL ,
      [AccountId] [numeric](18, 0) NULL ,
      [Notes] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[DailyDef]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[DailyDef] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [ColumnName] [nvarchar] (255) NULL ,
      [Sign] [bit] NULL ,
      [Type] [bit] NULL ,
      [AccountId] [numeric](18, 0) NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Expenses]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[Expenses] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [VendorId] [numeric](18, 0) NULL ,
      [InvoiceNumber] [nvarchar] (50) NULL ,
      [CheckNumber] [numeric](18, 0) NULL ,
      [Amount] [numeric](18, 0) NULL ,
      [Date] [smalldatetime] NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[ExtraIncome]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[ExtraIncome] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Description] [nvarchar] (255) NULL ,
      [CheckNumber] [numeric](18, 0) NULL ,
      [Amount] [numeric](18, 0) NULL ,
      [Date] [smalldatetime] NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Purchases]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[Purchases] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [VendorId] [numeric](18, 0) NULL ,
      [InvoiceNumber] [nvarchar] (50) NULL ,
      [CheckNumber] [numeric](18, 0) NULL ,
      [Amount] [numeric](18, 0) NULL ,
      [Date] [smalldatetime] NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Purchases_Vendors]    Script Date: 11/23/2006 1:17:55 PM ******/
CREATE TABLE [dbo].[Purchases_Vendors] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Name] [nvarchar] (50) NULL ,
      [Notes] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Station]    Script Date: 11/23/2006 1:17:56 PM ******/
CREATE TABLE [dbo].[Station] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Description] [nvarchar] (255) NULL ,
      [Debit] [numeric](18, 0) NULL ,
      [Credit] [numeric](18, 0) NULL ,
      [Balance] [numeric](18, 0) NULL ,
      [Date] [smalldatetime] NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Trans]    Script Date: 11/23/2006 1:17:56 PM ******/
CREATE TABLE [dbo].[Trans] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [AccountId] [numeric](18, 0) NULL ,
      [Date] [datetime] NULL ,
      [Notes] [nvarchar] (255) NULL ,
      [TotalDebit] [numeric](18, 0) NULL ,
      [TotalCredit] [numeric](18, 0) NULL ,
      [Bank] [numeric](18, 0) NULL ,
      [Deposit] [numeric](18, 0) NULL ,
      [DepositCheck] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[TransDetails]    Script Date: 11/23/2006 1:17:56 PM ******/
CREATE TABLE [dbo].[TransDetails] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [DailyDefId] [numeric](18, 0) NULL ,
      [TransId] [numeric](18, 0) NULL ,
      [Amount] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Users]    Script Date: 11/23/2006 1:17:56 PM ******/
CREATE TABLE [dbo].[Users] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [UserName] [nvarchar] (50) NOT NULL ,
      [Password] [nvarchar] (50) NULL ,
      [RegisterDate] [smalldatetime] NULL ,
      [LastLogOn] [smalldatetime] NULL ,
      [Notes] [nvarchar] (255) NULL ,
      [AccountId] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Vendors]    Script Date: 11/23/2006 1:17:56 PM ******/
CREATE TABLE [dbo].[Vendors] (
      [Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [Name] [nvarchar] (50) NULL ,
      [Notes] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Accounts] WITH NOCHECK ADD
      CONSTRAINT [PK_Accounts] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Daily] WITH NOCHECK ADD
      CONSTRAINT [PK_Daily] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[DailyDef] WITH NOCHECK ADD
      CONSTRAINT [PK_DailyDef] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Expenses] WITH NOCHECK ADD
      CONSTRAINT [PK_Expenses] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[ExtraIncome] WITH NOCHECK ADD
      CONSTRAINT [PK_ExtraIncome] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Purchases] WITH NOCHECK ADD
      CONSTRAINT [PK_Purchases] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Purchases_Vendors] WITH NOCHECK ADD
      CONSTRAINT [PK_Purchases_Vendors] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Station] WITH NOCHECK ADD
      CONSTRAINT [PK_Station] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Trans] WITH NOCHECK ADD
      CONSTRAINT [PK_Trans] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[TransDetails] WITH NOCHECK ADD
      CONSTRAINT [PK_TransDetails] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
      CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED
      (
            [UserName]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Vendors] WITH NOCHECK ADD
      CONSTRAINT [PK_Vendors] PRIMARY KEY  CLUSTERED
      (
            [Id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Daily] ADD
      CONSTRAINT [DF_Daily_Sales] DEFAULT (0) FOR [Sales],
      CONSTRAINT [DF_Daily_SalesTax] DEFAULT (0) FOR [SalesTax],
      CONSTRAINT [DF_Daily_Pub] DEFAULT (0) FOR [Pub],
      CONSTRAINT [DF_Daily_Lotto] DEFAULT (0) FOR [Lotto],
      CONSTRAINT [DF_Daily_Tickets] DEFAULT (0) FOR [Tickets],
      CONSTRAINT [DF_Daily_ExtraIncome] DEFAULT (0) FOR [ExtraIncome],
      CONSTRAINT [DF_Daily_Machine] DEFAULT (0) FOR [Machine],
      CONSTRAINT [DF_Daily_Money] DEFAULT (0) FOR [Money],
      CONSTRAINT [DF_Daily_MoneyTsfr] DEFAULT (0) FOR [MoneyTsfr],
      CONSTRAINT [DF_Daily_TotalDebit] DEFAULT (0) FOR [TotalDebit],
      CONSTRAINT [DF_Daily_Coupon] DEFAULT (0) FOR [Coupon],
      CONSTRAINT [DF_Daily_PubPaidOut] DEFAULT (0) FOR [PubPaidOut],
      CONSTRAINT [DF_Daily_LottoCash] DEFAULT (0) FOR [LottoCash],
      CONSTRAINT [DF_Daily_TicketsCash] DEFAULT (0) FOR [TicketsCash],
      CONSTRAINT [DF_Daily_PubCredit] DEFAULT (0) FOR [PubCredit],
      CONSTRAINT [DF_Daily_StoreCredit] DEFAULT (0) FOR [StoreCredit],
      CONSTRAINT [DF_Daily_TotalCredit] DEFAULT (0) FOR [TotalCredit],
      CONSTRAINT [DF_Daily_Bank] DEFAULT (0) FOR [Bank],
      CONSTRAINT [DF_Daily_Deposit] DEFAULT (0) FOR [Deposit],
      CONSTRAINT [DF_Daily_DepositCheck] DEFAULT (0) FOR [DepositCheck]
GO

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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