Using Microsoft SQL Server (MSSQL) instead of SQLite
The training uses the SQLite database above by default, but you can follow the whole course using Microsoft SQL Server instead. The tables, column names and exercises are the same; only the database setup and connection differ.
Choose one of the two routes below.
Route 1: Add the tables to an existing database
Use this if you already have a database you want to use (for example, a personal dev database).
1. Download 01_Create_Tables.sql (attached below).
2. Open it in SQL Server Management Studio (SSMS) or Azure Data Studio.
3. Select your database (or uncomment the `USE` line at the top of the script and enter your database name).
4. Run the script.
This creates the `Clients`, `Logging` and `FileLog` tables in that database.
Route 2: Create a new TrainingDB database with the tables
Use this if you want a separate database just for the training.
1. Download 02_Create_Database_And_Tables.sql (attached below).
2. Open it in SSMS or Azure Data Studio, connected to your SQL Server instance.
3. Run the script.
This creates a database called `TrainingDB`, the `Clients`, `Logging` and `FileLog` tables inside it, and a login you can use to connect from Linx:
TrainingDB login details
-
Database: TrainingDB
-
Username: TrainingUser
-
Password: Password
This user has full rights on `TrainingDB` only. The password is for training use only; don’t reuse it on a real or shared server.
Before you start:
-
Run the script as a login with sysadmin rights, since it creates a database and a login.
-
To log in as `TrainingUser`, SQL Server must allow SQL logins. In SSMS, right-click the server → Properties → Security, select SQL Server and Windows Authentication mode, then restart the SQL Server service.
Both scripts can safely be run more than once. Anything that already exists is skipped, and no data is deleted.
/* =====================================================================
Linx Training - MSSQL table creation script
Route 1: You already have a database you want to use.
Creates the three tables used in the Linx training (Clients, Logging,
FileLog) in the CURRENT database. Select your database in SSMS (or
uncomment the USE line below) before running.
Safe to run more than once: a table is only created if it doesn't
already exist.
===================================================================== */
-- USE [YourDatabaseName];
-- GO
SET NOCOUNT ON;
-- Client records loaded from clients.csv and synced to the CRM API
IF OBJECT_ID(N'dbo.Clients', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Clients (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Clients PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Surname NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) NOT NULL,
Cellphone NVARCHAR(50) NULL,
DateAdded DATETIME NULL,
AddedBy NVARCHAR(100) NULL
);
PRINT 'Created table dbo.Clients';
END
ELSE
PRINT 'Table dbo.Clients already exists - skipped';
-- Process / action logging
IF OBJECT_ID(N'dbo.Logging', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Logging (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Logging PRIMARY KEY,
\[DateTime\] DATETIME NULL,
Action NVARCHAR(MAX) NOT NULL,
ActionUser NVARCHAR(100) NULL
);
PRINT 'Created table dbo.Logging';
END
ELSE
PRINT 'Table dbo.Logging already exists - skipped';
-- Audit trail of generated report files (Training Exercise)
IF OBJECT_ID(N'dbo.FileLog', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FileLog (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_FileLog PRIMARY KEY,
FileName NVARCHAR(260) NOT NULL,
DateCreated DATETIME NULL
);
PRINT 'Created table dbo.FileLog';
END
ELSE
PRINT 'Table dbo.FileLog already exists - skipped';
GO
Route 2: You want a fresh, dedicated training database.
Creates a database called TrainingDB (using the server’s default file locations and settings), creates a SQL login/user for it (TrainingUser / Password, db_owner on TrainingDB only), then creates the three tables used in the Linx training (Clients, Logging, FileLog) inside it.
Run this while connected to the server with a login that is allowed to create databases and logins (e.g. sysadmin). To log in as TrainingUser, the server must allow SQL Server authentication (“SQL Server and Windows Authentication mode”). Safe to run more than once: the database, login, user and each table are only created if they don’t already exist.
===================================================================== */
SET NOCOUNT ON;
IF DB_ID(N'TrainingDB') IS NULL
BEGIN
CREATE DATABASE \[TrainingDB\];
PRINT 'Created database TrainingDB';
END
ELSE
PRINT 'Database TrainingDB already exists - skipped';
GO
-- Server login for the training (training password only - not for real environments)
IF SUSER_ID(N'TrainingUser') IS NULL
BEGIN
CREATE LOGIN \[TrainingUser\]
WITH PASSWORD = N'Password',
DEFAULT_DATABASE = \[TrainingDB\],
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
PRINT 'Created login TrainingUser';
END
ELSE
PRINT 'Login TrainingUser already exists - skipped';
GO
USE [TrainingDB];
GO
-- Database user for the login, with full rights on TrainingDB only
IF USER_ID(N'TrainingUser') IS NULL
BEGIN
CREATE USER \[TrainingUser\] FOR LOGIN \[TrainingUser\];
PRINT 'Created user TrainingUser';
END
ELSE
PRINT 'User TrainingUser already exists - skipped';
IF IS_ROLEMEMBER(N'db_owner', N'TrainingUser') = 0
ALTER ROLE \[db_owner\] ADD MEMBER \[TrainingUser\];
GO
-- Client records loaded from clients.csv and synced to the CRM API
IF OBJECT_ID(N'dbo.Clients', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Clients (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Clients PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Surname NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) NOT NULL,
Cellphone NVARCHAR(50) NULL,
DateAdded DATETIME NULL,
AddedBy NVARCHAR(100) NULL
);
PRINT 'Created table dbo.Clients';
END
ELSE
PRINT 'Table dbo.Clients already exists - skipped';
-- Process / action logging
IF OBJECT_ID(N'dbo.Logging', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Logging (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Logging PRIMARY KEY,
\[DateTime\] DATETIME NULL,
Action NVARCHAR(MAX) NOT NULL,
ActionUser NVARCHAR(100) NULL
);
PRINT 'Created table dbo.Logging';
END
ELSE
PRINT 'Table dbo.Logging already exists - skipped';
-- Audit trail of generated report files (Training Exercise)
IF OBJECT_ID(N'dbo.FileLog', N'U') IS NULL
BEGIN
CREATE TABLE dbo.FileLog (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_FileLog PRIMARY KEY,
FileName NVARCHAR(260) NOT NULL,
DateCreated DATETIME NULL
);
PRINT 'Created table dbo.FileLog';
END
ELSE
PRINT 'Table dbo.FileLog already exists - skipped';
GO
Connecting from Linx
Wherever the training guide uses the SQLite plugin, use the Database plugin instead (e.g. `ExecuteSQL`), with the connection type set to SQL Server.
Connection String:
Data Source=localhost;Initial Catalog=TrainingDB;User ID=TrainingUser;Password=Password;TrustServerCertificate=True
Replace localhost\ with your server/instance name (e.g. \localhost\SQLEXPRESS\ or (localdb)\MSSQLLocalDB`).
We recommend storing the connection string in a Setting so it’s easy to change between the Designer and the Server.