[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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
0
amrelgarhy81
Asked:
amrelgarhy81
1 Solution
 
AgariciCommented:
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
 
amrelgarhy81Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now