amrelgarhy81
asked on
Creat Database using .sql script file
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
/****** 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\StoreBoo
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].[Account
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]'
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].[DailyDe
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].[Expense
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].[ExtraIn
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].[Purchas
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].[Purchas
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
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]'
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].[TransDe
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]'
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
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