Creating a database installer : The Official Microsoft ASP.NET Forums

Reply

Creating a database installer

Oct 14, 2010 12:28 PM|LINK

Hi, I have been reading the below article and I have got it to work.

http://msdn.microsoft.com/en-us/library/49b92ztk.aspx

The problem is when I use my database script file it throws an error 1001 saying error next to keyword 'GO'. But if i take the exact same script and run it in SQL Management Studio it works fine.

Try this small script, this is what I am using and its giving me an error, An above is a lovely article to creating a database from microsoft.

CREATE TABLE [dbo].[Category](
	[CatID] [int] IDENTITY(1,1) NOT NULL,
	[CatName] [nvarchar](max) NULL,
	[CatDesc] [nvarchar](max) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
	[CatID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO
/****** Object:  Table [dbo].[Customer]    Script Date: 10/14/2010 09:52:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
	[AccNo] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[Name] [nvarchar](max) NULL,
	[Surname] [nvarchar](max) NULL,
	[ID_No] [nvarchar](max) NULL,
	[Address] [nvarchar](max) NULL,
	[Telephone] [nvarchar](max) NULL,
	[Mobile] [nvarchar](max) NULL,
	[Fax] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[Notes] [nvarchar](max) NULL,
	[Balance] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
	[AccNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks

Remember to click “Mark as Answer” on the post, if it helps you. Because It helps others to find the solution

Reply

ignatandrei

All-Star

137716 Points

22159 Posts

Moderator

MVP

Re: Creating a database installer

Oct 14, 2010 02:12 PM|LINK

load all text, separate the script(use Split function) by GO and execute each portion ( this way do SSMS too, I think)

My .NET blog : http://msprogrammer.serviciipeweb.ro/ |2012 Programmer tools

Reply

Re: Creating a database installer

Oct 19, 2010 01:22 PM|LINK

Hi,

Please have a note that:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

from http://msdn.microsoft.com/en-us/library/ms188037.aspx

So, I suggest you just remove all "Go" in this script to have another try.

Best Regards,
Shengqing Yang

Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact msdnmg@microsoft.com.
Microsoft One Code Framework

Edited by Shengqing Yang - MSFT on Oct 19, 2010 01:24 PM

Reply

Re: Creating a database installer

Oct 20, 2010 08:48 AM|LINK

I have removed the GO and it does create the database now and the tables but it fails at the stored procedures, it says "Create/Alter procedure needs to be first" Heres my script

CREATE TABLE [dbo].[Category](
	[CatID] [int] IDENTITY(1,1) NOT NULL,
	[CatName] [nvarchar](max) NULL,
	[CatDesc] [nvarchar](max) NULL
	)




CREATE TABLE [dbo].[Customer](
	[AccNo] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[Name] [nvarchar](max) NULL,
	[Surname] [nvarchar](max) NULL,
	[ID_No] [nvarchar](max) NULL,
	[Address] [nvarchar](max) NULL,
	[Telephone] [nvarchar](max) NULL,
	[Mobile] [nvarchar](max) NULL,
	[Fax] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[Notes] [nvarchar](max) NULL,
	[Balance] [decimal](18, 2) NULL)


CREATE TABLE [dbo].[Invoice](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Quote_Ref] [int] NULL,
	[paid] [int] NULL,
	[paidAmmount] [decimal](18, 2) NULL,
	[DateCreated] [nvarchar](max) NULL)




CREATE TABLE [dbo].[Log](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Page] [nvarchar](max) NULL,
	[Error] [nvarchar](max) NULL,
	[Date] [nvarchar](max) NULL)


CREATE TABLE [dbo].[Products](
	[ProdID] [int] IDENTITY(1,1) NOT NULL,
	[CatID] [int] NULL,
	[ProdName] [nvarchar](max) NULL,
	[ProdDesc] [nvarchar](max) NULL,
	[Unit] [int] NULL,
	[HPrice] [decimal](18, 2) NULL,
	[CPrice] [decimal](18, 2) NULL)


CREATE TABLE [dbo].[Quote_Header](
	[Quote_Ref] [int] IDENTITY(1,1) NOT NULL,
	[AccNo] [int] NULL,
	[SDate] [nvarchar](max) NULL,
	[EDate] [nvarchar](max) NULL,
	[Checked] [int] NULL,
	[WebOrder] [int] NOT NULL,
	[UDate] [nvarchar](max) NULL,
	[TotalPrice] [decimal](18, 2) NULL,
	[Status] [nvarchar](max) NULL,
	[InvoiceID] [int] NULL,
	[DateCreated] [nvarchar](max) NULL)


CREATE TABLE [dbo].[Quote_Lines](
	[Quote_Line_ID] [int] IDENTITY(1,1) NOT NULL,
	[Quote_Ref] [int] NULL,
	[Prod_ID] [int] NULL,
	[ProdName] [nvarchar](max) NULL,
	[Qty] [int] NULL,
	[LineTotal] [decimal](18, 2) NULL)


CREATE TABLE [dbo].[Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](max) NULL,
	[Password] [nvarchar](max) NULL,
	[Code] [nvarchar](max) NULL,
	[Alive] [int] NULL,
	[UserGroup] [int] NULL)


CREATE procedure insert_call_in_user
(
	@mail nvarchar(max),
	@pass nvarchar(max),
	@code nvarchar(max),
	@alive int,
	@group int
)


as


insert into Users (Email, Password, Code, Alive, UserGroup)
values (@mail, @pass, @code, @alive, @group)SELECT @@IDENTITY


Insert into Users (Email,Password,Code,Alive,Usergroup) values ('htmc@fused.co.za','YQ==', 'admin', 1, 2)

Remember to click “Mark as Answer” on the post, if it helps you. Because It helps others to find the solution

Reply

Re: Creating a database installer

Oct 20, 2010 08:59 AM|LINK

Hi,

You may write the sp to another block of script instead of process it with t-sql together.

CREATE procedure insert_call_in_user
(
    @mail nvarchar(max),
    @pass nvarchar(max),
    @code nvarchar(max),
    @alive int,
    @group int
)
  

as
  

insert into Users (Email, Password, Code, Alive, UserGroup)
values (@mail, @pass, @code, @alive, @group)SELECT @@IDENTITY
  

Insert into Users (Email,Password,Code,Alive,Usergroup) values ('htmc@fused.co.za','YQ==', 'admin', 1, 2)

Best Regards,
Shengqing Yang

Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact msdnmg@microsoft.com.
Microsoft One Code Framework

Reply

Re: Creating a database installer

Oct 20, 2010 09:51 AM|LINK

I am not sure what you mean, I need 1 installer to create the database and tables and sp's. I have used the walkthrough that microsoft provides an that is working, but it does not work with stored procedures, I have 70 stored procedures!

I need help!

Remember to click “Mark as Answer” on the post, if it helps you. Because It helps others to find the solution