Solved

Creat Database using .sql script file

Posted on 2006-11-23
2
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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