Solved

Creat Database using .sql script file

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Exception in Log4Net 1 20
Expando 4 36
Why, and when, to use Windows Workflow 1 20
Code works but how can I download the file? 20 47
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

13 Experts available now in Live!

Get 1:1 Help Now