|
|
Posted by David Portas on 11/15/05 08:53
"Jozef" <me@you.com> wrote in message news:Avfef.112772$S4.97277@edtnps84...
> Hello,
>
> I have an "Issue Manager" script that I would like to run on my SQL Server
> (2000). Being a novice, I'm not sure how to do this. I can't seem to
> find anything in the help file that makes sense to me, and I'm sure it's
> because my search strings suck, they only produce a single result usually,
> and always un related.
>
> I've pasted the script I'm trying to run below.
>
> Any help at all would be appreciated.
>
> Thanks!
>
>
>
>
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[issues]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[issues]
> GO
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[priorities]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[priorities]
> GO
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[responses]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[responses]
> GO
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[statuses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[statuses]
> GO
>
> if exists (select * from sysobjects where id = object_id(N'[dbo].[users]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[users]
> GO
>
> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[settings]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[settings]
> GO
>
> if exists (select * from sysobjects where id = object_id(N'[dbo].[files]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[files]
> GO
>
> CREATE TABLE [users] (
> [user_id] [int] IDENTITY (1, 1) NOT NULL ,
> [user_name] [nvarchar] (50) NULL ,
> [login] [nvarchar] (15) NULL ,
> [pass] [nvarchar] (15) NULL ,
> [email] [nvarchar] (50) NULL ,
> [security_level] [tinyint] NULL ,
> [notify_new] [int] NULL DEFAULT (0),
> [notify_original] [int] NULL DEFAULT (0),
> [notify_reassignment] [int] NULL DEFAULT (0),
> [allow_upload] [int] NULL DEFAULT (0)
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [statuses] (
> [status_id] [int] IDENTITY (1, 1) NOT NULL ,
> [status] [nvarchar] (20) NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [settings] (
> [settings_id] [int] IDENTITY (1, 1) NOT NULL ,
> [file_extensions] [nvarchar] (100) NULL ,
> [file_path] [nvarchar] (100) NULL ,
> [notify_new_from] [nvarchar] (50) NULL ,
> [notify_new_subject] [nvarchar] (100) NULL ,
> [notify_new_body] [ntext] NULL ,
> [notify_change_from] [nvarchar] (50) NULL ,
> [notify_change_subject] [nvarchar] (100) NULL ,
> [notify_change_body] [ntext] NULL,
> [upload_enabled] [tinyint] NULL DEFAULT (0)
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [responses] (
> [response_id] [int] IDENTITY (1, 1) NOT NULL ,
> [issue_id] [int] NULL ,
> [response] [ntext] NULL ,
> [user_id] [int] NULL ,
> [priority_id] [int] NOT NULL ,
> [status_id] [int] NOT NULL ,
> [version] [nvarchar] (10) NULL ,
> [approved] [tinyint] NULL ,
> [tested] [tinyint] NULL ,
> [assigned_to] [int] NULL ,
> [date_response] [smalldatetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [priorities] (
> [priority_id] [int] IDENTITY (1, 1) NOT NULL ,
> [priority_desc] [nvarchar] (15) NULL ,
> [priority_order] [int] NULL ,
> [priority_color] [nvarchar] (30) NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [issues] (
> [issue_id] [int] IDENTITY (1, 1) NOT NULL ,
> [issue_name] [nvarchar] (100) NULL ,
> [issue_desc] [ntext] NULL ,
> [user_id] [int] NULL ,
> [priority_id] [int] NOT NULL ,
> [status_id] [int] NOT NULL ,
> [version] [nvarchar] (10) NULL ,
> [approved] [tinyint] NULL ,
> [tested] [tinyint] NULL ,
> [assigned_to] [int] NULL ,
> [assigned_to_orig] [int] NULL ,
> [date_submitted] [smalldatetime] NULL ,
> [date_resolved] [smalldatetime] NULL ,
> [date_modified] [smalldatetime] NULL ,
> [modified_by] [int] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [files] (
> [file_id] [int] IDENTITY (1, 1) NOT NULL ,
> [file_name] [nvarchar] (100) NULL ,
> [issue_id] [int] NULL ,
> [date_uploaded] [smalldatetime] NULL ,
> [uploaded_by] [int] NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO [dbo].[users]([user_name], [login], [pass], [email],
> [security_level], [allow_upload])
> VALUES('Administrator', 'admin', 'admin', 'admin@company.com', 3, 0)
>
> INSERT INTO [dbo].[users]([user_name], [login], [pass], [email],
> [security_level], [allow_upload])
> VALUES('Guest', 'guest', 'guest', 'guest@company.com', 1, 0)
>
> INSERT INTO [dbo].[settings]( [file_extensions], [file_path],
> [notify_new_from], [notify_new_subject], [notify_new_body],
> [notify_change_from], [notify_change_subject], [notify_change_body],
> [upload_enabled])
> VALUES('zip,pdf,doc,rtf,gif, jpg,png,txt', 'uploads', 'admin@company.com',
> 'New Issue# {issue_no} was Submitted', 'hi {issue_receiver},
> <b>{issue_title}</b> was submitted {issue_url} by <b>{issue_poster}</b>',
> 'admin@company.com', 'Issue# {issue_no} was Changed' , 'hi
> {issue_receiver},
> <b>{issue_title}</b> was changed {issue_url} by <b>{issue_poster}</b>',0)
>
> INSERT INTO [dbo].[priorities]([priority_desc], [priority_order],
> [priority_color])
> VALUES('Highest', 10, 'red')
>
> INSERT INTO [dbo].[priorities]([priority_desc], [priority_order],
> [priority_color])
> VALUES('High', 20, 'blue')
>
> INSERT INTO [dbo].[priorities]([priority_desc], [priority_order])
> VALUES('Normal', 30)
>
> INSERT INTO [dbo].[priorities]([priority_desc], [priority_order],
> [priority_color])
> VALUES('Low', 40, '#444444')
>
> INSERT INTO [dbo].[priorities]([priority_desc], [priority_order],
> [priority_color])
> VALUES('Lowest', 50, '#dddddd')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Open')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'On Hold')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Closed')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'In Progress')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Questions')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Proposed')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Compl&Tested')
>
> INSERT INTO [dbo].[statuses]([status])
> VALUES( 'Completed')
>
>
Your script works for me. What is the problem? To create the database itself
you need a CREATE DATABASE statement, which isn't in your script.
--
David Portas
SQL Server MVP
--
Navigation:
[Reply to this message]
|