|
|
Posted by Jozef on 11/15/05 08:46
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')
Navigation:
[Reply to this message]
|