Error 102 - Incorrect Syntax Near "NULL"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Error 102 - Incorrect Syntax Near "NULL"

Author

Topic

itnorfolk
Starting Member

3 Posts

Posted - 2012-10-22 : 23:51:12

Hi Guys,

I'm new to MSSQL but have a good working knowledge of MySQL, I've set up a new windows based server, set up the database using MSSQL (2008) and simply tried to run a script to create my database tables etc...

I've run the code and I just get Error 102 - Incorrect Syntax Near "NULL" but cannot for the life of me see the syntax problem....

Can anyone help please?

[CODE]
/*Trailblazer Database Setup scripts */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[shipvia]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[shipvia](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ShipMethod] [nvarchar](50) NULL,
[ShippingCost] [money] NULL,
[flag] [int] DEFAULT 0,
CONSTRAINT [PK_shipvia] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Costs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Costs](
[CostVal] [money] NOT NULL,
CONSTRAINT [PK_Costs] PRIMARY KEY CLUSTERED
(
[CostVal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[statuses]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[statuses](
[id] [int] IDENTITY(1,1) NOT NULL,
[status_order] [int] NOT NULL,
[status_name] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[security_levels]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[security_levels](
[id] [int] IDENTITY(1,1) NOT NULL,
[security_level_name] [nvarchar](50) NULL,
[security_level_description] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[priority]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[priority](
[id] [int] IDENTITY(1,1) NOT NULL,
[priority_name] [nvarchar](50) NOT NULL,
[level] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employees]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](30) NULL,
[LastName] [nvarchar](50) NULL,
[Title] [nvarchar](20) NULL,
[Password] [nvarchar](20) NULL,
[EmailName] [nvarchar](50) NULL,
[Extension] [nvarchar](30) NULL,
[Address] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[Region] [nvarchar](50) NULL,
[PostalCode] [nvarchar](20) NULL,
[Country] [nvarchar](50) NULL,
[HomePhone] [nvarchar](30) NULL,
[WorkPhone] [nvarchar](30) NULL,
[DepartmentName] [nvarchar](50) NULL,
[Birthdate] [datetime] NULL,
[SupervisorID] [nvarchar](50) NULL,
[EmrgcyContactName] [nvarchar](50) NULL,
[EmrgcyContactPhone] [nvarchar](30) NULL,
[Photograph] [image] NULL,
[Note] [ntext] NULL,
[securityLevel] [int] NULL,
[site] [int] NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customers](
[CustomerID] [int] NOT NULL,
[OrganizationName] [nvarchar](50) NULL,
[Username] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[Region] [nvarchar](50) NULL,
[PostalCode] [nvarchar](20) NULL,
[Country] [nvarchar](50) NULL,
[ContactName] [nvarchar](50) NULL,
[ContactTitle] [nvarchar](50) NULL,
[PhoneNumber] [nvarchar](30) NULL,
[FaxNumber] [nvarchar](30) NULL,
[Note] [ntext] NULL,
[MobilePhone] [nvarchar](30) NULL,
[EmailName] [nvarchar](50) NULL,
[ReferredBy] [nvarchar](255) NULL,
[Photograph] [image] NULL,
[PaymentTerms] [nvarchar](50) NULL,
[EntryDate] [datetime] NULL,
[DeliveryRoute] [nvarchar](50) NULL,
[activated] [bit] NULL,
[password] [nvarchar](50) NULL,
[Trader] [bit] NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Manufacturers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Manufacturers](
[id] [int] IDENTITY(1,1) NOT NULL,
[manufacturer] [nvarchar](50) NULL,
CONSTRAINT [PK_Manufacturers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Models]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Models](
[modelid] [int] IDENTITY(1,1) NOT NULL,
[manufacturerID] [int] NOT NULL,
[ModelName] [nvarchar](100) NULL,
CONSTRAINT [PK_Models_1] PRIMARY KEY CLUSTERED
(
[modelid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PaymentTerms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PaymentTerms](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PaymentTerms] [nvarchar](50) NOT NULL,
[NumberOfDays] [float] NULL,
CONSTRAINT [PK_PaymentTerms] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[RepairData] Script Date: 01/18/2011 14:06:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RepairData](
[OrderID] [int] NOT NULL,
[ShipVia] [nvarchar](50) NULL,
[FreightCharge] [money] NULL,
[CodCharge] [money] NULL,
[InvoiceID] [int] NULL,
[DepositDue] [money] NULL,
[ServiceDate] [datetime] NULL,
[CustomerID] [nvarchar](50) NULL,
[ProblemDescription] [ntext] NULL,
[EquipmentDescription] [ntext] NULL,
[SerialNumber] [nvarchar](50) NULL,
[EmployeeID] [int] NULL,
[DateReceived] [datetime] NULL,
[ReceivedBy] [int] NULL,
[DatePromised] [datetime] NULL,
[EstimatedCost] [money] NULL,
[WithBezel] [bit] NULL,
[WithPowerCord] [bit] NULL,
[ManufacturerName] [int] NULL,
[Description] [nvarchar](50) NULL,
[ModelNumber] [nvarchar](50) NULL,
[Comments] [ntext] NULL,
[BenchFee] [money] NULL,
[PartsReplaced] [ntext] NULL,
[Note] [ntext] NULL,
[PartsCost] [money] NULL,
[TaxCost] [money] NULL,
[ActualCost] [money] NULL,
[DatePartsOrdered] [datetime] NULL,
[DatePartsReceived] [datetime] NULL,
[PartsOrdered] [bit] NULL,
[PartsReceived] [bit] NULL,
[ActionTakenToRepair] [ntext] NULL,
[AmountPaid] [money] NULL,
[AmountBilled] [money] NULL,
[TotalCost] [money] NULL,
[DateDelivered] [datetime] NULL,
[PrevServiceDate] [datetime] NULL,
[WarrantyExpDate] [datetime] NULL,
[AuthorizedBy] [nvarchar](50) NULL,
[WarrantyWork] [bit] NULL,
[WithSignalCable] [bit] NULL,
[PaidInFull] [bit] NULL,
[AssignedTechnician] [int] NULL,
[RepairCompleted] [bit] NULL,
[UneconomicalToRepair] [bit] NULL,
[NoProblemFound] [bit] NULL,
[NoPartsAvailable] [bit] NULL,
[PaidDate] [datetime] NULL,
[DateCompleted] [datetime] NULL,
[Status] [int] NULL,
[Priority] [int] NULL,
[repairTime] [datetime] NULL,
[NewManufacturer] [nvarchar](50) NULL,
[NewModel] [nvarchar](50) NULL,
[invoiced] [bit] NULL,
[site] [int] NULL,
[couriername] [nvarchar](50) NULL,
[shippingtime] [nvarchar](20) NULL,
[trackingno] [nvarchar](50) NULL,
CONSTRAINT [PK_RepairData] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[RepairData] ADD CONSTRAINT [DF_RepairData_RepairCompleted] DEFAULT ((0)) FOR [RepairCompleted]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[configuration]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[configuration](
[confid] [int] IDENTITY(1,1) NOT NULL,
[cms] [bit] NULL,
[name] [nvarchar](50) NULL,
[value] [text] NULL,
CONSTRAINT [PK_configuration] PRIMARY KEY CLUSTERED
(
[confid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cms](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[title] [nvarchar](50) NULL,
[value] [text] NULL,
[active] [bit] NULL,
CONSTRAINT [PK_cms] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoices]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Invoices](
[ID] [nvarchar](20) NOT NULL,
[custid] [int] NULL,
[gendate] [datetime] NULL,
[notes] [text] NULL,
[printed] [bit] NULL,
[printdate] [datetime] NULL,
[paid] [bit] NULL,
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceItems]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InvoiceItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[InvoiceID] [nvarchar](20) NULL,
[repairID] [int] NULL,
[note] [nvarchar](500) NULL,
CONSTRAINT [PK_InvoiceItems] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lastInvoice]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[lastInvoice](
[id] [int] IDENTITY(1,1) NOT NULL,
[invid] [int] NOT NULL,
CONSTRAINT [PK_lastInvoice_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRepairsUnassigned]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwRepairsUnassigned]
AS
SELECT dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, dbo.RepairData.DateReceived, dbo.RepairData.RepairCompleted,
dbo.priority.priority_name, dbo.RepairData.Priority
FROM dbo.Models RIGHT OUTER JOIN
dbo.priority RIGHT OUTER JOIN
dbo.RepairData ON dbo.priority.[level] = dbo.RepairData.Priority ON dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id
WHERE (dbo.RepairData.Status = 1)
'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwInvoice]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwInvoice]
AS
SELECT dbo.Invoices.ID, dbo.Customers.OrganizationName, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.Trader,
dbo.Invoices.gendate, dbo.Invoices.notes, dbo.Invoices.printed, dbo.Invoices.printdate, dbo.Invoices.paid
FROM dbo.Customers RIGHT OUTER JOIN
dbo.Invoices ON dbo.Customers.CustomerID = dbo.Invoices.custid
'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCompletedRepairsNotInvoiced]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwCompletedRepairsNotInvoiced]
AS
SELECT dbo.RepairData.OrderID, dbo.Customers.OrganizationName, dbo.Customers.City, dbo.Manufacturers.manufacturer, dbo.Models.ModelName,
dbo.RepairData.SerialNumber, dbo.RepairData.DateCompleted, dbo.RepairData.TotalCost, dbo.RepairData.CustomerID
FROM dbo.RepairData LEFT OUTER JOIN
dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id FULL OUTER JOIN
dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE (dbo.RepairData.invoiced = 0 OR
dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)
'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustomerswithRepairsNotYetInvoiced]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
AS
SELECT DISTINCT dbo.RepairData.CustomerID, dbo.Customers.OrganizationName
FROM dbo.RepairData LEFT OUTER JOIN
dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE (dbo.RepairData.invoiced = 0 OR
dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)
'
GO
CREATE VIEW [dbo].[vwCustomerTickets]
AS
SELECT dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103)
AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.statuses.status_name, dbo.RepairData.CustomerID,
dbo.RepairData.SerialNumber
FROM dbo.Models RIGHT OUTER JOIN
dbo.statuses RIGHT OUTER JOIN
dbo.RepairData ON dbo.statuses.id = dbo.RepairData.Status LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id ON
dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
dbo.priority ON dbo.RepairData.Priority = dbo.priority.id

GO

SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[menus](
[id] [int] NOT NULL,
[title] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[url] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[seclevel] [int] NULL,
[order] [int] NULL,
CONSTRAINT [PK_menus] PRIMARY KEY CLUSTERED
(
[id] 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].[sites] Script Date: 12/07/2010 22:15:42 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sites]') AND type in (N'U'))
DROP TABLE [dbo].[sites]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[sites](
[id] [int] IDENTITY(1,1) NOT NULL,
[sitename] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[address1] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[address2] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[postcode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[tel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[fax] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[siteURL] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[logo] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_sites] PRIMARY KEY CLUSTERED
(
[id] 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: View [dbo].[vwEmployees] Script Date: 12/07/2010 22:14:51 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwEmployees]'))
DROP VIEW [dbo].[vwEmployees]
/****** Object: View [dbo].[vwEmployees] Script Date: 12/07/2010 22:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwEmployees]
AS
SELECT dbo.Employees.EmployeeID, dbo.Employees.UserName, dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS name, dbo.sites.sitename,
dbo.security_levels.security_level_description
FROM dbo.Employees LEFT OUTER JOIN
dbo.security_levels ON dbo.Employees.securityLevel = dbo.security_levels.id LEFT OUTER JOIN
dbo.sites ON dbo.Employees.site = dbo.sites.id

GO

GO
/****** Object: View [dbo].[vwTechswithRepairs] Script Date: 12/07/2010 22:15:12 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTechswithRepairs]'))
DROP VIEW [dbo].[vwTechswithRepairs]

/****** Object: View [dbo].[vwTechswithRepairs] Script Date: 12/07/2010 22:11:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwTechswithRepairs]
AS
SELECT EmployeeID, FirstName, LastName,
(SELECT COUNT(1) AS Expr1
FROM dbo.RepairData
WHERE (AssignedTechnician = dbo.Employees.EmployeeID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) AS repairs
FROM dbo.Employees
WHERE ((SELECT COUNT(1) AS Expr1
FROM dbo.RepairData AS RepairData_1
WHERE (AssignedTechnician = dbo.Employees.EmployeeID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) > 0)
GO

INSERT INTO [dbo].[Employees]
([UserName]
,[FirstName]
,[MiddleName]
,[LastName]
,[Title]
,[Password]
,[EmailName]
,[Extension]
,[Address]
,[City]
,[State]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
,[WorkPhone]
,[DepartmentName]
,[Birthdate]
,[SupervisorID]
,[EmrgcyContactName]
,[EmrgcyContactPhone]
,[Photograph]
,[Note]
,[securityLevel]
,[site])
VALUES ('admin','','','','','admin','','','','','','','','','','','','','','','','','',5,1)

INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(1,'View Repairs','~/TicketList.aspx',3,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(2,'Book in Repair','~/TicketForm.aspx',4,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(3,'View Invoices','~/Invoices.aspx',4,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(4,'Uninvoiced Repairs','~/TicketsnotInvoiced.aspx',4,4)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(5,'Customers','~/CustomerList.aspx',4,5)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(6,'Employees','~/Employee_List.aspx',5,6)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(7,'Payment Terms','~/PaymentMethods.aspx',5,7)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(8,'Status Levels','~/StatusList.aspx',5,8)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(9,'Manufacturers','~/Manufacturers.aspx',5,9)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(10,'Shipping','~/Shipping.aspx',5,10)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(11,'Import Manufacturers','~/ManufacturersImport.aspx',5,11)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(12,'View Repair Statistics','~/RepairStats.aspx',5,12)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(13,'Configuration','~/configuration.aspx',5,13)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(14,'CMS','~/ConfigCMS.aspx',5,14)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(15,'Book in Repair','~/TicketForm.aspx',5,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(16,'View Repairs','~/TicketListAdmin.aspx',4,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(17,'View Repairs','~/TicketListAdmin.aspx',5,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(18,'View Invoices','~/Invoices.aspx',5,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(19,'Uninvoiced Repairs','~/TicketsnotInvoiced.aspx',5,4)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(20,'Customers','~/CustomerList.aspx',5,5)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(21,'My Profile','~/Employee_Edit.aspx',3,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(22,'My Profile','~/Employee_Edit.aspx',4,6)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(23,'My Profile','~/Employee_Edit.aspx',5,15)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(24,'Change Password','~/EmployeeChangePassword.aspx',3,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(25,'Change Password','~/EmployeeChangePassword.aspx',4,7)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(26,'Change Password','~/EmployeeChangePassword.aspx',5,16)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(27,'Change Password','~/CustomerChangePassword.aspx',0,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(28,'My Profile','~/CustomerAmendDetails.aspx',0,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(29,'Book in Repair','~/CustomerTicketBookin.aspx',0,1)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(30,'My Profile','~/CustomerAmendDetails.aspx',99,3)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(31,'Change Password','~/CustomerChangePassword.aspx',99,2)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(32,'Company Sites','~/Sites.aspx',5,10)

SET IDENTITY_INSERT [configuration] ON
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(1,0,'defaulttitle','Micro Services Ltd')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(2,0,'default body','<p>If you are a customer, please log in through the Customer Accounts button. Once logged in, you will be able to see any repairs that are currently in our system, and be able to check their status.</p> <p><span lang="en-gb">Trailblazer is a web based .NET application that keeps track of all the repairs that you have in your shop, and allows customers to log in and check the status of their booked in items. If you have trade customers with multiple items in for repair at any time, this becomes a powerful tool, enabling them to check the repair status at any time, and from any location.</span></p>')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(3,0,'sidebar','<h5>Intro:</h5> <p>Trailblazer is designed to make tracking of repairs simple. It allows customers to log in and see the status of the repairs they have in the shop.</p>')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(4,0,'CompanyName','Micro Services Ltd')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(5,0,'adminemail','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(6,0,'address1','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(7,0,'address2','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(8,0,'City','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(9,0,'Postcode','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(10,0,'Tel','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(11,0,'url','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(12,0,'companyreg','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(13,0,'VATNo','')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(14,0,'mailserver','mail.domain.com')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(15,0,'mailusername','admin@domain.com')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(16,0,'mailpassword','password')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(17,0,'notifytech','1')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(18,0,'smsusername','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(19,0,'smspassword','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(20,0,'licencekey','yours')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(21,0,'completethreshhold','6')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(22,0,'VATRatePercent','20')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(23,0,'SalesTaxName','VAT')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(24,0,'currencysymbol','£')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(25,0,'enforcepeerreview','1')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(26,0,'truncatecomments','300')
INSERT INTO [configuration] ([confid],[cms],[name],[value])VALUES(27,0,'pcrepairmode','1')
SET IDENTITY_INSERT [configuration] OFF

SET IDENTITY_INSERT [security_levels] ON

INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(1,'Minimum','Read Only')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(2,'','User Support')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(3,'','Technician')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(4,'','Manager')
INSERT INTO [security_levels] ([id],[security_level_name],[security_level_description])VALUES(5,'Maximum','Administrator')
SET IDENTITY_INSERT [security_levels] OFF

SET IDENTITY_INSERT [PaymentTerms] ON
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(1,'C.O.D.',0.000000000000000e+000)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(2,'Net 120',1.200000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(3,'Net 150',1.500000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(4,'Net 180',1.800000000000000e+002)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(5,'Net 30',3.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(6,'Net 60',6.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(7,'Net 90',9.000000000000000e+001)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(8,'Upon Receipt',8.000000000000000e+000)
INSERT INTO [PaymentTerms] ([ID],[PaymentTerms],[NumberOfDays])VALUES(9,'Credit',9.000000000000000e+001)
SET IDENTITY_INSERT [PaymentTerms] Off

SET IDENTITY_INSERT [priority] ON
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(1,'Low',1)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(2,'Medium',2)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(3,'High',3)
INSERT INTO [priority] ([id],[priority_name],[level])VALUES(4,'Urgent',10)
SET IDENTITY_INSERT [priority] Off

SET IDENTITY_INSERT [shipvia] ON
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(1,'Royal Mail',8.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(2,'UPS',15.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(3,'Pickup in Store',0.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(4,'Courier',35.0000)
INSERT INTO [shipvia] ([ID],[ShipMethod],[ShippingCost])VALUES(5,'Our Driver',8.0000)
SET IDENTITY_INSERT [shipvia] Off

SET IDENTITY_INSERT [statuses] ON
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(0,0,'Booked in by Customer')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(1,1,'Booked in')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(2,2,'Assigned to Technician')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(3,3,'Awaiting Parts')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(4,4,'Awaiting Customer Decision')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(5,5,'Repair In Progress')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(6,6,'Repair Complete')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(7,7,'Under Test')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(8,8,'Shipped')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(9,9,'Invoiced')
INSERT INTO [statuses] ([id],[status_order],[status_name])VALUES(10,10,'Paid')
SET IDENTITY_INSERT [statuses] Off

INSERT INTO [Costs] ([CostVal])VALUES(0.00)
INSERT INTO [Costs] ([CostVal])VALUES(5.00)
INSERT INTO [Costs] ([CostVal])VALUES(10.00)
INSERT INTO [Costs] ([CostVal])VALUES(15.00)
INSERT INTO [Costs] ([CostVal])VALUES(20.00)
INSERT INTO [Costs] ([CostVal])VALUES(25.00)
INSERT INTO [Costs] ([CostVal])VALUES(30.00)
INSERT INTO [Costs] ([CostVal])VALUES(35.00)
INSERT INTO [Costs] ([CostVal])VALUES(40.00)
INSERT INTO [Costs] ([CostVal])VALUES(45.00)
INSERT INTO [Costs] ([CostVal])VALUES(50.00)
INSERT INTO [Costs] ([CostVal])VALUES(55.00)
INSERT INTO [Costs] ([CostVal])VALUES(60.00)
INSERT INTO [Costs] ([CostVal])VALUES(65.00)
INSERT INTO [Costs] ([CostVal])VALUES(70.00)
INSERT INTO [Costs] ([CostVal])VALUES(75.00)
INSERT INTO [Costs] ([CostVal])VALUES(80.00)
INSERT INTO [Costs] ([CostVal])VALUES(85.00)
INSERT INTO [Costs] ([CostVal])VALUES(90.00)
INSERT INTO [Costs] ([CostVal])VALUES(95.00)
INSERT INTO [Costs] ([CostVal])VALUES(100.00)
INSERT INTO [Costs] ([CostVal])VALUES(105.00)
INSERT INTO [Costs] ([CostVal])VALUES(110.00)
INSERT INTO [Costs] ([CostVal])VALUES(115.00)
INSERT INTO [Costs] ([CostVal])VALUES(120.00)
INSERT INTO [Costs] ([CostVal])VALUES(125.00)
INSERT INTO [Costs] ([CostVal])VALUES(130.00)
INSERT INTO [Costs] ([CostVal])VALUES(135.00)
INSERT INTO [Costs] ([CostVal])VALUES(140.00)
INSERT INTO [Costs] ([CostVal])VALUES(145.00)
INSERT INTO [Costs] ([CostVal])VALUES(150.00)
INSERT INTO [Costs] ([CostVal])VALUES(155.00)
INSERT INTO [Costs] ([CostVal])VALUES(160.00)
INSERT INTO [Costs] ([CostVal])VALUES(165.00)
INSERT INTO [Costs] ([CostVal])VALUES(170.00)
INSERT INTO [Costs] ([CostVal])VALUES(175.00)
INSERT INTO [Costs] ([CostVal])VALUES(180.00)
INSERT INTO [Costs] ([CostVal])VALUES(185.00)
INSERT INTO [Costs] ([CostVal])VALUES(190.00)
INSERT INTO [Costs] ([CostVal])VALUES(200.00)
INSERT INTO [Costs] ([CostVal])VALUES(205.00)
INSERT INTO [Costs] ([CostVal])VALUES(210.00)
INSERT INTO [Costs] ([CostVal])VALUES(215.00)
INSERT INTO [Costs] ([CostVal])VALUES(220.00)
INSERT INTO [Costs] ([CostVal])VALUES(225.00)
INSERT INTO [Costs] ([CostVal])VALUES(230.00)
INSERT INTO [Costs] ([CostVal])VALUES(235.00)
INSERT INTO [Costs] ([CostVal])VALUES(240.00)
INSERT INTO [Costs] ([CostVal])VALUES(245.00)
INSERT INTO [Costs] ([CostVal])VALUES(250.00)
INSERT INTO [Costs] ([CostVal])VALUES(255.00)
INSERT INTO [Costs] ([CostVal])VALUES(260.00)
INSERT INTO [Costs] ([CostVal])VALUES(265.00)
INSERT INTO [Costs] ([CostVal])VALUES(270.00)
INSERT INTO [Costs] ([CostVal])VALUES(275.00)
INSERT INTO [Costs] ([CostVal])VALUES(280.00)
INSERT INTO [Costs] ([CostVal])VALUES(285.00)
INSERT INTO [Costs] ([CostVal])VALUES(290.00)
INSERT INTO [Costs] ([CostVal])VALUES(300.00)

SET IDENTITY_INSERT [Manufacturers] ON
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(1,'Dell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(3,'Macintosh')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(4,'Hewlett Packard')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(5,'Yuraku')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(6,'Asus')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(7,'ABS Computer Technologies(Parent:Newegg)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(8,'Acer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(9,'Gateway')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(10,'Aigo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(11,'Viewsonic')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(12,'AMAX')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(14,'IBM')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(15,'eMachines')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(16,'Packard Bell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(17,'Amstrad')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(18,'Apple')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(19,'AVADirect')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(20,'Averatec')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(21,'AXIOO International')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(22,'Axiomtek')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(23,'Belinea')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(24,'BenQ')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(25,'Cerise Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(26,'Chip PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(27,'Clevo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(28,'Commodore Gaming')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(29,'Cray')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(30,'CyberPower PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(31,'Data General')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(32,'Alienware')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(33,'Dera')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(34,'Doxx Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(35,'Egenera')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(36,'Falcon Northwest')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(37,'Fujitsu')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(38,'Gericom')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(39,'Gigabyte')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(40,'Groupe Bull')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(41,'HLBS Tech (P) Limited')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(42,'Hasee')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(43,'Hewlett-Packard')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(44,'Compaq')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(45,'VoodooPC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(46,'HCL Infosystems Ltd')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(47,'Hitachi')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(48,'Integrated Intellect Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(49,'IGEL')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(50,'Jetta International')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(51,'Kohjinsha')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(52,'Kontron AG')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(53,'Lanix')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(54,'Lanner Inc')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(55,'LanSlide Gaming PCs')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(56,'Lenovo')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(57,'LG')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(58,'Maingear')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(59,'MDG Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(60,'Mecer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(61,'Medion')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(62,'Mesh Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(63,'Micro-Star International (MSI)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(64,'Micro Center')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(65,'Mitac International Corp.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(66,'Motion Computing Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(67,'NEC Corp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(68,'Nedfield NV(formerly: Tulip Computers)')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(69,'NEO')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(70,'Neos Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(71,'Olidata')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(72,'Olivetti')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(73,'Oracle Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(74,'Sun Microsystems')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(75,'Origin PC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(76,'Panasonic')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(77,'Polywell')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(78,'Puget Systems')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(79,'Q2')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(80,'Redfox')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(81,'Sager Notebook Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(82,'Samsung Electronics')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(83,'Sharp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(84,'Shuttle Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(85,'SGI')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(86,'Socket Mobile Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(87,'Sony Corp')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(88,'Systemax')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(89,'Circuit City')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(90,'CompUSA')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(91,'TigerDirect')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(92,'System76')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(93,'TabletKiosk')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(94,'Tadpole Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(95,'TIME')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(96,'Toshiba')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(97,'TriGem Computer')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(98,'Inc')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(99,'Tyan')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(100,'Unisys')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(101,'Velocity Micro')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(102,'Viglen')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(103,'Vigor Gaming')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(104,'WidowPC')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(105,'Wipro Infotech')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(106,'Wyse Technology Inc.')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(107,'Xitrix Computer Corporation')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(108,'Zenith Computers')
INSERT INTO [Manufacturers] ([id],[manufacturer])VALUES(109,'Zoostorm')

SET IDENTITY_INSERT [Manufacturers] OFF

SET IDENTITY_INSERT [lastInvoice] ON
INSERT INTO [lastInvoice] ([id],[invid])VALUES(1,2000)
SET IDENTITY_INSERT [lastInvoice] OFF

SET IDENTITY_INSERT [sites] ON
INSERT INTO [sites] ([id],[sitename],[address1],[address2],[city],[postcode],[tel],[fax],[email],[siteURL],[logo])VALUES(1,'default',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
SET IDENTITY_INSERT [sites] OFF

/****** Object: Table [dbo].[lastCustomer] Script Date: 12/10/2010 13:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lastCustomer](
[id] [int] IDENTITY(1,1) NOT NULL,
[custid] [int] NOT NULL,
CONSTRAINT [PK_lastCustomer_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/****** Object: Table [dbo].[lastRepair] Script Date: 12/10/2010 13:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[lastRepair](
[id] [int] IDENTITY(1,1) NOT NULL,
[repid] [int] NOT NULL,
CONSTRAINT [PK_lastRepair_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [lastCustomer] ON
INSERT INTO [lastCustomer] ([id],[custid])VALUES(1,100)
SET IDENTITY_INSERT [lastCustomer] OFF
SET IDENTITY_INSERT [lastRepair] ON
INSERT INTO [lastRepair] ([id],[repid])VALUES(1,3000)
SET IDENTITY_INSERT [lastRepair] OFF

/****** Object: View [dbo].[vwCustswithRepairs] Script Date: 12/15/2010 18:57:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustswithRepairs]
AS
SELECT CustomerID, OrganizationName, FirstName, LastName,
(SELECT COUNT(1) AS Expr1
FROM dbo.RepairData
WHERE (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) AS repairs
FROM dbo.Customers
WHERE ((SELECT COUNT(1) AS Expr1
FROM dbo.RepairData AS RepairData_1
WHERE (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) > 0)

GO
/* 26th Jan 2011 */
ALTER TABLE RepairData
ADD [techsignoff] [bit] NULL,
[techsignoff2] [bit] NULL,
[tech2id] [int] NULL,
[externalref] nvarchar(150) NULL;

/****** Object: View [dbo].[vwRepairsbyEmployees] Script Date: 12/08/2010 18:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwRepairsbyEmployees]
AS
SELECT dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103)
AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.statuses.status_name, dbo.RepairData.CustomerID,
dbo.RepairData.SerialNumber, dbo.RepairData.AssignedTechnician, dbo.RepairData.UneconomicalToRepair, dbo.RepairData.externalref

FROM dbo.Models RIGHT OUTER JOIN
dbo.statuses RIGHT OUTER JOIN
dbo.RepairData ON dbo.statuses.id = dbo.RepairData.Status LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id ON
dbo.Models.modelid = dbo.RepairData.ModelNumber LEFT OUTER JOIN
dbo.priority ON dbo.RepairData.Priority = dbo.priority.id

GO

/****** Object: View [dbo].[vwCustomerswithRepairsNotYetInvoiced] Script Date: 01/26/2011 13:33:29 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustomerswithRepairsNotYetInvoiced]'))
DROP VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
GO

/****** Object: View [dbo].[vwCustomerswithRepairsNotYetInvoiced] Script Date: 01/27/2011 13:45:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustomerswithRepairsNotYetInvoiced]
AS
SELECT DISTINCT dbo.RepairData.CustomerID, dbo.Customers.OrganizationName
FROM dbo.RepairData LEFT OUTER JOIN
dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE (dbo.RepairData.invoiced = 0 OR
dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)

GO

/****** Object: View [dbo].[vwRepairsAll] Script Date: 01/27/2011 13:48:08 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwRepairsAll]'))
DROP VIEW [dbo].[vwRepairsAll]
GO

/****** Object: View [dbo].[vwRepairsAll] Script Date: 01/27/2011 13:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwRepairsAll]
AS
SELECT dbo.RepairData.OrderID, dbo.Manufacturers.manufacturer, dbo.Models.ModelName, CONVERT(VARCHAR(10), dbo.RepairData.DateReceived, 103)
AS Received, dbo.RepairData.RepairCompleted, dbo.priority.priority_name, dbo.RepairData.Priority, dbo.statuses.status_name,
CASE WHEN dbo.Customers.OrganizationName <> '' THEN Customers.OrganizationName ELSE dbo.Customers.FirstName + ' ' + dbo.Customers.LastName
END AS cust, dbo.Customers.LastName, dbo.RepairData.site, dbo.sites.sitename, dbo.RepairData.SerialNumber,
dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS empname, dbo.Employees.UserName, dbo.RepairData.Status,
dbo.RepairData.AssignedTechnician, dbo.RepairData.externalref, dbo.Customers.FirstName
FROM dbo.sites RIGHT OUTER JOIN
dbo.Employees RIGHT OUTER JOIN
dbo.RepairData ON dbo.Employees.EmployeeID = dbo.RepairData.AssignedTechnician ON dbo.sites.id = dbo.RepairData.site LEFT OUTER JOIN
dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID LEFT OUTER JOIN
dbo.statuses ON dbo.RepairData.Status = dbo.statuses.id LEFT OUTER JOIN
dbo.priority ON dbo.RepairData.Priority = dbo.priority.[level] LEFT OUTER JOIN
dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id

GO

/****** Object: View [dbo].[vwCustswithRepairs] Script Date: 02/10/2011 21:46:09 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCustswithRepairs]'))
DROP VIEW [dbo].[vwCustswithRepairs]
GO

/****** Object: View [dbo].[vwCustswithRepairs] Script Date: 02/10/2011 21:46:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCustswithRepairs]
AS
SELECT CustomerID, OrganizationName, FirstName, LastName,
(SELECT COUNT(1) AS Expr1
FROM dbo.RepairData
WHERE (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) AS repairs, PhoneNumber, MobilePhone, EmailName
FROM dbo.Customers
WHERE ((SELECT COUNT(1) AS Expr1
FROM dbo.RepairData AS RepairData_1
WHERE (CustomerID = dbo.Customers.CustomerID) AND (RepairCompleted = 0 OR
RepairCompleted IS NULL)) > 0)

GO

/****** Object: View [dbo].[vwCompletedRepairsNotInvoiced] Script Date: 02/13/2011 18:06:26 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwCompletedRepairsNotInvoiced]'))
DROP VIEW [dbo].[vwCompletedRepairsNotInvoiced]
GO

/****** Object: View [dbo].[vwCompletedRepairsNotInvoiced] Script Date: 02/13/2011 18:06:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwCompletedRepairsNotInvoiced]
AS
SELECT dbo.RepairData.OrderID, dbo.Customers.OrganizationName, dbo.Customers.City, dbo.Manufacturers.manufacturer, dbo.Models.ModelName,
dbo.RepairData.SerialNumber, dbo.RepairData.DateCompleted, dbo.RepairData.TotalCost, dbo.RepairData.CustomerID,
dbo.RepairData.AmountBilled

FROM dbo.RepairData LEFT OUTER JOIN
dbo.Models ON dbo.RepairData.ModelNumber = dbo.Models.modelid LEFT OUTER JOIN
dbo.Manufacturers ON dbo.RepairData.ManufacturerName = dbo.Manufacturers.id LEFT OUTER JOIN
dbo.Customers ON dbo.RepairData.CustomerID = dbo.Customers.CustomerID
WHERE (dbo.RepairData.invoiced = 0 OR
dbo.RepairData.invoiced IS NULL) AND (dbo.RepairData.RepairCompleted = 1) AND (dbo.Customers.Trader = 1)

GO

/****** Object: Table [security] Script Date: 02/22/2011 15:18:20 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[security]') AND type in (N'U'))
DROP TABLE dbo.[security]
GO

/****** Object: Table [security] Script Date: 02/22/2011 15:18:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[security](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[description] [nvarchar](100) NULL,
[readonlyuser] [bit] NULL,
[reportuser] [bit] NULL,
[technician] [bit] NULL,
[manager] [bit] NULL,
[administrator] [bit] NULL,
CONSTRAINT [PK_security] PRIMARY KEY CLUSTERED
(
[id] 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

SET IDENTITY_INSERT [security] ON
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('1','serial Number','Serial Number for repair item','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('2','Equipment Description','Equipment Description text box','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('3','AssignedTechnician','Drop down selection for assigning technician to repair job','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('4','Priority','Priority selection drop down list','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('5','Status','Status selection drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('6','Complete','Checkbox for setting repair as completed','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('7','Date Completed','Date picker to set completed date','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('8','Manufacturer','Manufacturer selection - drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('9','Model','Model selection - drop down list','0','0','1','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('10','ProblemDescription','Text area specifying nature of problem with repair job','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('11','DatePromised','Date used as a guide for when repair shop be delivered back to customer','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('12','assignpeer','Drop down list used to select technician to review repair once complete','0','0','0','1','1')
INSERT [security](id,name,description,readonlyuser,reportuser,technician,manager,administrator) VALUES('13','Delete','Repair item delete button ','0','0','0','0','1')
SET IDENTITY_INSERT [security] OFF

INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(33,'Security Restrictions','~/FieldRestrictions.aspx',5,16)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(34,'Customer Messages','~/messaging.aspx',5,15)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(35,'Parts Store','~/PartsList.aspx',5,14)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(36,'Report Bug','~/bugreport.aspx',5,20)
INSERT INTO [menus] ([id],[title],[url],[seclevel],[order])VALUES(37,'Service Codes','~/ServiceCodesList.aspx',5,15)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partsused](
[id] [int] IDENTITY(1,1) NOT NULL,
[repairID] [int] NOT NULL,
[partID] [int] NOT NULL,
[quantity] [int] NOT NULL,
CONSTRAINT [PK_partsused] PRIMARY KEY CLUSTERED
(
[id] 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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partsstore](
[id] [int] IDENTITY(1,1) NOT NULL,
[partno] [nvarchar](50) NOT NULL,
[partname] [nvarchar](50) NOT NULL,
[description] [nvarchar](200) NOT NULL,
[cost] [decimal](18, 2) NOT NULL,
[quant] [int] NOT NULL,
[reorder] [int] NOT NULL,
[partsource] [nvarchar](50) NULL,
[sourcepartcode] [nvarchar](50) NULL,
[category] [int] NULL,
CONSTRAINT [PK_partsstore] PRIMARY KEY CLUSTERED
(
[id] 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].[messages] Script Date: 02/28/2011 11:47:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[messages](
[id] [int] IDENTITY(1,1) NOT NULL,
[status] [int] NULL,
[smsmessage] [nvarchar](200) NULL,
[emailmessage] [nvarchar](500) NULL,
[sendoncomplete] [bit] NULL,
[sendsms] [bit] NULL,
[sendemail] [bit] NULL,
CONSTRAINT [PK_messages] PRIMARY KEY CLUSTERED
(
[id] 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].[partscategory] Script Date: 02/28/2011 11:47:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[partscategory](
[id] [int] IDENTITY(1,1) NOT NULL,
[categoryname] [nvarchar](50) NOT NULL,
[description] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_partscategory] PRIMARY KEY CLUSTERED
(
[id] 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

SET IDENTITY_INSERT [messages] ON
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('2','2','Your item has now been assigned to a technician','Your item has now been assigned to a technician','0','0','0')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('3','3','Your repair item requires your decision. Please contact us to discuss your repair.','Your repair item requires your decision. Please contact us to discuss your repair.','0','0','1')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('4','6','Your repair is now complete.','From #company#. Your repair no. #orderno# is now complete. #manufacturer# #model# #serial#','1','1','1')
INSERT [messages](id,status,smsmessage,emailmessage,sendoncomplete,sendsms,sendemail) VALUES('5','9','You have now been sent an invoice for your repair item','You have now been sent an invoice for your repair item #orderno#','0','1','1')
SET IDENTITY_INSERT [messages] OFF

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

INSERT INTO [dbo].[partscategory]
([categoryname]
,[description])
VALUES
('Default'
,'Default category for all parts')
GO

/****** Object: Table [dbo].[servicecodes] Script Date: 03/15/2011 15:18:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[servicecodes](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nvarchar](10) NULL,
[type] [int] NULL,
[description] [nvarchar](500) NULL,
[cost] [decimal](18, 2) NULL,
CONSTRAINT [PK_servicecodes] PRIMARY KEY CLUSTERED
(
[id] 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].[servicecodesused] Script Date: 03/15/2011 15:18:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[servicecodesused](
[id] [int] IDENTITY(1,1) NOT NULL,
[repairID] [int] NOT NULL,
[servID] [int] NOT NULL,
[notes] [nvarchar](500) NULL,
CONSTRAINT [PK_servused] PRIMARY KEY CLUSTERED
(
[id] 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].[servicetypes] Script Date: 03/15/2011 15:19:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[servicetypes](
[id] [int] IDENTITY(1,1) NOT NULL,
[description] [nvarchar](50) NULL,
CONSTRAINT [PK_servicetypes] PRIMARY KEY CLUSTERED
(
[id] 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

INSERT INTO [dbo].[servicetypes]
([description])
VALUES
('General')
GO

INSERT INTO [dbo].[servicetypes]
([description])
VALUES
('Electronic Work')
GO

INSERT INTO [dbo].[servicetypes]
([description])
VALUES
('Calibration')
GO

SET IDENTITY_INSERT servicecodes ON
INSERT servicecodes(id,code,type,description,cost) VALUES('1','1','1','Dust off','5.00')
INSERT servicecodes(id,code,type,description,cost) VALUES('2','rf1','2','CPU Reflow','35.00')
INSERT servicecodes(id,code,type,description,cost) VALUES('3','cb1','3','PSU Calibration','34.00')
INSERT servicecodes(id,code,type,description,cost) VALUES('4','rr1','1','RAM Re seat','5.00')
SET IDENTITY_INSERT servicecodes OFF

/* --------------------------------------------------------------------------------------------------------------------------------*/
/*'12/7/2011'*/
delete from menus
GO

ALTER TABLE menus
ADD [parent] int default 0
GO

INSERT menus(id,title,url,seclevel,[order],parent) VALUES('1','Repairs','~/TicketList.aspx','3','1','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('2','New Repair','~/TicketForm.aspx','4','2','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('3','View Invoices','~/Invoices.aspx','4','3','39')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('4','Uninvoiced Repairs','~/TicketsnotInvoiced.aspx','4','4','39')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('5','Customers','~/CustomerList.aspx','4','3','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('6','Employees','~/Employee_List.aspx','5','6','41')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('7','Payment Terms','~/PaymentMethods.aspx','5','7','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('8',' Status Levels','~/StatusList.aspx','5','8','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('9','Manufacturers','~/Manufacturers.aspx','5','10','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('10','Shipping','~/Shipping.aspx','5','9','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('11','Import Manufacturers','~/ManufacturersImport.aspx','5','11','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('12','View Repair Statistics','~/RepairStats.aspx','5','12','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('13','Configuration','~/configuration.aspx','5','7','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('14','CMS','~/ConfigCMS.aspx','5','14','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('15','New Repair','~/TicketForm.aspx','5','2','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('16','Repairs','~/TicketListAdmin.aspx','4','1','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('17','Repairs','~/TicketListAdmin.aspx','5','1','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('18','View Invoices','~/Invoices.aspx','5','3','40')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('19','Uninvoiced Repairs','~/TicketsnotInvoiced.aspx','5','4','40')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('20','Customers','~/CustomerList.aspx','5','3','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('21','My Profile','~/Employee_Edit.aspx','3','2','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('22','My Profile','~/Employee_Edit.aspx','4','6','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('23','My Profile','~/Employee_Edit.aspx','5','17','41')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('24','Change Password','~/EmployeeChangePassword.aspx','3','3','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('25','Change Password','~/EmployeeChangePassword.aspx','4','7','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('26','Change Password','~/EmployeeChangePassword.aspx','5','18','41')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('27','Change Password','~/CustomerChangePassword.aspx','0','2','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('28','My Profile','~/CustomerAmendDetails.aspx','0','3','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('29','Book in Repair','~/CustomerTicketBookin.aspx','0','1','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('30','My Profile','~/CustomerAmendDetails.aspx','99','3','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('31','Change Password','~/CustomerChangePassword.aspx','99','2','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('32','Company Sites','~/Sites.aspx','5','6','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('33','Security Restrictions','~/FieldRestrictions.aspx','5','17','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('34','Customer Messages','~/messaging.aspx','5','16','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('35','Parts Store','~/PartsList.aspx','5','14','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('36','Report Bug','~/bugreport.aspx','5','20','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('37','Service Codes','~/ServiceCodesList.aspx','5','15','38')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('38','Administration','#','5','6','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('39','Invoices','#','4','4','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('40','Invoices','#','5','4','0')
INSERT menus(id,title,url,seclevel,[order],parent) VALUES('41','Users','#','5','5','0')

SET IDENTITY_INSERT [configuration] ON
INSERT configuration(confid,cms,name,value) VALUES('28',NULL,'alertnewcustomer','0')
INSERT configuration(confid,cms,name,value) VALUES('29',NULL,'alerttradecustomer','1')
INSERT configuration(confid,cms,name,value) VALUES('30',NULL,'notifytechOutlook','1')
INSERT configuration(confid,cms,name,value) VALUES('31',NULL,'repairdeadlinedays','5')
INSERT configuration(confid,cms,name,value) VALUES('32',NULL,'emailserverPort','25')
SET IDENTITY_INSERT [configuration] OFF

/*Version 010120 */
/****** Object: Table [repairs].[historylog] Script Date: 10/17/2011 16:01:57 ******/

CREATE TABLE [historylog](
[id] [int] IDENTITY(1,1) NOT NULL,
[userid] [nvarchar](50) NULL,
[logentry] [nvarchar](100) NULL,
[date] [datetime] NULL,
CONSTRAINT [PK_historylog] PRIMARY KEY CLUSTERED
(
[id] 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
/*Version 010121 */
[/CODE]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-23 : 00:38:25

You missed column name [code],

CREATE TABLE [dbo].[servicecodes](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nvarchar](10) NULL,
[type] [int] NULL,
[description] [nvarchar](500) NULL,
[cost] [decimal](18, 2) NULL,
CONSTRAINT [PK_servicecodes] PRIMARY KEY CLUSTERED
(
[id] 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

--
Chandu

itnorfolk
Starting Member

3 Posts

Posted - 2012-10-23 : 01:00:21

hey. Thanks for that. Ive fixed that but im getting the same error details now :(

ugh. I hate code lol

itnorfolk
Starting Member

3 Posts

Posted - 2012-10-23 : 13:39:09

Hey Guys,

I checked through and found this....

[CODE]
/* 26th Jan 2011 */
ALTER TABLE RepairData
ADD [techsignoff] [bit] NULL,
[techsignoff2] [bit] NULL,
[tech2id] [int] NULL,
[externalref] nvarchar(150) NULL;
[/CODE]

I'm guessing it should be , not ; so I've changed that but now it get a different error (/* SQL Error (102): Incorrect syntax near 'GO'. */)

Any ideas what on earth is happening?

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2012-10-23 : 14:31:19

If you double click on the red text in the output of SQL Server management studio, you should be brought to the line where the error was detected.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:42:16

once i add the missing column and executed it ran to success for me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources