SEO

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')

"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
--

Hi Joseph,



You have to add something like

USE NameofthedatabaseWhereyouwanttodeploythescript
GO
<YourScript>

You can then load this within query analyzer and execute it. if you
dont have the QA just execute the script with the OSQL commandline
function like this:

OSQL -S<YourServernameHere> -E -i<PathandNameoftheInputscript>

HTH, Jens Suessmeyer.

Thanks Jens, you ROCK! It worked fine. I know it was a silly problem, but
I couldn't find exactly what I was looking for in the help file.

David, I was just looking for the "tool" to run the script in (and the
additional code that Jens provided to specify the database). The QA was the
answer.

Thanks again!

"Jens" <Jens@sqlserver2005.de> wrote in message
news:1132039143.720269.61390@g49g2000cwa.googlegro ups.com...
> Hi Joseph,
>
>
>
> You have to add something like
>
> USE NameofthedatabaseWhereyouwanttodeploythescript
> GO
> <YourScript>
>
> You can then load this within query analyzer and execute it. if you
> dont have the QA just execute the script with the OSQL commandline
> function like this:
>
> OSQL -S<YourServernameHere> -E -i<PathandNameoftheInputscript>
>
> HTH, Jens Suessmeyer.
>