Configuration file for your SQL database server! 

Eine deutsche Version dieses Dokumentes finden Sie hier:
https://www.terminal-systems.de/wp/home-2/lagerverwaltung-aktuell/lagerverwaltung-sql-erweiterung/sql-datenmodell/




SQL querys for fast and easy configuration of SQL database and tables.
Each query is prepared for copy & paste. Means: mark complete command,
copy into clipboard, paste into SQL admin tool and execute command. 
This will create needed database and tables automatically!



-----------------------------
Step 1: Create SQL Database
-----------------------------

Use following SQL command:

CREATE DATABASE DBAEWWS


Make sure new database DBAEWWS is active datebase for your next commands!

-----------------------------
Step 2: Create article table
Valid from version 2.01.004
-----------------------------



Use following SQL command:

CREATE TABLE [dbo].[ARTLIST100] (
[Idx] int NOT NULL PRIMARY KEY, 
[ArtNr] nvarchar(50),
[ArtText] nvarchar(255),
[MaterialGroup] nvarchar(255),
[StockQty] decimal (15,3),
[MinStockQty] decimal (15,3),
[PR1] decimal (15,3), [SumPR1] decimal (15,3),
[PR2] decimal (15,3), [SumPR2] decimal (15,3),
[StorageLocation] nvarchar (255),
[ScaleUnit] nvarchar (50),
[Supplier] nvarchar (255),
[Note] nvarchar (max),
[TextF1] nvarchar (255), [TextF2] nvarchar (255), [TextF3] nvarchar (255),
[TextF4] nvarchar (255), [TextF5] nvarchar (255), [TextF6] nvarchar (255),
[DT1] datetime, [DT2] datetime, [DT3] datetime,
[NUM1] decimal (15,3), [NUM2] decimal (15,3), [NUM3] decimal (15,3),
[CheckF1] bit, [CheckF2] bit, [CheckF3] bit, 
[ListF1] nvarchar (255), [ListF2] nvarchar (255), [ListF3] nvarchar (255), 
[Picture1] nvarchar (255),
[Partlist] nvarchar (255),
[HomeDir] nvarchar (255),
[Color1] nvarchar (255),
[LockState] nvarchar (10),
[ItemCreateDate] datetime, 
[ItemCreateUser] nvarchar (50))


-----------------------------
Step 3: Create logfile table
LOG Table for AE WWS version 2.01.xxx and higher:
-----------------------------

Use following SQL command:



CREATE TABLE [dbo].[LOGLIST100] (
[DT] datetime,
[USR] nvarchar (50),
[Idx] int,
[ANO] nvarchar (50),
[ATX] nvarchar (255),
[ACN] nvarchar (255),
[TF1] nvarchar (255),
[TF2] nvarchar (255),
[SUP] nvarchar (255))


-----------------------------
Step 3: Create logfile table
LOG Table for AE WWS version 1.xx and 2.00
-----------------------------



CREATE TABLE [dbo].[LOGLIST100] (
[Datum] datetime,
[AppUser] nvarchar (50),
[Idx] int,
[Artikel] nvarchar (50),
[Hist_ArtText] nvarchar (255),
[Aktion] nvarchar (255),
[Vorgang] nvarchar (255),
[Id_Kst] nvarchar (255))


If you want to update an existing log table from AE WWS 1.xx / 2.00 to 2.01 or higher
you have to rename existing columns in LOG table. We are using a saved procedure for 
renaming a column:

sp_rename 'LOGLIST100.Datum', 'DT', 'COLUMN'; 
sp_rename 'LOGLIST100.AppUser', 'USR', 'COLUMN'; 
sp_rename 'LOGLIST100.Artikel', 'ANO', 'COLUMN'; 
sp_rename 'LOGLIST100.Hist_ArtText', 'ATX', 'COLUMN'; 
sp_rename 'LOGLIST100.Aktion', 'ACN', 'COLUMN'; 
sp_rename 'LOGLIST100.Vorgang', 'TF1', 'COLUMN'; 
sp_rename 'LOGLIST100.Id_Kst', 'TF2', 'COLUMN'; 

Note: press GO after each line!


-----------------------------
Step 4: Create SQL status table
-----------------------------

CREATE TABLE [dbo].[WWS100USR] (
[SoftwareUser] nvarchar (50),
[SoftwareUserPw] nvarchar (50),
[SoftwareRights] nvarchar (50),
[TimeStamp] datetime,
[SessionId] nvarchar (255))