CREATE DATABASE SprocketProduction GO USE SprocketProduction GO CREATE TABLE [dbo].[SprocketPrices] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [SprocketID] [int] NULL , [Price] [money] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Sprockets] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [Description] [varchar] (50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[SprocketReferences] ( [SprocketID] [int] IDENTITY NOT NULL , [Reference] [varchar] (50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[SprocketReferences] WITH NOCHECK ADD CONSTRAINT [PK_SprocketReferences] PRIMARY KEY NONCLUSTERED ( [SprocketID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[SprocketPrices] WITH NOCHECK ADD CONSTRAINT [PK_SprocketPrices] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Sprockets] WITH NOCHECK ADD CONSTRAINT [PK_Sprockets] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE VIEW dbo.CurrentPrices AS SELECT SprocketID, Price, Description FROM Sprockets INNER JOIN SprocketPrices ON Sprockets.RecordID = SprocketPrices.SprocketID GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO CREATE DATABASE SprocketDevelopment GO ALTER DATABASE SprocketDevelopment SET ENABLE_BROKER GO USE SprocketDevelopment GO CREATE XML SCHEMA COLLECTION SprocketsMessageSchema AS N' '; GO CREATE XML SCHEMA COLLECTION SprocketsSchema AS N' '; GO CREATE MESSAGE TYPE SprocketMessage VALIDATION = VALID_XML WITH SCHEMA COLLECTION SprocketsMessageSchema GO CREATE CONTRACT SubmissionContract (SprocketMessage SENT BY INITIATOR) GO CREATE QUEUE TargetQueue GO CREATE SERVICE TargetService ON QUEUE TargetQueue (SubmissionContract) GO CREATE ROUTE InitiatorRoute WITH SERVICE_NAME = 'InitiatorService', ADDRESS = 'LOCAL' GO CREATE ROLE InitiatingUser GO GRANT SEND ON SERVICE::TargetService TO InitiatingUser GO CREATE ASSEMBLY SprocketAssembly FROM 0x4d5a90000300000004000000ffff0000b800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000e1fba0e00b409cd21b8014ccd21546869732070726f6772616d2063616e6e6f742062652072756e20696e20444f53206d6f64652e0d0d0a2400000000000000\ 504500004c010300370542460000000000000000e0000e210b0108000010000000200000000000009e2b0000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030000040000100000100000000010000010000000000000100000000000000000000000\ 482b000053000000004000005803000000000000000000000000000000000000006000000c000000dc2a00001c0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002e74657874000000\ a40b0000002000000010000000100000000000000000000000000000200000602e7273726300000058030000004000000010000000200000000000000000000000000000400000402e72656c6f6300000c0000000060000000100000003000000000000000000000000000004000004200000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 802b0000000000004800000002000500702200006c08000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330030062000000010000110002280200000616fe010b072d0872010000700a2b4a1b8d170000010c0816027b010000\ 048c18000001a20817720b000070a20818027b020000048c18000001a20819720b000070a2081a027b030000048c18000001a208281100000a0a2b00062a0000133001000c0000000200001100027b040000040a2b00062a133002001700000003000011001200fe15020000021200177d04000004060b2b00072a001b300400\ 8600000004000011000f00281200000a16fe01130411042d090028030000060d2b691200fe1502000002000f00fe16030000016f1300000a178d1a00000113051105161f2c9d11056f1400000a0b120007169a281500000a7d01000004120007179a281500000a7d02000004120007189a281500000a7d030000041200167d04\ 000004060dde040c00087a00092a000001100000000022005d7f00041c000001133002001a0000000500001100027b01000004027b020000045a027b030000045a0a2b00062a000013300200130000000500001100027b01000004027b020000045a0a2b00062a0013300200130000000500001100027b01000004027b030000\ 045a0a2b00062a0013300200130000000500001100027b02000004027b030000045a0a2b00062a00133001000c0000000500001100027b010000040a2b00062a133001000c0000000500001100027b030000040a2b00062a133001000c0000000500001100027b020000040a2b00062a42534a4201000100000000000c000000\ 76322e302e35303732370000000005006c00000030030000237e00009c030000b003000023537472696e6773000000004c07000014000000235553006007000010000000234755494400000070070000fc00000023426c6f6200000000000000020000015717a2010900000000fa013300160000010000001c00000002000000\ 040000000b0000000100000001000000150000000c00000005000000010000000500000005000000010000000200000000000a000100000000000600380031000a0063004e000a00aa004e0006002c011a0106006a014b0106007e011a01060097011a010600b2011a010600cd011a010600e6011a010600ff011a0106001e02\ 1a0106003b021a010600650252023b00790200000600a80288020600c80288020600f50231000a0026030b030a0042030b03060049034b0106005f034b0106006a0331000600710331000600780331000600880331000600930331000600a40331000000000001000000000001000100092110001d0000000500010001000100\ 6d000a00010076000a0001007e000a00010086000d00502000000000c6008d0010000100c02000000000e609960014000100d820000000009608a10018000100fc20000000009600b4001d000100a021000000008600ba0024000200c821000000008600c10024000200e821000000008600c900240002000822000000008600\ d300240002002822000000008608dc00240002004022000000008608e700240002005822000000008608f1002400020000000100860302000900210045013500290045013a00310045013500390045013500410045013500490045013500510045013500590045013500610045013500690045013500710045013f0081004501\ 4500890045014a00910045014a00990045014e00a90045015d00c9007f036300190096001400b9008d001000c9008d037b00d9009b0382002e003b0099002e00130099002e00230099002e002b009f002e003300b7002e005300b7002e006b00da002e004b0099002e00430099002e005b00c8002e006300d10043007b005400\ 69007000740087009500020001000000fb002800000002012c0000000701310000000e01310000001401310002000200030002000300050002000900070002000a00090002000b000b0004800000010000007d0a1b7b000000000000e60200000200000000000000000000000100280000000000020000000000000000000000\ 010042000000000000000000003c4d6f64756c653e00576964676574417373656d626c792e646c6c0044696d656e73696f6e73006d73636f726c69620053797374656d0056616c7565547970650053797374656d2e446174610053797374656d2e446174612e53716c547970657300494e756c6c61626c65006d5f4865696768\ 74006d5f5769647468006d5f4465707468006d5f4e756c6c00546f537472696e67006765745f49734e756c6c006765745f4e756c6c0053716c537472696e6700506172736500566f6c756d6500546f70417265610046726f6e7441726561005369646541726561006765745f486569676874006765745f446570746800676574\ 5f57696474680049734e756c6c004e756c6c004865696768740044657074680057696474680053797374656d2e5265666c656374696f6e00417373656d626c7956657273696f6e417474726962757465002e63746f720053797374656d2e52756e74696d652e496e7465726f70536572766963657300436f6d56697369626c65\ 41747472696275746500417373656d626c7943756c7475726541747472696275746500417373656d626c7954726164656d61726b41747472696275746500417373656d626c79436f7079726967687441747472696275746500417373656d626c7950726f6475637441747472696275746500417373656d626c79436f6d70616e\ 7941747472696275746500417373656d626c79436f6e66696775726174696f6e41747472696275746500417373656d626c794465736372697074696f6e41747472696275746500417373656d626c795469746c654174747269627574650053797374656d2e446961676e6f73746963730044656275676761626c654174747269\ 6275746500446562756767696e674d6f6465730053797374656d2e52756e74696d652e436f6d70696c6572536572766963657300436f6d70696c6174696f6e52656c61786174696f6e734174747269627574650052756e74696d65436f6d7061746962696c69747941747472696275746500576964676574417373656d626c79\ 0053657269616c697a61626c65417474726962757465004d6963726f736f66742e53716c5365727665722e5365727665720053716c55736572446566696e65645479706541747472696275746500466f726d6174005374727563744c61796f7574417474726962757465004c61796f75744b696e64004f626a65637400446f75\ 626c6500537472696e6700436f6e636174007300436861720053706c697400436f6e7665727400546f446f75626c6500457863657074696f6e00000000094e0055004c004c00000720002c0020000000c2045e585cc1a14482a4cff8f2f30ce70008b77a5c561934e08902060d0206020320000e032000020400001108060001\ 1108110d0320000d0328000204080011080328000d042001010e042001010205200101113d0420010108032000010520010111510801000100000000000520010111590500010e1d1c0607030e021d1c03070102060702110811080620011d0e1d030400010d0e0d070611081d0e12711108021d030307010d05010000000017\ 010012436f7079726967687420c2a920203230303700001001000b57696467657453746f636b00000801000701000000000801000800000000001e01000100540216577261704e6f6e457863657074696f6e5468726f7773010000000000000037054246000000000200000050000000f82a0000f81a0000525344533b7c1d67\ de5069438c7cdca95e1a89c101000000433a5c4464726976655c576f726b5c57696467657453746f636b5c6f626a5c44656275675c576964676574417373656d626c792e70646200702b000000000000000000008e2b0000002000000000000000000000000000000000000000000000802b0000000000000000000000000000\ 00005f436f72446c6c4d61696e006d73636f7265652e646c6c0000000000ff25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 00000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000000300000000000000000000000334000000560053005f00560045005200530049004f004e005f0049004e0046004f0000000000\ bd04effe00000100000001001b7b7d0a000001001b7b7d0a3f000000000000000400000002000000000000000000000000000000440000000100560061007200460069006c00650049006e0066006f00000000002400040000005400720061006e0073006c006100740069006f006e00000000000000b0046002000001005300\ 7400720069006e006700460069006c00650049006e0066006f0000003c020000010030003000300030003000340062003000000040000c000100460069006c0065004400650073006300720069007000740069006f006e0000000000570069006400670065007400530074006f0063006b00000040000f000100460069006c00\ 6500560065007200730069006f006e000000000031002e0030002e0032003600380035002e00330031003500310035000000000048001300010049006e007400650072006e0061006c004e0061006d006500000057006900640067006500740041007300730065006d0062006c0079002e0064006c006c000000000048001200\ 01004c006500670061006c0043006f007000790072006900670068007400000043006f0070007900720069006700680074002000a90020002000320030003000370000005000130001004f0072006900670069006e0061006c00460069006c0065006e0061006d00650000005700690064006700650074004100730073006500\ 6d0062006c0079002e0064006c006c000000000038000c000100500072006f0064007500630074004e0061006d00650000000000570069006400670065007400530074006f0063006b00000044000f000100500072006f006400750063007400560065007200730069006f006e00000031002e0030002e003200360038003500\ 2e00330031003500310035000000000048000f00010041007300730065006d0062006c0079002000560065007200730069006f006e00000031002e0030002e0032003600380035002e00330031003500310035000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 002000000c000000a03b00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\ 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 WITH PERMISSION_SET=SAFE GO CREATE TYPE SprocketSizes EXTERNAL NAME SprocketAssembly.Dimensions GO CREATE TABLE [dbo].[SprocketPrices] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [SprocketID] [int] NULL , [Price] [money] NULL , [DateValidFrom] [datetime] NULL , [DateValidTo] [datetime] NULL , [Active] [char] (1) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Sprockets] ( [RecordID] [int] IDENTITY (1, 1) NOT NULL , [SprocketName] [varchar] (50) NULL , [SKU] [varchar] (20) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[SprocketReferences] ( [SprocketID] [int] NOT NULL , [Reference] [varchar] (50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[SprocketReferences] WITH NOCHECK ADD CONSTRAINT [PK_SprocketReferences] PRIMARY KEY NONCLUSTERED ( [SprocketID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[SprocketPrices] WITH NOCHECK ADD CONSTRAINT [DF_SprocketPrices_DateValidFrom] DEFAULT (getdate()) FOR [DateValidFrom], CONSTRAINT [DF_SprocketPrices_Active] DEFAULT ('N') FOR [Active], CONSTRAINT [PK_SprocketPrices] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Sprockets] WITH NOCHECK ADD CONSTRAINT [PK_Sprockets] PRIMARY KEY NONCLUSTERED ( [RecordID] ) ON [PRIMARY] GO CREATE INDEX [IX_SprocketPrices] ON [dbo].[SprocketPrices]([SprocketID]) ON [PRIMARY] GO CREATE INDEX [IX_SprocketPrices_1] ON [dbo].[SprocketPrices]([DateValidFrom]) ON [PRIMARY] GO CREATE INDEX [IX_SprocketPrices_2] ON [dbo].[SprocketPrices]([DateValidTo]) ON [PRIMARY] GO GRANT SELECT ON [dbo].[SprocketPrices] TO [public] GO DENY REFERENCES , INSERT , DELETE , UPDATE ON [dbo].[SprocketPrices] TO [public] CASCADE GO GRANT SELECT ON [dbo].[Sprockets] TO [public] GO DENY REFERENCES , INSERT , DELETE , UPDATE ON [dbo].[Sprockets] TO [public] CASCADE GO ALTER TABLE [dbo].[SprocketPrices] ADD CONSTRAINT [FK_SprocketPrices_Sprockets] FOREIGN KEY ( [SprocketID] ) REFERENCES [dbo].[Sprockets] ( [RecordID] ) GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE VIEW dbo.CurrentPrices AS SELECT SprocketPrices.SprocketID, SprocketPrices.Price, Sprockets.SprocketName FROM dbo.Sprockets INNER JOIN dbo.SprocketPrices ON dbo.Sprockets.RecordID = dbo.SprocketPrices.SprocketID WHERE dbo.SprocketPrices.Active = 'Y' GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT SELECT ON [dbo].[CurrentPrices] TO [public] GO DENY INSERT , DELETE , UPDATE ON [dbo].[CurrentPrices] TO [public] CASCADE GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE prcActivatePrices AS UPDATE SprocketPrices SET Active='N' WHERE GetDate()DateValidFrom UPDATE SprocketPrices SET Active='Y' WHERE GetDate()>=DateValidFrom OR GetDate()<=DateValidFrom GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO DENY EXECUTE ON [dbo].[prcActivatePrices] TO [public] CASCADE GO CREATE SCHEMA [dispatch] GO CREATE TABLE [dispatch].[SprocketPackaging] ( [RecordID] [int] NOT NULL IDENTITY(1, 1), [SprocketID] [int] NOT NULL, [PackagingSize] [dbo].[SprocketSizes] NOT NULL, [Packing Description] [varchar] (250) COLLATE Latin1_General_CS_AS_KS_WS NULL ) GO ALTER TABLE [dispatch].[SprocketPackaging] ADD CONSTRAINT [PK_SprocketPacking] PRIMARY KEY NONCLUSTERED ([RecordID]) GO ALTER TABLE [dispatch].[SprocketPackaging] ADD CONSTRAINT [FK_Sprocket_SprocketPacking] FOREIGN KEY ([SprocketID]) REFERENCES [dbo].[Sprockets] ([RecordID]) GO CREATE VIEW [dispatch].[SprocketPackagingDetails] AS SELECT w.SprocketName AS 'Name', wp.[Packing Description], wp.[PackagingSize].FrontArea() AS 'Front Area', wp.[PackagingSize].TopArea() AS 'Top Area', wp.[PackagingSize].SideArea() AS 'Side Area', wp.[PackagingSize].Volume() AS 'Volume' FROM [dispatch].[SprocketPackaging] wp JOIN [dbo].Sprockets w ON w.RecordID = wp.SprocketID GO