10/6/2007 5:21:35 PM v1.3445 - Incorrect syntax near 'GO' Continue or restart VDM - This error was queued for analysis, we apologize. -- WebSites,WebPages,Accounts,TaxProfiles,Contacts,Ledger,Catalog,Resources,Warehouses,Components,Re 0001:0021AFB8 axView.txView.RunSQL line 6167 0001:0018F4B0 axPopup.txPopup.DoOnClick line 1028 base: 00B51000 -- WebSites,WebPages,Accounts,TaxProfiles,Contacts,Ledger,Catalog,Resources,Warehouses,Components,Relationships,Numbers,Orders,OrderItems,Specs,Serials,Activities,Variations,Notes,DataLog,Notifications -- above line is read by VDM to know the VDM Tables order for Import and Exports for now ------------- -- Fixes -- ------------- -- if exists ( select * from sysobjects where [Name] = 'Notifications' AND [Type] = 'U' ) drop table Notifications -- if exists ( select * from sysobjects where [Name] = 'DataLog' AND [Type] = 'U' ) drop table DataLog -- if exists ( select * from sysobjects where [Name] = 'Notes' AND [Type] = 'U' ) drop table Notes -- if exists ( select * from sysobjects where [Name] = 'Variations' AND [Type] = 'U' ) drop table Variations -- if exists ( select * from sysobjects where [Name] = 'Activities' AND [Type] = 'U' ) drop table Activities -- if exists ( select * from sysobjects where [Name] = 'Serials' AND [Type] = 'U' ) drop table Serials -- if exists ( select * from sysobjects where [Name] = 'Specs' AND [Type] = 'U' ) drop table Specs -- if exists ( select * from sysobjects where [Name] = 'OrderItems' AND [Type] = 'U' ) drop table OrderItems -- if exists ( select * from sysobjects where [Name] = 'Orders' AND [Type] = 'U' ) drop table Orders -- if exists ( select * from sysobjects where [Name] = 'Numbers' AND [Type] = 'U' ) drop table Numbers -- if exists ( select * from sysobjects where [Name] = 'Relationships' AND [Type] = 'U' ) drop table Relationships -- if exists ( select * from sysobjects where [Name] = 'Components' AND [Type] = 'U' ) drop table Components -- if exists ( select * from sysobjects where [Name] = 'Warehouses' AND [Type] = 'U' ) drop table Warehouses -- if exists ( select * from sysobjects where [Name] = 'Resources' AND [Type] = 'U' ) drop table Resources -- if exists ( select * from sysobjects where [Name] = 'Catalog' AND [Type] = 'U' ) drop table [Catalog] -- if exists ( select * from sysobjects where [Name] = 'Ledger' AND [Type] = 'U' ) drop table Ledger -- if exists ( select * from sysobjects where [Name] = 'Contacts' AND [Type] = 'U' ) drop table Contacts -- if exists ( select * from sysobjects where [Name] = 'TaxProfiles' AND [Type] = 'U' ) drop table TaxProfiles -- if exists ( select * from sysobjects where [Name] = 'Accounts' AND [Type] = 'U' ) drop table Accounts -- if exists ( select * from sysobjects where [Name] = 'WebPages' AND [Type] = 'U' ) drop table WebPages -- if exists ( select * from sysobjects where [Name] = 'WebSites' AND [Type] = 'U' ) drop table WebSites -- -- if exists ( select * from sysobjects where [Name] = 'optionprod' AND [Type] = 'U' ) drop table optionprod -- if exists ( select * from sysobjects where [Name] = 'category' AND [Type] = 'U' ) drop table category -- if exists ( select * from sysobjects where [Name] = 'product' AND [Type] = 'U' ) drop table product if exists ( select * from sysobjects where [Name] = 'DropDefault' AND [Type] = 'P' ) drop procedure dbo.DropDefault GO create procedure dbo.DropDefault( @TableName sysname, @ColName sysname ) as if exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = @TableName AND Column_Name = @ColName AND Column_Default IS NOT NULL ) begin declare @@Cmd varchar( 500 ) select @@Cmd = isNull( SO.[Name], '' ) from sysObjects SO join sysColumns SC on SO.[ID] = SC.cDefault where SC.[ID] = OBJECT_ID( @TableName ) AND SC.[Name] = @ColName if @@Cmd <> '' begin select @@Cmd = 'alter table ' + @TableName + ' drop constraint ' + @@Cmd exec( @@Cmd ) end end GO if exists ( select * from sysobjects where [Name] = 'DropConstraint' AND [Type] = 'P' ) drop procedure dbo.DropConstraint GO create procedure dbo.DropConstraint( @TableName sysname, @ColName sysname ) as if exists ( select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where Table_Name = @TableName AND Column_Name = @ColName ) begin declare @@Cmd varchar( 500 ) select @@Cmd = isNull( Constraint_Name, '' ) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where Table_Name = @TableName AND Column_Name = @ColName if @@Cmd <> '' begin select @@Cmd = 'alter table ' + @TableName + ' drop constraint ' + @@Cmd exec( @@Cmd ) end end GO -- Drop and recreate constraint -- -- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Activities' ) begin -- declare @@Cmd varchar( 500 ) -- if exists ( select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where Table_Name = 'Activities' AND Column_Name = 'Type' ) begin -- select @@Cmd = Constraint_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where Table_Name = 'Activities' AND Column_Name = 'Type' -- if @@Cmd <> '' begin -- select @@Cmd = 'alter table Activities drop constraint ' + @@Cmd -- exec( @@Cmd ) -- alter table Activities add check( [Type] IN -- ( 'Project', 'Schedule', 'Log', 'Sale', 'Purchase', 'Adjustment', 'Production', 'Manufacturing', 'Estimate', 'Template' ) ) -- end -- end -- end -- GO -- -- Drop and recreate default -- -- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin -- declare @@Cmd varchar( 500 ) -- Oct 13, 05 -- if exists ( select * from INFORMATION_SCHEMA.COLUMNS -- where Table_Name = 'Contacts' AND Column_Name = 'Type' AND Column_Default IS NOT NULL ) begin -- select @@Cmd = isNull( SO.[Name], '' ) from sysObjects SO join sysColumns SC on SO.[ID] = SC.cDefault -- where SC.[ID] = OBJECT_ID( 'Contacts' ) AND SC.[Name] = 'Type' -- if @@Cmd <> '' begin -- select @@Cmd = 'alter table Contacts drop constraint ' + @@Cmd -- exec( @@Cmd ) -- alter table Contacts add default 'Contact' for [Type] -- end -- end -- select @@Cmd = isNull( Constraint_Name, '' ) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE -- where Table_Name = 'Contacts' AND Column_Name = 'Type' begin -- if @@Cmd <> '' begin -- select @@Cmd = 'alter table Contacts drop constraint ' + @@Cmd -- exec( @@Cmd ) -- end -- end -- update Contacts set Can_be_a_Client = 0 where NOT ( [Type] IN ( 'Client', 'Both' ) ) -- update Contacts set Can_be_a_Supplier = 1 where [Type] IN ( 'Supplier', 'Both' ) -- update Contacts set [Type] = 'Contact' where [Type] IN ( 'Client', 'Supplier', 'Both', 'Other' ) -- end -- GO -- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin -- alter table Contacts add constraint CK_Contacts_Type check ( [Type] IN ( 'Contact', 'Employee', 'ShipTo' ) ) -- end -- GO --if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'sample' ) begin -- if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'sample' AND Column_Name = 'ID' ) begin -- alter table Sample add ID int NULL -- end --end --GO -------------- -- WebSites -- -------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebSites' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'WebSites' AND Column_Name = 'Meta_Description' ) begin alter table WebSites add Meta_Description varchar( 2000 ) Null, Meta_Keywords varchar( 2000 ) Null end end GO if NOT exists ( select * from sysobjects where [Name] = 'WebSites' AND [Type] = 'U' ) create table dbo.WebSites ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, Template_ID int Null, Default_Template_ID int Null, Version int Null default( 0 ), Date_Created datetime default GetDate(), Date_Modified datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 10 ) default( 'Menu' ) check ( [Type] IN ( 'Menu', 'Data', 'Content' ) ), [Name] char( 50 ) NOT Null, Template_Name char( 50 ) Null, Default_Template_Name char( 50 ) Null, Name2 char( 50 ) Null, Name3 char( 50 ) Null, Form_Name char( 50 ) Null, Filter text Null, FTP_Home_Address text Null, FTP_HTML_Path text Null, FTP_Username char( 50 ) Null, FTP_Password char( 50 ) Null, Destination_URL varchar( 500 ) Null, Destination_Path varchar( 500 ) Null, Local_Path varchar( 500 ) Null, Original_Home_Address varchar( 500 ) Null, Menu_Type char( 50 ) default( 'Top-Drop-Down' ) check ( Menu_Type IN ( 'Top-Drop-Down', 'Left-Drop-Right', 'Top-then-Left', 'Left-then-Top', 'Top-then-Line-2' ) ), Menu_Height_in_Pixels int default( 25 ), Menu_Width_in_Pixels int default( 800 ), SubMenu_Height_in_Pixels int default( 0 ), SubMenu_Width_in_Pixels int default( 0 ), Menu_Font char( 50 ) default( 'bold 9pt arial' ), Menu_Item_Font char( 50 ) default( 'bold 9pt arial' ), Menu_Color char( 50 ) default( 'white' ), Border_Color char( 50 ) default( 'black' ), Menu_Background char( 50 ) default( '#000080' ), Menu_Hover_Color char( 50 ) default( 'yellow' ), Menu_Hover_Background char( 50 ) default( '#008080' ), Menu_Border bit default( 0 ), Drop_Menu_Border bit default( 0 ), Menu_Item_Border bit default( 0 ), Meta_Description varchar( 2000 ) Null, Meta_Keywords varchar( 2000 ) Null, Note text NULL, Audio image NULL, Video image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, constraint PK_WebSites PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_WebSites_GUID unique ( GUID ), constraint IX_WebSites UNIQUE ( [Name] ) ) GO deny update ( Date_Created ) on WebSites to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebSites' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_WebSites' ) begin create unique index IX_WebSItes on WebSItes( [Name] ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebSites' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_WebSites_GUID' ) begin create unique index IX_WebSItes_GUID on WebSItes( GUID ) end end GO -------------- -- WebPages -- -------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebPages' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'WebPages' AND Column_Name = 'Meta_Description' ) begin alter table WebPages add Meta_Description varchar( 2000 ) Null, Meta_Keywords varchar( 2000 ) Null end end GO if NOT exists ( select * from sysobjects where [Name] = 'WebPages' AND [Type] = 'U' ) create table dbo.WebPages ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, WebSite_ID int Null, WebPage_ID int Null, Template_ID int Null, Version int Null default( 0 ), Date_Created datetime default GetDate(), Date_Modified datetime Null, Date_Verified datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL, Title char( 50 ) NOT Null, Template_Name char( 50 ) Null, Title2 char( 50 ) Null, Title3 char( 50 ) Null, Form_Name char( 50 ) Null, Filter text Null, Create_Pages_from_Column char( 50 ) Null, Destination_URL varchar( 500 ) Null, Destination_Path varchar( 500 ) Null, Local_Path varchar( 500 ) Null, Parent_URL varchar( 500 ) Null, Use_as_Template bit Null default( 0 ), Active bit Null default( 1 ), Core_Page bit Null default( 0 ), Menu_Page bit Null default( 0 ), Missing_Page bit Null default( 0 ), Page_Level int Null default( 0 ), Reference_Count int Null default( 0 ), HTML_Text text NULL, Meta_Description varchar( 2000 ) Null, Meta_Keywords varchar( 2000 ) Null, Note text NULL, Audio image NULL, Video image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, constraint PK_WebPages PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_WebPages_GUID unique ( GUID ), constraint FK_WebPages_WebSites FOREIGN KEY ( WebSite_ID ) REFERENCES WebSites ( [ID] ) on delete cascade, constraint FK_WebPages_WebPages FOREIGN KEY ( WebPage_ID ) REFERENCES WebPages ( [ID] ), constraint FK_WebPages_Templates FOREIGN KEY ( Template_ID ) REFERENCES WebPages ( [ID] ) ) GO deny update ( Date_Created ) on WebPages to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebPages' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_WebPages_Sequence' ) begin create index IX_WebPages_Sequence on WebPages( WebSite_ID, WebPage_ID, Sequence ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'WebPages' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_WebPages_Title' ) begin create index IX_WebPages_Title on WebPages( Title ) end end GO -------------- -- Accounts -- -------------- if NOT exists ( select * from sysobjects where [Name] = 'Accounts' AND [Type] = 'U' ) create table dbo.Accounts ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, Version int default( 0 ), Total_Account_ID int Null, Date_Created datetime default GetDate(), Date_Modified datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL, [Name] char( 50 ) NOT NULL , [Type] char( 10 ) default 'Debit' check ( [Type] IN ( 'Debit', 'Credit' ) ), [Group] char( 50 ) NULL , Balance decimal(18, 4) default (0), Total_to_Account char(50) NULL, Account_is_Total bit default (0), Account_is_AR bit default (0), Account_is_AP bit default (0), Account_is_Bank bit default (0), [Change_Notification_List] varchar(100) NULL , Notification_Condition varchar( 500 ) NULL, [Late_Notification_List] varchar(100) NULL , [Notify_when_Late_by_Days] decimal(15, 4) NULL default(1.5), [Late_Reminder_every_Hours] decimal(15, 4) NULL default(4), [Follow-Up_Notification_List] varchar(100) NULL, [Follow-Up_every_Days] decimal(15, 4) NULL default(5), [Late_Follow-Up_Reminder_every_Hours] decimal(15, 4) NULL default(24), [Date_Last_Follow-Up_Done] datetime NULL, Note text NULL, Audio image NULL, Video image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, constraint PK_Accounts PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Accounts_GUID unique ( GUID ), constraint IX_Accounts_Name UNIQUE ( [Name] ), constraint FK_Accounts_Accounts FOREIGN KEY ( Total_Account_ID ) REFERENCES Accounts ( [ID] ) ) GO deny update ( Date_Created, Balance ) on Accounts to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Accounts' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Accounts_Sequence' ) begin create index IX_Accounts_Sequence on Accounts( Sequence ) end end GO ------------------ -- Tax Profiles -- ------------------ if NOT exists ( select * from sysobjects where [Name] = 'TaxProfiles' AND [Type] = 'U' ) CREATE TABLE dbo.TaxProfiles ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Tax1_Account_ID int Null, Tax2_Account_ID int Null, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Effective] datetime default GetDate() , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Name] char( 50 ) NOT NULL, Tax1_Percent decimal(18, 4) default (0), Tax1_Account char( 50 ) Null, Tax1_is_Taxable bit default (0), Tax2_Percent decimal(18, 4) default (0), Tax2_Account char( 50 ) Null, [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_TaxProfiles] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_TaxProfiles_GUID unique ( GUID ), constraint [FK_TaxProfiles_Tax1] foreign key ( Tax1_Account_ID ) references Accounts ( [ID] ), constraint [FK_TaxProfiles_Tax2] foreign key ( Tax2_Account_ID ) references Accounts ( [ID] ) ) GO deny update ( Date_Created ) on TaxProfiles to VDM GO if ( select Count( [Name] ) from TaxProfiles ) = 0 begin insert TaxProfiles ([Name], Tax1_Percent ) values( 'GST', 6 ) insert TaxProfiles ([Name], Tax1_Percent ) values( 'HST', 15 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent, Tax1_is_Taxable ) values( 'GST/BC', 6, 7, 1 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent ) values( 'GST/SK', 6, 5 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent ) values( 'GST/MA', 6, 7 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent ) values( 'GST/ON', 6, 8 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent, Tax1_is_Taxable ) values( 'GST/QC', 6, 7.5, 1 ) insert TaxProfiles ([Name], Tax1_Percent, Tax2_Percent, Tax1_is_Taxable ) values( 'GST/PEI', 6, 10, 1 ) end GO -------------- -- Contacts -- -------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Contacts' AND Column_Name = 'Password1' ) begin alter table Contacts add Password1 varchar( 20 ) Null, Password2 varchar( 20 ) Null end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Contacts' AND Column_Name = 'Gender' ) begin alter table Contacts add Gender char(1) NULL default ('M') check ( Gender IN ( 'M', 'F' ) ), Salutation varchar( 20 ) NULL, Title varchar( 20 ) NULL end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Contacts' AND Column_Name = 'Credit_Card' ) begin alter table Contacts add Credit_Card varchar( 20 ) NULL, Card_Number varchar( 500 ) NULL, Card_Expiry varchar( 10 ) NULL, Card_Code varchar( 10 ) Null end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Contacts' AND Column_Name = 'Expiry_Date' ) begin alter table Contacts drop column Expiry_Date alter table Contacts add Card_Expiry varchar( 10 ) NULL end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Contacts' AND Column_Name = 'First_Name' ) begin alter table Contacts add First_Name varchar( 30 ) NULL , Last_Name varchar( 30 ) NULL end end GO if NOT exists ( select * from sysobjects where [Name] = 'Contacts' AND [Type] = 'U' ) CREATE TABLE dbo.Contacts ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Contact_ID int Null, Main_Contact_ID int Null, Sales_Account_ID int Null, AR_Account_ID int Null, AP_Account_ID int Null, Asset_Account_ID int Null, COGS_Account_ID int Null, Expense_Account_ID int Null, Payment_Account_ID int Null, Payment1_Account_ID int Null, Payment2_Account_ID int Null, Payment3_Account_ID int Null, Payment4_Account_ID int Null, Payment5_Account_ID int Null, Payment6_Account_ID int Null, Payment7_Account_ID int Null, Payment8_Account_ID int Null, Payment9_Account_ID int Null, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Outlook] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) NULL default ('Contact') check ( [Type] IN ( 'Contact', 'Employee', 'ShipTo' ) ), [Group] char(50) NULL , Employee char( 50 ) NULL, [Name] char(50) NOT NULL , Main_Contact_Name char(50) NULL, Gender char(1) NULL default ('M') check ( Gender IN ( 'M', 'F' ) ), Salutation varchar( 20 ) NULL, Title varchar( 20 ) NULL, First_Name varchar( 30 ) NULL , Last_Name varchar( 30 ) NULL , [Address] char(100) NULL , [City] char(50) NULL , [State_or_Province] char(50) NULL , [Postal_Code] char(50) NULL , [Country] char(50) NULL , [Phone] char(50) NULL , [Fax] char(50) NULL , [Mobile] char(50) NULL , [Pager] char(50) NULL , [eMail] char(50) NULL , [Web_Page] char(100) NULL , [Password1] varchar(20) NULL , [Password2] varchar(20) NULL , [Currency] char(5) NULL, [Credit_Limit] decimal(18, 4) default (0), [Balance] decimal(18, 4) default (0), Credit_Card varchar( 20 ) NULL, Card_Number varchar( 500 ) NULL, Card_Expiry varchar( 10 ) NULL, Card_Code varchar( 10 ) Null, [Avg_Amount_per_Month] decimal(18, 4) default (0), [Tax_Profile] char(50) default ('GST/QC') , Price_Level char(1) default ('0') check ( Price_Level IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) ), [Currently_Active_Since] datetime NULL , [Pause_Started] datetime NULL , [Pause_Minutes] int default( 0 ), [Pause_Ends] datetime NULL , [Active] bit default( 1 ), [Hold_All_Activity] bit default( 0 ), [Can_be_a_Client] [bit] default (1), [Can_be_a_Supplier] [bit] default (0), Company_Configuration [bit] default (0), Sales_Account char( 50 ) Null, AR_Account char( 50 ) Null, AP_Account char( 50 ) Null, Asset_Account char( 50 ) Null, COGS_Account char( 50 ) Null, Expense_Account char( 50 ) Null, Payment_Account varchar( 50 ) Null, Payment1_Account varchar( 50 ) Null, Payment2_Account varchar( 50 ) Null, Payment3_Account varchar( 50 ) Null, Payment4_Account varchar( 50 ) Null, Payment5_Account varchar( 50 ) Null, Payment6_Account varchar( 50 ) Null, Payment7_Account varchar( 50 ) Null, Payment8_Account varchar( 50 ) Null, Payment9_Account varchar( 50 ) Null, [Regular_Days_List] char( 100 ) default( 'Monday,Tuesday,Wednesday,Thursday,Friday' ), [Regular_Day_Hours_List] char( 50 ) default( '9-17' ), [Sunday_Hours_List] char( 50 ) NULL, [Monday_Hours_List] char( 50 ) NULL, [Tuesday_Hours_List] char( 50 ) NULL, [Wednesday_Hours_List] char( 50 ) NULL, [Thursday_Hours_List] char( 50 ) NULL, [Friday_Hours_List] char( 50 ) NULL, [Saturday_Hours_List] char( 50 ) NULL, [Change_Notification_List] varchar(100) NULL , Notification_Condition varchar( 500 ) NULL, [Late_Notification_List] varchar(100) NULL , [Notify_when_Late_by_Days] decimal(15, 4) NULL default(1.5), [Late_Reminder_every_Hours] decimal(15, 4) NULL default(4), [Follow-Up_Notification_List] varchar(100) NULL, [Follow-Up_every_Days] decimal(15, 4) NULL default(5), [Late_Follow-Up_Reminder_every_Hours] decimal(15, 4) NULL default(24), [Date_Last_Follow-Up_Done] datetime NULL, Note text NULL , [Audio] image NULL, [Video] image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Contacts_GUID unique ( GUID ), CONSTRAINT [IX_Contacts] UNIQUE ( [Name] ), constraint [FK_Contacts_Sales] foreign key ( Sales_Account_ID ) references Accounts ( [ID] ), constraint [FK_Contacts_AR] foreign key ( AR_Account_ID ) references Accounts ( [ID] ), constraint [FK_Contacts_AP] foreign key ( AP_Account_ID ) references Accounts ( [ID] ), constraint [FK_Contacts_Asset] foreign key ( Asset_Account_ID ) references Accounts ( [ID] ), constraint [FK_Contacts_Expense] foreign key ( Expense_Account_ID ) references Accounts ( [ID] ), constraint [FK_Contacts_COGS] foreign key ( COGS_Account_ID ) references Accounts ( [ID] ) ) GO deny update ( Date_Created ) on Contacts to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Contacts' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Contacts_Configuration' ) begin create index IX_Contacts_Configuration on Contacts( Company_Configuration ) end end GO ------------ -- Ledger -- ------------ if exists ( select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where Table_Name = 'Ledger' AND Constraint_Name = 'FK_Ledger_CrossName' ) begin alter table Ledger drop constraint FK_Ledger_CrossName end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Ledger' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Ledger' AND Column_Name = 'Account_ID' ) begin alter table Ledger add Account_ID int Null end end GO if NOT exists ( select * from sysobjects where [Name] = 'Ledger' AND [Type] = 'U' ) create table dbo.Ledger ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, Account_ID int Null, Cross_Account_ID int Null, Contact_ID int Null, Ledger_ID int Null, Version int default( 0 ), Date_Created datetime default GetDate(), Date_Modified datetime Null, Date_Locked datetime Null, Date_Printed datetime Null, Date datetime Null default GetDate(), ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL, Apply_Sequence float NULL, Account_Name char(50) NULL, Account_Type char( 10 ) default 'Debit' check ( Account_Type IN ( 'Debit', 'Credit' ) ), Number char(20) NULL, Contact_Name char(50) NULL, Reference char(50) NULL, Amount decimal(18, 4) default (0), Balance decimal(18, 4) default (0), Contact_Balance decimal(18, 4) default (0), Apply_Balance decimal(18, 4) default (0), Cross_Account char(50) NULL , Note text NULL, isDuplicate bit default( 0 ), Account_is_Total bit default (0), Account_is_AR bit default (0), Account_is_AP bit default (0), Account_is_Bank bit default (0), Audio image NULL, Video image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, constraint PK_Ledger primary key clustered ( [ID] ), constraint IX_Ledger_GUID unique ( GUID ), constraint FK_Ledger_Accounts foreign key ( Account_ID ) references Accounts ( [ID] ), constraint FK_Ledger_CrossAct foreign key ( Cross_Account_ID ) references Accounts ( [ID] ), constraint FK_Ledger_Ledger foreign key ( Ledger_ID ) references Ledger ( [ID] ), constraint FK_Ledger_Contacts foreign key ( Contact_ID ) references Contacts ( [ID] ) ) GO deny update ( Date_Created, Balance, Apply_Balance, Contact_Balance ) on Ledger to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Ledger' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Ledger_Sequence' ) begin create index IX_Ledger_Sequence on Ledger( Account_ID, Ledger_ID, Sequence ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Ledger' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Ledger_AR' ) begin create index IX_ledger_AR on Ledger( Account_is_AR ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Ledger' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Ledger_AP' ) begin create index IX_ledger_AP on Ledger( Account_is_AP ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Ledger' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Ledger_Bank' ) begin create index IX_ledger_Bank on Ledger( Account_is_Bank ) end end GO ------------- -- Catalog -- ------------- if NOT exists ( select * from sysobjects where [Name] = 'Catalog' AND [Type] = 'U' ) CREATE TABLE dbo.[Catalog] ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) default 'Product' check ( [Type] IN ( 'Product', 'Template' ) ), [Name] char(50) NULL , [Name2] char(50) NULL , [Name3] char(50) NULL , [Code] char(20) NULL , [Code2] char(20) NULL , [Code3] char(20) NULL , [Group] char(50) NULL , [Group2] char(50) NULL , [Group3] char(50) NULL , [Department] char(50) NULL , [Department2] char(50) NULL , [Department3] char(50) NULL , [Division] char(50) NULL , [Division2] char(50) NULL , [Division3] char(50) NULL , Price decimal(18, 4) default (0), Price1 decimal(18, 4) default (0), Price2 decimal(18, 4) default (0), Price3 decimal(18, 4) default (0), Price4 decimal(18, 4) default (0), Price5 decimal(18, 4) default (0), Price6 decimal(18, 4) default (0), Price7 decimal(18, 4) default (0), Price8 decimal(18, 4) default (0), Price9 decimal(18, 4) default (0), [Std_Cost] decimal(18, 4) default (0), [Publish_this_Resource] [bit] default (0), [Manage_this_Resource] [bit] default (0), [Update_eCommerce] [bit] default (0), [Description] text NULL , [Description2] text NULL , [Description3] text NULL , [Note] text NULL , [Sizes] char(50) NULL , [Sizes2] char(50) NULL , [Sizes3] char(50) NULL , [Colours] text NULL , [Colours2] text NULL , [Colours3] text NULL , [Delivery] char(50) NULL , [Delivery2] char(50) NULL , [Delivery3] char(50) NULL , Template_Name char( 50 ) NULL , [Fabric_Code] char(20) NULL , [Fabric_Name] char(50) NULL , [Fabric_Name2] char(50) NULL , [Fabric_Name3] char(50) NULL , [Fabric_Care] char(50) NULL , [Fabric_Care2] char(50) NULL , [Fabric_Care3] char(50) NULL , [Fabric_Composition] text NULL , [Fabric_Composition2] text NULL , [Fabric_Composition3] text NULL , [Section_Title] char(50) NULL , [Section_Title2] char(50) NULL , [Section_Title3] char(50) NULL , [Section_Index_in_Book] decimal(18, 4) default (0), [Item_Index_in_Section] decimal(18, 4) default (0), [Audio] image NULL, [Video] image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, CONSTRAINT [PK_Catalog] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Catalog_GUID unique nonclustered ( GUID ), ) GO deny update ( Date_Created ) on Catalog to VDM GO ------------- -- Resources -- ------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Resources' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Resources' AND Column_Name = 'Date_Start' ) begin alter table Resources add Date_Start datetime NULL, Date_Stop datetime NULL end end GO if NOT exists ( select * from sysobjects where [Name] = 'Resources' AND [Type] = 'U' ) CREATE TABLE dbo.Resources ( [ID] int IDENTITY( 1, 1 ) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [Catalog_ID] int NULL , Last_Count_ID int NULL , Asset_Account_ID int NULL , Sales_Account_ID int NULL , COGS_Account_ID int NULL , Expense_Account_ID int NULL , Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) default ('Product') check ( [Type] IN ( 'Product', 'Equipment', 'Task', 'Contact', 'Template' ) ), [Name] char(50) NOT NULL , [Template_Name] char(50) NULL , [Code] char(20) NULL , [UPC] char(20) NULL , [Group] char(50) NULL , [Department] char(50) NULL , [Division] char(50) NULL , Price decimal(18, 4) default (0), Price1 decimal(18, 4) default (0), Price2 decimal(18, 4) default (0), Price3 decimal(18, 4) default (0), Price4 decimal(18, 4) default (0), Price5 decimal(18, 4) default (0), Price6 decimal(18, 4) default (0), Price7 decimal(18, 4) default (0), Price8 decimal(18, 4) default (0), Price9 decimal(18, 4) default (0), [Date_Start] datetime NULL, [Date_Stop] datetime NULL, [Taxable] [bit] default (1), [Publish_this_Resource] [bit] default (0), [Update_eCommerce] [bit] default (0), [Track_Qty_on_Hand] [bit] default (1), [Track_Lot_Numbers] [bit] default (0), [Track_Serial_Numbers] [bit] default (0), [Can_be_Sold] [bit] default (1), [Can_be_Purchased] [bit] default (1), [Can_be_Manufactured] [bit] default (0) NULL, [Can_be_a_Component] [bit] default (0) NULL, Last_Supplier char( 50 ) Null, [Std_Cost] decimal(18, 4) default (0), [Mfg_Cost] decimal(18, 4) default (0), [Avg_Cost] decimal(18, 4) default (0), [Units_on_Hand] decimal(18, 4) default (0), [Units_Ordered] decimal(18, 4) default (0), [Units_Required] decimal(18, 4) default (0), Unit char( 20 ) default( 'Each' ), Order_Point decimal( 18, 4 ) default( 0 ), Order_Quantity decimal( 18, 4 ) default( 0 ), Manual_Order_Point bit default( 0 ), [Avg_Usage_per_Month] decimal(18, 4) default (0), [Avg_Days_to_Replenish] int default (0), Date_Locked_for_Count datetime NULL, Date_Released_from_Count datetime NULL, Date_Quantity_Adjusted datetime NULL, Snapshot_Quantity decimal(18, 4) default (0), Quantity_Counted decimal(18, 4) default (0), Storage_Areas_List varchar( 100 ) Null, [Currently_Active_Since] datetime NULL , [Pause_Started] datetime NULL , [Pause_Minutes] int default( 0 ), [Pause_Ends] datetime NULL , [Active] bit default( 1 ), [Hold_All_Activity] bit default( 0 ), Asset_Account char( 50 ) NULL , Sales_Account char( 50 ) NULL , COGS_Account char( 50 ) NULL , Expense_Account char( 50 ) NULL , [Regular_Days_List] char( 100 ) default( 'Monday,Tuesday,Wednesday,Thursday,Friday' ), [Regular_Day_Hours_List] char( 50 ) default( '9-17' ), [Sunday_Hours_List] char( 50 ) NULL, [Monday_Hours_List] char( 50 ) NULL, [Tuesday_Hours_List] char( 50 ) NULL, [Wednesday_Hours_List] char( 50 ) NULL, [Thursday_Hours_List] char( 50 ) NULL, [Friday_Hours_List] char( 50 ) NULL, [Saturday_Hours_List] char( 50 ) NULL, [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , [Change_Notification_List] varchar(100) NULL , Notification_Condition varchar( 500 ) NULL, [Late_Notification_List] varchar(100) NULL , [Notify_when_Late_by_Days] decimal(15, 4) NULL default(1.5), [Late_Reminder_every_Hours] decimal(15, 4) NULL default(4), [Follow-Up_Notification_List] varchar(100) NULL, [Follow-Up_every_Days] decimal(15, 4) NULL default(5), [Late_Follow-Up_Reminder_every_Hours] decimal(15, 4) NULL default(24), [Date_Last_Follow-Up_Done] datetime NULL, [Note] text NULL , [Audio] image NULL, [Video] image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Resources_GUID unique nonclustered ( GUID ), CONSTRAINT [IX_Resources_Name] UNIQUE NONCLUSTERED ( [Name] ), CONSTRAINT [IX_Resources_Code] UNIQUE NONCLUSTERED ( [Code] ), CONSTRAINT [IX_Resources_UPC] UNIQUE NONCLUSTERED ( [UPC] ), constraint [FK_Resources_Sales] foreign key ( Sales_Account_ID ) references Accounts ( [ID] ), constraint [FK_Resources_Asset] foreign key ( Asset_Account_ID ) references Accounts ( [ID] ), constraint [FK_Resources_COGS] foreign key ( COGS_Account_ID ) references Accounts ( [ID] ), constraint [FK_Resources_Expense] foreign key ( Expense_Account_ID ) references Accounts ( [ID] ) ) GO deny update ( Date_Created, Avg_Cost, Units_on_Hand, Units_Ordered, Units_Required ) on Resources to VDM GO ---------------- -- Warehouses -- ---------------- if NOT exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Warehouses' ) CREATE TABLE dbo.Warehouses ( ID int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Resource_ID int NOT NULL , Contact_ID int NULL, Last_Count_ID int NULL , Asset_Account_ID int NULL , Sales_Account_ID int NULL , COGS_Account_ID int NULL , Expense_Account_ID int NULL , Date_Created datetime default GetDate() , Date_Modified datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), Resource_Name varchar( 50 ) NULL , Contact_Name varchar( 50 ) NULL , Variations_List varchar( 500 ) NULL, [Sub-Variations_List] varchar( 500 ) NULL, Price decimal(18, 4) default (0), Price1 decimal(18, 4) default (0), Price2 decimal(18, 4) default (0), Price3 decimal(18, 4) default (0), Price4 decimal(18, 4) default (0), Price5 decimal(18, 4) default (0), Price6 decimal(18, 4) default (0), Price7 decimal(18, 4) default (0), Price8 decimal(18, 4) default (0), Price9 decimal(18, 4) default (0), [Taxable] [bit] default (1), [Std_Cost] decimal(18, 4) default (0), [Mfg_Cost] decimal(18, 4) default (0), [Avg_Cost] decimal(18, 4) default (0), [Units_on_Hand] decimal(18, 4) default (0), [Units_Ordered] decimal(18, 4) default (0), [Units_Required] decimal(18, 4) default (0), Order_Point decimal( 18, 4 ) default( 0 ), Order_Quantity decimal( 18, 4 ) default( 0 ), Manual_Order_Point bit default( 0 ), [Avg_Usage_per_Month] decimal(18, 4) default (0), [Avg_Days_to_Replenish] int default (0), Date_Locked_for_Count datetime NULL, Date_Released_from_Count datetime NULL, Date_Quantity_Adjusted datetime NULL, Snapshot_Quantity decimal(18, 4) default (0), Quantity_Counted decimal(18, 4) default (0), Storage_Areas_List varchar( 100 ) Null, Asset_Account char( 50 ) NULL , Sales_Account char( 50 ) NULL , COGS_Account char( 50 ) NULL , Expense_Account char( 50 ) NULL , Note text Null, Audio image NULL, Video image NULL, CONSTRAINT PK_Warehouses PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Warehouses_GUID unique nonclustered ( GUID ), CONSTRAINT FK_Warehouses_Resources FOREIGN KEY ( Resource_ID ) REFERENCES Resources ( ID ) ON DELETE CASCADE, CONSTRAINT FK_Warehouses_Contacts FOREIGN KEY ( Contact_ID ) REFERENCES Contacts ( ID ), constraint FK_Warehouses_Sales foreign key ( Sales_Account_ID ) references Accounts ( ID ), constraint FK_Warehouses_Asset foreign key ( Asset_Account_ID ) references Accounts ( ID ), constraint FK_Warehouses_COGS foreign key ( COGS_Account_ID ) references Accounts ( ID ), constraint FK_Warehouses_Expense foreign key ( Expense_Account_ID ) references Accounts ( ID ) ) GO deny update ( Date_Created ) on Warehouses to VDM GO ---------------- -- Components -- ---------------- if NOT exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Components' ) CREATE TABLE dbo.Components ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Resource_ID int NOT NULL , Component_ID int NULL, Contact_ID int NULL, [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL , Resource_Code char(20) NULL , Resource_Name char(50) NULL , [Contact_Name] char(50) NULL , [Units_Required] decimal(18, 4) default (0), [Hours_Required] decimal(18, 4) default (0), [Percent_Required] decimal(18, 4) default (0), [One_Time_Setup] [bit] default (0) NULL, [Avg_Cost] decimal(18, 4) default (0), [Total_Cost] decimal(18, 4) default (0), [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , [Note] text Null, [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Components] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Components_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Components_Resources] FOREIGN KEY ( [Resource_ID] ) REFERENCES [Resources] ( [ID] ) ON DELETE CASCADE ) GO deny update ( Date_Created ) on Components to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Components' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Components_Sequence' ) begin create index IX_Components_Sequence on Components( Resource_ID, Sequence ) end end GO ----------- -- Specs -- ----------- if NOT exists ( select * from sysobjects where [Name] = 'Specs' AND [Type] = 'U' ) CREATE TABLE dbo.Specs ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Resource_ID int NULL, OrderItem_ID int NULL, [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL , [Name] char(50) NOT NULL , [Value] char(50) NULL , [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , [Note] text Null, [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Specs] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Specs_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Specs_Resources] FOREIGN KEY ( Resource_ID ) REFERENCES Resources ( [ID] ) ON DELETE CASCADE -- CONSTRAINT [FK_Specs_OrderItems] FOREIGN KEY ( OrderItem_ID ) REFERENCES OrderItems ( [ID] ) ON DELETE CASCADE ) GO deny update ( Resource_ID, Date_Created ) on Specs to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Specs' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Specs_Sequence' ) begin create index IX_Specs_Sequence on Specs( Resource_ID, OrderItem_ID, Sequence ) end end GO ------------------- -- Relationships -- ------------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Relationships' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Relationships' AND Column_Name = 'Percent_Discount' ) begin alter table Relationships add Percent_Discount decimal(18, 4) default (0) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Relationships' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Relationships' AND Column_Name = 'Resource_ID' ) begin alter table Relationships add Resource_ID int Null end end GO if NOT exists ( select * from sysobjects where [Name] = 'Relationships' AND [Type] = 'U' ) CREATE TABLE dbo.Relationships ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [Contact_ID] int NULL , Resource_ID int NULL , Related_ID int NULL , [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) NULL , [Contact_Name] char(50) NULL , [Resource_Name] char(50) NULL , [Reference_Code] char(20) NULL , [Price] decimal(18, 4) default (0), Percent_Discount decimal(18, 4) default (0), [Minimum_Quantity] decimal(18, 4) default (0), [Date_Start] datetime NULL , [Date_Stop] datetime NULL , [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , [Note] Text Null, CONSTRAINT [PK_Relationships] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Relationships_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Relationships_Contacts] FOREIGN KEY ( [Contact_ID] ) REFERENCES [Contacts] ( [ID] ) ON DELETE CASCADE ) GO deny update ( Contact_ID, Date_Created ) on Relationships to VDM GO update Relationships set Percent_Discount = 0 where Percent_Discount is Null GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Relationships' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Relationships_Contact_Name' ) begin create index IX_Relationships_Contact_Name on Relationships( Contact_Name ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Relationships' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Relationships_Resources' ) begin create index IX_Relationships_Resources on Relationships( Resource_ID ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Relationships' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Relationships_Resource_Name' ) begin create index IX_Relationships_Resource_Name on Relationships( Resource_Name ) end end GO ------------ -- Orders -- ------------ if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Orders' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Orders' AND Column_Name = 'Credit_Card' ) begin alter table Orders add Credit_Card varchar( 20 ) NULL, Card_Number varchar( 500 ) NULL, Card_Expiry varchar( 10 ) NULL, Card_Code varchar( 10 ) Null end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Orders' ) begin if exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Orders' AND Column_Name = 'Date_Expiry' ) begin alter table Orders drop column Date_Expiry alter table Orders add Expiry_Date varchar( 10 ) NULL end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Orders' ) begin if exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Orders' AND Column_Name = 'Expiry_Date' ) begin alter table Orders drop column Expiry_Date alter table Orders add Card_Expiry varchar( 10 ) NULL end end GO if NOT exists ( select * from sysobjects where [Name] = 'Orders' AND [Type] = 'U' ) CREATE TABLE dbo.Orders ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [BackOrder_ID] int NULL , Deposit_ID int NULL , [Contact_ID] int NULL , Main_Contact_ID int Null, [Employee_ID] int NULL , Sales_Account_ID int Null, AR_Account_ID int Null, AP_Account_ID int Null, Asset_Account_ID int Null, COGS_Account_ID int Null, Expense_Account_ID int Null, Payment_Account_ID int Null, Payment1_Account_ID int Null, Payment2_Account_ID int Null, Payment3_Account_ID int Null, Payment4_Account_ID int Null, Payment5_Account_ID int Null, Payment6_Account_ID int Null, Payment7_Account_ID int Null, Payment8_Account_ID int Null, Payment9_Account_ID int Null, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , [Date_Voided] datetime NULL , [Date_Printed] datetime NULL , [Date_Required] datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) check ( [Type] IN ( 'Sale', 'Purchase', 'Adjustment', 'Production', 'Manufacturing', 'Estimate', 'Service' ) ), [Group] char( 20 ) Null, Repeat_Frequency char( 20 ) NULL default( 'None' ) check ( Repeat_Frequency IN ( 'None', 'Daily', 'Weekly', 'Bi-Monthly', 'Monthly', 'Quaterly', 'Yearly' ) ), [Employee] char(50) NULL , Number char( 20 ) NULL, Reference char( 50 ) NULL, Referred_by char( 50 ) NULL, [Name] char(50) NOT NULL , Main_Contact_Name char(50) NULL, [Address] char(100) NULL , [City] char(50) NULL , [State_or_Province] char(50) NULL , [Postal_Code] char(50) NULL , [Country] char(50) NULL , [Phone] char(50) NULL , [Fax] char(50) NULL , [eMail] char(50) NULL , [Ship_Name] char(50) NULL , [Ship_Address] char(100) NULL , [Ship_City] char(50) NULL , [Ship_State_or_Province] char(50) NULL , [Ship_Postal_Code] char(50) NULL , [Ship_Country] char(50) NULL , [Ship_Phone] char(50) NULL , [Ship_Fax] char(50) NULL , [Ship_eMail] char(50) NULL , Credit_Card varchar( 20 ) NULL, Card_Number varchar( 500 ) NULL, Card_Expiry varchar( 10 ) NULL, Card_Code varchar( 10 ) Null, Sales_Account char( 50 ) Null, AR_Account char( 50 ) Null, AP_Account char( 50 ) Null, Asset_Account char( 50 ) Null, COGS_Account char( 50 ) Null, Expense_Account char( 50 ) Null, Payment_Account varchar( 50 ) Null, Payment1_Account varchar( 50 ) Null, Payment2_Account varchar( 50 ) Null, Payment3_Account varchar( 50 ) Null, Payment4_Account varchar( 50 ) Null, Payment5_Account varchar( 50 ) Null, Payment6_Account varchar( 50 ) Null, Payment7_Account varchar( 50 ) Null, Payment8_Account varchar( 50 ) Null, Payment9_Account varchar( 50 ) Null, Price_Level char(1) default ('0') check ( Price_Level IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) ), [Tax_Profile] char(50) default ('GST/QC') , [Currency] char(5) NULL, Grand_Total decimal( 18, 4 ) default (0), [Total] decimal(18, 4) default (0), [Taxable_Amount] decimal(18, 4) default (0), [Taxes] decimal(18, 4) default (0), [Taxes1] decimal(18, 4) default (0), [Taxes2] decimal(18, 4) default (0), Payment decimal(18, 4) default (0), Payment1 decimal(18, 4) default (0), Payment2 decimal(18, 4) default (0), Payment3 decimal(18, 4) default (0), Payment4 decimal(18, 4) default (0), Payment5 decimal(18, 4) default (0), Payment6 decimal(18, 4) default (0), Payment7 decimal(18, 4) default (0), Payment8 decimal(18, 4) default (0), Payment9 decimal(18, 4) default (0), Paid decimal(18, 4) default (0), [Change] decimal(18, 4) default (0), Deposit decimal( 18, 4 ) default( 0 ), Locked_Deposits decimal( 18, 4 ) default( 0 ), [Balance] decimal(18, 4) default (0), [Change_Notification_List] varchar(100) NULL , Notification_Condition varchar( 500 ) NULL, [Late_Notification_List] varchar(100) NULL , [Notify_when_Late_by_Days] decimal(15, 4) NULL default(1.5), [Late_Reminder_every_Hours] decimal(15, 4) NULL default(4), [Follow-Up_Notification_List] varchar(100) NULL, [Follow-Up_every_Days] decimal(15, 4) NULL default(5), [Late_Follow-Up_Reminder_every_Hours] decimal(15, 4) NULL default(24), [Date_Last_Follow-Up_Done] datetime NULL, [Note] Text NULL , Has_Back_Orders [bit] default (0), Auto_Generated [bit] default (0), [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Orders_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Orders_Contacts] FOREIGN KEY ( [Contact_ID] ) REFERENCES [Contacts] ( [ID] ), CONSTRAINT [FK_Orders_Contacts_Employee_ID] FOREIGN KEY ( [Employee_ID] ) REFERENCES [Contacts] ( [ID] ), constraint [FK_Orders_Sales] foreign key ( Sales_Account_ID ) references Accounts ( [ID] ), constraint [FK_Orders_AR] foreign key ( AR_Account_ID ) references Accounts ( [ID] ), constraint [FK_Orders_AP] foreign key ( AP_Account_ID ) references Accounts ( [ID] ), constraint [FK_Orders_Asset] foreign key ( Asset_Account_ID ) references Accounts ( [ID] ), constraint [FK_Orders_COGS] foreign key ( COGS_Account_ID ) references Accounts ( [ID] ), constraint [FK_Orders_Expense] foreign key ( Expense_Account_ID ) references Accounts ( [ID] ) ) GO deny update ( Date_Created, Date_Locked, Date_Printed, Total, Taxable_Amount, Taxes, Taxes1, Taxes2, Balance ) on Orders to VDM GO ------------- -- Numbers -- ------------- if NOT exists ( select * from sysobjects where [Name] = 'Numbers' AND [Type] = 'U' ) CREATE TABLE dbo.Numbers ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) check ( [Type] IN ( 'Sale', 'Purchase', 'Adjustment', 'Production', 'Estimate', 'Service' ) ), Last_Number char( 20 ) NULL, [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Numbers_GUID unique nonclustered ( GUID ), constraint IX_Numbers_Type unique nonclustered ( [Type] ) ) GO deny update ( Date_Created, [Type] ) on Numbers to VDM GO if ( select Count( [Type] ) from Numbers ) = 0 begin insert Numbers ([Type]) values('Sale') insert Numbers ([Type]) values('Purchase') insert Numbers ([Type]) values('Adjustment') insert Numbers ([Type]) values('Production') insert Numbers ([Type]) values('Estimate') insert Numbers ([Type]) values('Service') update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Sale' AND Date_Locked is NOT Null order by Number desc ), 1000 ) where [Type] = 'Sale' update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Purchase' order by Number desc ), 1000 ) where [Type] = 'Purchase' update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Adjustment' order by Number desc ), 1000 ) where [Type] = 'Adjustment' update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Production' order by Number desc ), 1000 ) where [Type] = 'Production' update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Estimate' order by Number desc ), 1000 ) where [Type] = 'Estimate' update Numbers set Last_Number = isNull( ( select top 1 Number from Orders where [Type] = 'Service' AND Date_Locked is NOT Null order by Number desc ), 1000 ) where [Type] = 'Service' end GO ---------------- -- OrderItems -- ---------------- if NOT exists ( select * from sysobjects where [Name] = 'OrderItems' AND [Type] = 'U' ) CREATE TABLE dbo.OrderItems ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [Order_ID] int NULL , [Contact_ID] int NULL , Mfg_Contact_ID int NULL , [Employee_ID] int NULL , [Resource_ID] int NULL , [Template_ID] int NULL , [Serial_ID] int NULL , [OrderItem_ID] int NULL , [Component_ID] int NULL , Sales_Account_ID int Null, Asset_Account_ID int Null, COGS_Account_ID int Null, Expense_Account_ID int Null, Version int default( 0 ), [Date_Created] datetime default GetDate(), [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , [Date_Voided] datetime NULL , [Date_Required] datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) check ( [Type] IN ( 'Sale', 'Purchase', 'Adjustment', 'Production', 'Manufacturing', 'Estimate', 'Service' ) ), [Order_Name] char(50) NULL , [Sequence] float NULL , [Resource_Name] char(50) NULL , [Resource_Code] char(20) NULL , [Resource_UPC] char(20) NULL , [Reference_Code] char(20) NULL , Serial_or_Lot_Number char(50) NULL , [Template_Name] char(50) NULL , [Contact_Name] char(50) NULL , [Date_Start] datetime default GetDate(), [Date_Stop] datetime NULL, Unit char( 20 ) default( 'Each' ), [Quantity] decimal(18, 4) default (0), [Back_Order] decimal(18, 4) default (0), [Hours] decimal(18, 4) default (0), [Price] decimal(18, 4) default (0), [Landed_Price] decimal(18, 4) default (0), [Taxable] [bit] default (1), [Total] decimal(18, 4) default (0), [Balance] decimal(18, 4) default (0), [Avg_Cost] decimal(18, 4) default (0), [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , Default_Index int default( 0 ), Sales_Account char( 50 ) Null, Asset_Account char( 50 ) Null, COGS_Account char( 50 ) Null, Expense_Account char( 50 ) Null, Tree_Path varchar( 500 ) NULL, [Note] Text NULL , [Audio] image NULL, [Video] image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_OrderItems_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_OrderItems_Orders] FOREIGN KEY ( [Order_ID] ) REFERENCES [Orders] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_OrderItems_Resources] FOREIGN KEY ( [Resource_ID] ) REFERENCES [Resources] ( [ID] ), CONSTRAINT [FK_OrderItems_Contacts] FOREIGN KEY ( [Contact_ID] ) REFERENCES [Contacts] ( [ID] ), CONSTRAINT [FK_OrderItems_Contacts_Employee] FOREIGN KEY ( [Employee_ID] ) REFERENCES [Contacts] ( [ID] ), constraint [FK_OrderItems_Sales] foreign key ( Sales_Account_ID ) references Accounts ( [ID] ), constraint [FK_OrderItems_Asset] foreign key ( Asset_Account_ID ) references Accounts ( [ID] ), constraint [FK_OrderItems_COGS] foreign key ( COGS_Account_ID ) references Accounts ( [ID] ), constraint [FK_OrderItems_Expense] foreign key ( Expense_Account_ID ) references Accounts ( [ID] ) ) GO deny update ( Order_ID, Date_Created, Date_Locked, [Variations_List], [Sub-Variations_List], Total, Balance, Avg_Cost ) on OrderItems to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'OrderItems' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_OrderItems' ) begin create unique index IX_OrderItems on OrderItems( Resource_ID, Date_Locked, [Type], ID ) -- include( Quantity, Balance ) end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'OrderItems' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_OrderItems_Sequence' ) begin create index IX_OrderItems_Sequence on OrderItems( Order_ID, OrderItem_ID, Sequence ) end end GO ----------- -- Serials -- ----------- if NOT exists ( select * from sysobjects where [Name] = 'Serials' AND [Type] = 'U' ) CREATE TABLE dbo.Serials ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [Resource_ID] int NULL , [Contact_ID] int NULL , [OrderItem_ID] int NULL , [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) check ( [Type] IN ( 'Sale', 'Purchase', 'Adjustment', 'Production', 'Manufacturing', 'Estimate' ) ), [Order_Name] char(50) NULL , [Number] char(50) NOT NULL , [Quantity] decimal(18, 4) default (0), [Balance] decimal(18, 4) default (0), [Note] Text NULL , [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Serials] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Serials_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Serials_OrderItems] FOREIGN KEY ( [OrderItem_ID] ) REFERENCES [OrderItems] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_Serials_Resources] FOREIGN KEY ( [Resource_ID] ) REFERENCES [Resources] ( [ID] ), CONSTRAINT [FK_Serials_Contacts] FOREIGN KEY ( [Contact_ID] ) REFERENCES [Contacts] ( [ID] ) ) GO deny update ( Resource_ID, Date_Created ) on Serials to VDM GO ---------------- -- Activities -- ---------------- if NOT exists ( select * from sysobjects where [Name] = 'Activities' AND [Type] = 'U' ) CREATE TABLE dbo.Activities ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [OrderItem_ID] int NULL , [Contact_ID] int NULL , [Resource_ID] int NULL , Serial_ID int NULL , [Catalog_ID] int NULL , [Activity_ID] int NULL , Activities_Count int NULL, Version int default( 0 ), [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Type] char( 20 ) default 'Activity' check ( [Type] IN ( 'Activity', 'Project', 'Schedule', 'Log', 'Template' ) ), [Sequence] float NULL , [Name] char(50) NOT NULL , Template_Name char( 50 ) NULL , [Contact_Name] char(50) NULL , [Resource_Name] char(50) NULL , Serial_or_Lot_Number char(50) NULL , [Catalog_Name] char(50) NULL , [Minutes] decimal(18, 4) default (0), [Hours] decimal(18, 4) default (0), [Days] decimal(18, 4) default (0), [Date_Start] datetime default GetDate(), [Date_Stop] datetime NULL, [Change_Notification_List] varchar(100) NULL , Notification_Condition varchar( 500 ) NULL, [Late_Notification_List] varchar(100) NULL , [Notify_when_Late_by_Days] decimal(15, 4) NULL default(1.5), [Late_Reminder_every_Hours] decimal(15, 4) NULL default(4), [Follow-Up_Notification_List] varchar(100) NULL, [Follow-Up_every_Days] decimal(15, 4) NULL default(5), [Late_Follow-Up_Reminder_every_Hours] decimal(15, 4) NULL default(24), [Date_Last_Follow-Up_Done] datetime NULL, Tree_Path varchar( 500 ) NULL, [Note] Text NULL , [Audio] image NULL, [Video] image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, CONSTRAINT [PK_Activities] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Activities_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Activities_OrderItems] FOREIGN KEY ( [OrderItem_ID] ) REFERENCES [OrderItems] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_Activities_Contacts] FOREIGN KEY ( [Contact_ID] ) REFERENCES [Contacts] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_Activities_Resources] FOREIGN KEY ( [Resource_ID] ) REFERENCES [Resources] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_Activities_Catalog] FOREIGN KEY ( [Catalog_ID] ) REFERENCES [Catalog] ( [ID] ) ON DELETE CASCADE ) GO deny update ( OrderItem_ID, Date_Created, Date_Locked ) on Activities to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Activities' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Activities_Sequence' ) begin create index IX_Activities_Sequence on Activities( OrderItem_ID, Contact_ID, Resource_ID, Catalog_ID, Sequence ) end end GO ---------------- -- Variations -- ---------------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Variations' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Variations' AND Column_Name = 'Warehouse_ID' ) begin alter table Variations add Warehouse_ID int NULL -- alter table Variations add CONSTRAINT FK_Variations_Warehouses FOREIGN KEY ( Warehouse_ID ) REFERENCES Warehouses( ID ) CYCLIC ERROR end end GO if NOT exists ( select * from sysobjects where [Name] = 'Variations' AND [Type] = 'U' ) CREATE TABLE dbo.Variations ( [ID] int IDENTITY (1, 1) NOT NULL , GUID uniqueidentifier Null default( NewID() ) rowguidcol, [Resource_ID] int NULL , Warehouse_ID int NULL , [OrderItem_ID] int NULL , [Relationship_ID] int NULL , [Component_ID] int NULL , [Spec_ID] int NULL , [Date_Created] datetime default GetDate() , [Date_Modified] datetime NULL , [Date_Locked] datetime NULL , ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Name] char(50) NULL , [Variations_List] [varchar] (500) NULL , [Sub-Variations_List] [varchar] (500) NULL , Base_Value char(50) NULL , [Name_00] char(50) NULL , [Name_01] char(50) NULL , [Name_02] char(50) NULL , [Name_03] char(50) NULL , [Name_04] char(50) NULL , [Name_05] char(50) NULL , [Name_06] char(50) NULL , [Name_07] char(50) NULL , [Name_08] char(50) NULL , [Name_09] char(50) NULL , [Name_10] char(50) NULL , [Name_11] char(50) NULL , [Name_12] char(50) NULL , [Name_13] char(50) NULL , [Name_14] char(50) NULL , [Name_15] char(50) NULL , [Name_16] char(50) NULL , [Name_17] char(50) NULL , [Name_18] char(50) NULL , [Name_19] char(50) NULL , [Name_20] char(50) NULL , [Name_21] char(50) NULL , [Name_22] char(50) NULL , [Name_23] char(50) NULL , [Name_24] char(50) NULL , [Name_25] char(50) NULL , [Name_26] char(50) NULL , [Name_27] char(50) NULL , [Name_28] char(50) NULL , [Name_29] char(50) NULL , [Value_00] char(50) NULL , [Value_01] char(50) NULL , [Value_02] char(50) NULL , [Value_03] char(50) NULL , [Value_04] char(50) NULL , [Value_05] char(50) NULL , [Value_06] char(50) NULL , [Value_07] char(50) NULL , [Value_08] char(50) NULL , [Value_09] char(50) NULL , [Value_10] char(50) NULL , [Value_11] char(50) NULL , [Value_12] char(50) NULL , [Value_13] char(50) NULL , [Value_14] char(50) NULL , [Value_15] char(50) NULL , [Value_16] char(50) NULL , [Value_17] char(50) NULL , [Value_18] char(50) NULL , [Value_19] char(50) NULL , [Value_20] char(50) NULL , [Value_21] char(50) NULL , [Value_22] char(50) NULL , [Value_23] char(50) NULL , [Value_24] char(50) NULL , [Value_25] char(50) NULL , [Value_26] char(50) NULL , [Value_27] char(50) NULL , [Value_28] char(50) NULL , [Value_29] char(50) NULL , [Quantity_00] decimal(18, 4) default (0), [Quantity_01] decimal(18, 4) default (0), [Quantity_02] decimal(18, 4) default (0), [Quantity_03] decimal(18, 4) default (0), [Quantity_04] decimal(18, 4) default (0), [Quantity_05] decimal(18, 4) default (0), [Quantity_06] decimal(18, 4) default (0), [Quantity_07] decimal(18, 4) default (0), [Quantity_08] decimal(18, 4) default (0), [Quantity_09] decimal(18, 4) default (0), [Quantity_10] decimal(18, 4) default (0), [Quantity_11] decimal(18, 4) default (0), [Quantity_12] decimal(18, 4) default (0), [Quantity_13] decimal(18, 4) default (0), [Quantity_14] decimal(18, 4) default (0), [Quantity_15] decimal(18, 4) default (0), [Quantity_16] decimal(18, 4) default (0), [Quantity_17] decimal(18, 4) default (0), [Quantity_18] decimal(18, 4) default (0), [Quantity_19] decimal(18, 4) default (0), [Quantity_20] decimal(18, 4) default (0), [Quantity_21] decimal(18, 4) default (0), [Quantity_22] decimal(18, 4) default (0), [Quantity_23] decimal(18, 4) default (0), [Quantity_24] decimal(18, 4) default (0), [Quantity_25] decimal(18, 4) default (0), [Quantity_26] decimal(18, 4) default (0), [Quantity_27] decimal(18, 4) default (0), [Quantity_28] decimal(18, 4) default (0), [Quantity_29] decimal(18, 4) default (0), [Price_00] decimal(18, 4) default (0), [Price_01] decimal(18, 4) default (0), [Price_02] decimal(18, 4) default (0), [Price_03] decimal(18, 4) default (0), [Price_04] decimal(18, 4) default (0), [Price_05] decimal(18, 4) default (0), [Price_06] decimal(18, 4) default (0), [Price_07] decimal(18, 4) default (0), [Price_08] decimal(18, 4) default (0), [Price_09] decimal(18, 4) default (0), [Price_10] decimal(18, 4) default (0), [Price_11] decimal(18, 4) default (0), [Price_12] decimal(18, 4) default (0), [Price_13] decimal(18, 4) default (0), [Price_14] decimal(18, 4) default (0), [Price_15] decimal(18, 4) default (0), [Price_16] decimal(18, 4) default (0), [Price_17] decimal(18, 4) default (0), [Price_18] decimal(18, 4) default (0), [Price_19] decimal(18, 4) default (0), [Price_20] decimal(18, 4) default (0), [Price_21] decimal(18, 4) default (0), [Price_22] decimal(18, 4) default (0), [Price_23] decimal(18, 4) default (0), [Price_24] decimal(18, 4) default (0), [Price_25] decimal(18, 4) default (0), [Price_26] decimal(18, 4) default (0), [Price_27] decimal(18, 4) default (0), [Price_28] decimal(18, 4) default (0), [Price_29] decimal(18, 4) default (0), [Audio] image NULL, [Video] image NULL, CONSTRAINT [PK_Variations] PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Variations_GUID unique nonclustered ( GUID ), CONSTRAINT [FK_Variations_Resources] FOREIGN KEY ( [Resource_ID] ) REFERENCES [Resources] ( [ID] ) ON DELETE CASCADE, -- CONSTRAINT [FK_Variations_Warehouses] FOREIGN KEY ( [Warehouse_ID] ) REFERENCES [Warehouses] ( [ID] ) ON DELETE CASCADE, CYCLIC ERROR CONSTRAINT [FK_Variations_Relationships] FOREIGN KEY ( [Relationship_ID] ) REFERENCES [Relationships] ( [ID] ) ON DELETE CASCADE, CONSTRAINT [FK_Variations_OrderItems] FOREIGN KEY ( [OrderItem_ID] ) REFERENCES [OrderItems] ( [ID] ) ON DELETE CASCADE ) GO deny update ( Date_Created, Date_Locked ) on Variations to VDM GO ----------- -- Notes -- ----------- if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Notes' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Notes' AND Column_Name = 'Type' ) begin alter table Notes add [Type] char( 20 ) Null end end GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Notes' ) begin if NOT exists ( select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = 'Notes' AND Column_Name = 'Date_Uploaded' ) begin alter table Notes add Date_Uploaded datetime Null, Date_of_Content datetime Null end end GO if NOT exists ( select * from sysobjects where [Name] = 'Notes' AND [Type] = 'U' ) create table dbo.Notes ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, WebSite_ID int Null, Contact_ID int Null, Catalog_ID int Null, Resource_ID int Null, Order_ID int Null, Activity_ID int Null, Account_ID int Null, Ledger_ID int Null, Version int default( 0 ), Date_Created datetime default GetDate(), Date_Modified datetime Null, Date_Uploaded datetime Null, Date_of_Content datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), [Sequence] float NULL, [Type] char( 20 ) Null, Note text NULL, Audio image NULL, Video image NULL, Photo1 image NULL, Photo1Thumb image NULL, Photo1Name Text NULL, Photo2 image NULL, Photo2Thumb image NULL, Photo2Name Text NULL, Photo3 image NULL, Photo3Thumb image NULL, Photo3Name Text NULL, Photo4 image NULL, Photo4Thumb image NULL, Photo4Name Text NULL, Photo5 image NULL, Photo5Thumb image NULL, Photo5Name Text NULL, Photo6 image NULL, Photo6Thumb image NULL, Photo6Name Text NULL, Photo7 image NULL, Photo7Thumb image NULL, Photo7Name Text NULL, Photo8 image NULL, Photo8Thumb image NULL, Photo8Name Text NULL, Photo9 image NULL, Photo9Thumb image NULL, Photo9Name Text NULL, constraint PK_Notes PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Notes_GUID unique nonclustered ( GUID ), constraint FK_Notes_WebSites foreign key ( WebSite_ID ) references WebSites ( [ID] ) on delete cascade, constraint FK_Notes_Contacts foreign key ( Contact_ID ) references Contacts ( [ID] ) on delete cascade, constraint FK_Notes_Catalog foreign key ( Catalog_ID ) references [Catalog] ( [ID] ) on delete cascade, constraint FK_Notes_Resources foreign key ( Resource_ID ) references Resources ( [ID] ) on delete cascade, constraint FK_Notes_Orders foreign key ( Order_ID ) references Orders ( [ID] ) on delete cascade, constraint FK_Notes_Accounts foreign key ( Account_ID ) references Accounts ( [ID] ) on delete cascade, constraint FK_Notes_Ledger foreign key ( Ledger_ID ) references Ledger ( [ID] ), constraint FK_Notes_Activities foreign key ( Activity_ID ) references Activities ( [ID] ) ) GO deny update ( Date_Created ) on Notes to VDM GO if exists ( select * from INFORMATION_SCHEMA.TABLES where Table_Name = 'Notes' ) begin if NOT exists ( select * from sysIndexes where [Name] = 'IX_Notes_Sequence' ) begin create index IX_Notes_Sequence on Notes( WebSite_ID, Contact_ID, Catalog_ID, Resource_ID, Order_ID, Account_ID, Ledger_ID, Activity_ID, Sequence ) end end GO -------------- -- DataLog -- -------------- if NOT exists ( select * from sysobjects where [Name] = 'DataLog' AND [Type] = 'U' ) create table dbo.DataLog ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, Version int default( 0 ), Date_Created datetime default GetDate(), Date_Mirrored datetime Null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), Last_ID_Scanned int Null, Table_Name varchar( 100 ) Null, Form_Name varchar( 100 ) Null, CmdText text Null, SQL_Command char( 10 ) Null, GUID_Key uniqueidentifier Null, Identity_Key int Null, Parent_Table varchar( 100 ) Null, Parent_GUID uniqueidentifier Null, Unique_Key text Null, Unique_Column varchar( 100 ) Null, Table_Column varchar( 100 ) Null, Old_Value text Null, New_Value text Null, Mirrored_Servers_List text Null, BLOB image NULL constraint PK_DataLog PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_DataLog_GUID unique nonclustered ( GUID ) ) GO ------------------- -- Notifications -- ------------------- if NOT exists ( select * from sysobjects where [Name] = 'Notifications' AND [Type] = 'U' ) create table dbo.Notifications ( [ID] int identity( 1, 1 ) NOT Null, GUID uniqueidentifier Null default( NewID() ) rowguidcol, Date_Created datetime default GetDate(), Date_Sent datetime null, ServerName varchar( 100 ) Null default( @@ServerName ), WorkStation varchar( 100 ) Null default( App_Name() ), UserName varchar( 100 ) Null default( System_User ), Table_Name char( 100 ) NOT Null, Table_ID int NOT Null, Notification_List varchar( 1000 ) NOT Null, Subject varchar( 100 ) NOT Null, Body varchar( 4000 ) NOT Null constraint PK_Notifications PRIMARY KEY CLUSTERED ( [ID] ), constraint IX_Notifications_GUID unique nonclustered ( GUID ) ) GO --------------------- -- eStore Products -- --------------------- if NOT exists ( select * from sysobjects where [Name] = 'product' AND [Type] = 'U' ) CREATE TABLE dbo.Product ( [product_id] varchar(30) NOT NULL , [product_name] varchar(60) NULL , [product_number] varchar(30) NULL , [product_pricestatus] varchar(4) NULL , [product_regprice] varchar(10) NULL , [product_saleprice] varchar(10) NULL , [product_volprice] varchar(200) NULL , [product_voltext] text NULL , [product_recurprice] varchar(10) NULL , [product_recurstatus] varchar(4) NULL , [product_keywords] varchar(100) NULL , [product_usecatdisc] varchar(4) NULL , [product_taxstateprov] varchar(4) NULL , [product_taxcountry] varchar(4) NULL , [product_useinv] varchar(4) NULL , [product_inv] varchar(10) NULL , [product_usemax] varchar(4) NULL , [product_max] varchar(10) NULL , [product_imgxlguse] varchar(4) NULL , [product_imgxlg] varchar(30) NULL , [product_imglg] varchar(30) NULL , [product_imgsm] varchar(30) NULL , [product_lgdisp] varchar(30) NULL , [product_addemtext] text NULL , [product_splashdisp] varchar(4) NULL , [product_new] varchar(4) NULL , [product_special] varchar(4) NULL , [product_bestsell] varchar(4) NULL , [product_descshort] text NULL , [product_desclong] text NULL , [product_delmethod] varchar(40) NULL , [product_dlfile] varchar(30) NULL , [product_shiplocal] varchar(40) NULL , [product_shipint] varchar(40) NULL , [product_shipweight] varchar(10) NULL , [product_shiplength] varchar(10) NULL , [product_shipwidth] varchar(10) NULL , [product_shipheight] varchar(10) NULL , [product_shipnumbox] varchar(4) NULL , [product_customship] varchar(30) NULL , [product_shiponename] varchar(40) NULL , [product_shiponeprice] varchar(10) NULL , [product_shiptwoname] varchar(40) NULL , [product_shiptwoprice] varchar(10) NULL , [product_shipthreename] varchar(40) NULL , [product_shipthreeprice] varchar(10) NULL , [product_shipemail] varchar(40) NULL , [product_shipstateprov] varchar(50) NULL , [product_shipcountry] varchar(50) NULL , [product_shipzip] varchar(10) NULL , [product_xprod] text NULL , [product_xopt] text NULL , [product_xcat] text NULL ) GO ----------------------- -- eStore Categories -- ----------------------- if NOT exists ( select * from sysobjects where [Name] = 'category' AND [Type] = 'U' ) CREATE TABLE dbo.category ( [category_id] varchar(30) NOT NULL , [category_name] varchar(150) NULL , [category_splashdisp] varchar(4) NULL , [category_imgsm] varchar(30) NULL , [category_catcount] varchar(4) NULL , [category_catdisp] varchar(30) NULL , [category_smcount] varchar(4) NULL , [category_smdisp] varchar(30) NULL , [category_active] varchar(10) NULL , [category_currpct] varchar(4) NULL , [category_value] varchar(10) NULL , [category_descsale] text NULL , [category_desc] text NULL , [category_xcat] text NULL ) GO ---------------------------- -- eStore Product Options -- ---------------------------- if NOT exists ( select * from sysobjects where [Name] = 'optionprod' AND [Type] = 'U' ) CREATE TABLE dbo.optionprod ( [optionprod_id] varchar(30) NOT NULL , [optionprod_name] varchar(60) NULL , [optionprod_descript] text NULL , [optionprod_display_type] varchar(20) NULL , [optionprod_inv] varchar(4) NULL , [optionprod_required] varchar(4) NULL , [optionprod_display_value] text NULL ) GO 10/6/2007 12:07:36 PM v1.3445 - Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction Continue or restart VDM - This error was queued for analysis, we apologize. declare @I int, @ID int, @GUID varchar( 100 ), @S varchar( 100 ) if NOT exists( select ID from Reso 0001:0021AFB8 axView.txView.RunSQL line 6184 0001:0018F4B0 axPopup.txPopup.DoOnClick line 1028 base: 00B51000 declare @I int, @ID int, @GUID varchar( 100 ), @S varchar( 100 ) if NOT exists( select ID from Resources where [Name] like 'VDM_Test_Resource_%' ) begin set @I = 0 while @I < 10 begin set @S = 'VDM_Test_Contact_' + Cast( @I as varchar( 10 ) ) insert Contacts ( [Name] ) values( @S ) set @S = 'VDM_Test_Resource_' + Cast( @I as varchar( 10 ) ) insert Resources ( [Name], Price ) values( @S, @I + 1 + @I / 10 ) set @I = @I + 1 end end set @I = 0 while @I < 100 begin set @S = 'VDM_Test_Contact_' + Cast( @I % 10 as varchar( 10 ) ) insert Orders ( [Name], [Type] ) values( @S, 'Sale' ) set @ID = Scope_Identity() set @S = 'VDM_Test_Resource_' + Cast( @I % 10 as varchar( 10 ) ) insert OrderItems ( Order_ID, Resource_Name, Quantity, Price ) values( @ID, @S, @I + 1, @I + 1 + @I / 10 ) select @GUID = isNull( GUID, '' ) from Orders where ID = @ID AND Date_Locked is Null set @S = 'VDM_Test_' + Cast( @ID as varchar( 100 ) ) execute dbo.LockOrder @GUID, 'getdate()', @S set @I = @I + 1 end 10/6/2007 12:07:24 PM v1.3445 - Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction Continue or restart VDM - This error was queued for analysis, we apologize. declare @I int, @ID int, @GUID varchar( 100 ), @S varchar( 100 ) if NOT exists( select ID from Reso 0001:0021AFB8 axView.txView.RunSQL line 6184 0001:0018F4B0 axPopup.txPopup.DoOnClick line 1028 base: 00A61000 declare @I int, @ID int, @GUID varchar( 100 ), @S varchar( 100 ) if NOT exists( select ID from Resources where [Name] like 'VDM_Test_Resource_%' ) begin set @I = 0 while @I < 10 begin set @S = 'VDM_Test_Contact_' + Cast( @I as varchar( 10 ) ) insert Contacts ( [Name] ) values( @S ) set @S = 'VDM_Test_Resource_' + Cast( @I as varchar( 10 ) ) insert Resources ( [Name], Price ) values( @S, @I + 1 + @I / 10 ) set @I = @I + 1 end end set @I = 0 while @I < 100 begin set @S = 'VDM_Test_Contact_' + Cast( @I % 10 as varchar( 10 ) ) insert Orders ( [Name], [Type] ) values( @S, 'Sale' ) set @ID = Scope_Identity() set @S = 'VDM_Test_Resource_' + Cast( @I % 10 as varchar( 10 ) ) insert OrderItems ( Order_ID, Resource_Name, Quantity, Price ) values( @ID, @S, @I + 1, @I + 1 + @I / 10 ) select @GUID = isNull( GUID, '' ) from Orders where ID = @ID AND Date_Locked is Null set @S = 'VDM_Test_' + Cast( @ID as varchar( 100 ) ) execute dbo.LockOrder @GUID, 'getdate()', @S set @I = @I + 1 end 10/5/2007 2:01:04 PM v1.3425 - Invalid column name 'Date_Start' Continue or restart VDM - This error was queued for analysis, we apologize. update Contacts set [Date_Start]='10/5/2007 4:36:18 PM' where [ID]=218 0001:0001E6C8 ComObj.DispCallError line 1795 0001:002418F8 axVDM.Finalization line 54 base: 00A61000 update Contacts set [Date_Start]='10/5/2007 4:36:18 PM' where [ID]=218 9/27/2007 9:40:44 AM v1.3395 - ZIP (11036) This archive is not a valid Zip archive Continue or restart VDM - This error was queued for analysis, we apologize. 0001:0022367C axView.txView.DoOnZipMessage line 6988 0001:00240A40 axVDM.Finalization line 54 base: 00AB1000 9/27/2007 6:51:49 AM v1.3395 - Invalid column name 'Date_Expiry' Continue or restart VDM - This error was queued for analysis, we apologize. select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) 0001:0022E370 axView.txView.ExecuteRS line 7967 0001:0023CA9C axView.txTabsEx.DoOnClick line 9524 base: 00AB1000 select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) ) as ID_Number,[Phone],[Name],[Main_Contact_Name],[Reference],Cast( Note as VarChar( 1000 ) ) as [Note],'$' + rTrim( Cast( Total as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Grand_Total as Decimal(18,2) ) ) as Total_Amts,[Grand_Total],[Total],[Taxable_Amount],[Tax_Profile],'$' + rTrim( Cast( Taxes1 as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Taxes2 as Decimal(18,2) ) ) as Tax_Amounts,[Taxes],[Taxes1],[Taxes2],[Locked_Deposits],[Change],[Balance],[Payment],[Payment1],[Payment2],[Payment3],[Payment4],[Payment5],[Payment6],[ID],[GUID],[Number],[Date_Created],[Date_Modified],[Date_Printed],[Date_Locked],[Date_Voided],rTrim( isnull( Date_Created, '' ) ) + ' - ' + rTrim( isNull( Date_Modified, '' ) ) + ' by ' + rTrim( UserName ) + ' on ' + rTrim( WorkStation ) as Created_Modified,rTrim( isNull( Date_Printed, '' ) ) + ' - ' + rTrim( isNull( Date_Locked, '' ) ) + ' - ' + rTrim( isNull( Date_Voided, '' ) ) as Printed_Locked,[Date_Required],[Type],[Group],[Repeat_Frequency],[Employee],[Referred_by],[Price_Level],[Currency],[Address],[City],[State_or_Province],[Postal_Code],[Country],[Fax],[eMail],[Credit_Card],[Card_Number],[Date_Expiry],[Card_Code],[Ship_Name],[Ship_Address],[Ship_City],[Ship_State_or_Province],[Ship_Postal_Code],[Ship_Country],[Ship_Fax],[Ship_eMail],[AR_Account],[Sales_Account],[Asset_Account],[COGS_Account],[Payment_Account],[Payment1_Account],[Payment2_Account],[Payment3_Account],[Payment4_Account],[Payment5_Account],[Payment6_Account],[Change_Notification_List],[Notification_Condition],[Late_Notification_List],[Notify_when_Late_by_Days],[Late_Reminder_every_Hours],[Follow-Up_Notification_List],[Follow-Up_every_Days],[Late_Follow-Up_Reminder_every_Hours],[Date_Last_Follow-Up_Done] from Orders where [ID]=3755 9/27/2007 6:51:43 AM v1.3395 - Invalid column name 'Date_Expiry' Continue or restart VDM - This error was queued for analysis, we apologize. select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) 0001:0022E370 axView.txView.ExecuteRS line 7967 0001:0023CA9C axView.txTabsEx.DoOnClick line 9524 base: 00AB1000 select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) ) as ID_Number,[Phone],[Name],[Main_Contact_Name],[Reference],Cast( Note as VarChar( 1000 ) ) as [Note],'$' + rTrim( Cast( Total as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Grand_Total as Decimal(18,2) ) ) as Total_Amts,[Grand_Total],[Total],[Taxable_Amount],[Tax_Profile],'$' + rTrim( Cast( Taxes1 as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Taxes2 as Decimal(18,2) ) ) as Tax_Amounts,[Taxes],[Taxes1],[Taxes2],[Locked_Deposits],[Change],[Balance],[Payment],[Payment1],[Payment2],[Payment3],[Payment4],[Payment5],[Payment6],[ID],[GUID],[Number],[Date_Created],[Date_Modified],[Date_Printed],[Date_Locked],[Date_Voided],rTrim( isnull( Date_Created, '' ) ) + ' - ' + rTrim( isNull( Date_Modified, '' ) ) + ' by ' + rTrim( UserName ) + ' on ' + rTrim( WorkStation ) as Created_Modified,rTrim( isNull( Date_Printed, '' ) ) + ' - ' + rTrim( isNull( Date_Locked, '' ) ) + ' - ' + rTrim( isNull( Date_Voided, '' ) ) as Printed_Locked,[Date_Required],[Type],[Group],[Repeat_Frequency],[Employee],[Referred_by],[Price_Level],[Currency],[Address],[City],[State_or_Province],[Postal_Code],[Country],[Fax],[eMail],[Credit_Card],[Card_Number],[Date_Expiry],[Card_Code],[Ship_Name],[Ship_Address],[Ship_City],[Ship_State_or_Province],[Ship_Postal_Code],[Ship_Country],[Ship_Fax],[Ship_eMail],[AR_Account],[Sales_Account],[Asset_Account],[COGS_Account],[Payment_Account],[Payment1_Account],[Payment2_Account],[Payment3_Account],[Payment4_Account],[Payment5_Account],[Payment6_Account],[Change_Notification_List],[Notification_Condition],[Late_Notification_List],[Notify_when_Late_by_Days],[Late_Reminder_every_Hours],[Follow-Up_Notification_List],[Follow-Up_every_Days],[Late_Follow-Up_Reminder_every_Hours],[Date_Last_Follow-Up_Done] from Orders where [ID]=3755 9/27/2007 6:51:39 AM v1.3395 - Invalid column name 'Date_Expiry' Continue or restart VDM - This error was queued for analysis, we apologize. select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) 0001:0022E370 axView.txView.ExecuteRS line 7967 0001:0023CA9C axView.txTabsEx.DoOnClick line 9524 base: 00AB1000 select rTrim( ID ) + ' - ' + rTrim( isNull( Deposit_ID, '' ) ) + ' - ' + rTrim( isNull( Number, '' ) ) as ID_Number,[Phone],[Name],[Main_Contact_Name],[Reference],Cast( Note as VarChar( 1000 ) ) as [Note],'$' + rTrim( Cast( Total as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Grand_Total as Decimal(18,2) ) ) as Total_Amts,[Grand_Total],[Total],[Taxable_Amount],[Tax_Profile],'$' + rTrim( Cast( Taxes1 as Decimal(18,2) ) ) + ', $' + rTrim( Cast( Taxes2 as Decimal(18,2) ) ) as Tax_Amounts,[Taxes],[Taxes1],[Taxes2],[Locked_Deposits],[Change],[Balance],[Payment],[Payment1],[Payment2],[Payment3],[Payment4],[Payment5],[Payment6],[ID],[GUID],[Number],[Date_Created],[Date_Modified],[Date_Printed],[Date_Locked],[Date_Voided],rTrim( isnull( Date_Created, '' ) ) + ' - ' + rTrim( isNull( Date_Modified, '' ) ) + ' by ' + rTrim( UserName ) + ' on ' + rTrim( WorkStation ) as Created_Modified,rTrim( isNull( Date_Printed, '' ) ) + ' - ' + rTrim( isNull( Date_Locked, '' ) ) + ' - ' + rTrim( isNull( Date_Voided, '' ) ) as Printed_Locked,[Date_Required],[Type],[Group],[Repeat_Frequency],[Employee],[Referred_by],[Price_Level],[Currency],[Address],[City],[State_or_Province],[Postal_Code],[Country],[Fax],[eMail],[Credit_Card],[Card_Number],[Date_Expiry],[Card_Code],[Ship_Name],[Ship_Address],[Ship_City],[Ship_State_or_Province],[Ship_Postal_Code],[Ship_Country],[Ship_Fax],[Ship_eMail