Solved

Creat Database using .sql script file

Posted on 2006-11-23
2
195 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MailAddress in vb 4 36
How to use spatial data types in SQL Server Database project 2 43
C# Service FileSystemwatcher isse 9 46
Nested forach loop to linq 3 28
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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