SQL Server 2022 with APPX 5.5.4 on Windows
Set up SQL Server as an APPX file system using FMS type 6. Start with one file, then save a record in APPX and check it in SQL Server.
APPX 5.5.4 · 32-bit engine SQL Server 2022 · Microsoft ODBC Driver 18 for SQL ServerWhat to install
The example uses a 32-bit APPX 5.5.4 engine on Windows 11 25H2 and a 64-bit SQL Server 2022 (16.0.1160.1) database. Install the 32-bit Microsoft ODBC Driver 18 for SQL Server client on the APPX server.
| Component | Download |
|---|---|
| APPX server | Install the 32-bit APPX 5.5.4 engine and set up an APPX Login Manager. APPX 5.5.4 Windows server installer · Installation instructions |
| APPX Desktop Client | Use the 5.5.4 Desktop Client to open an APPX session. Desktop Client installer |
| SQL Server | Install SQL Server 2022 or use an existing server with a dedicated test database. Database installation |
| ODBC driver | Match the client to the APPX engine’s 32-bit architecture. Microsoft ODBC Driver 18 for SQL Server 18.4.1.1 (32-bit) |
Check the APPX/ODBC product registration for SQL Server. If the APPX error names ODB, check the registration before troubleshooting the DSN.
Choose a database and create a login
Ask your DBA for the host, port, and database name. This single-byte example uses appxsb with SQL_Latin1_General_CP1_CI_AS. Use a dedicated schema or database to rehearse the setup. Replace the example password with a generated value.
CREATE DATABASE appxsb COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
CREATE LOGIN appx_sql_demo WITH PASSWORD = N'use_your_test_password',
DEFAULT_DATABASE = appxsb;
GO
USE appxsb;
CREATE USER appx_sql_demo FOR LOGIN appx_sql_demo WITH DEFAULT_SCHEMA = dbo;
GRANT CREATE TABLE TO appx_sql_demo;
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO appx_sql_demo;
GO
Confirm that the SQL login can open the selected database. If the driver reports Cannot open database requested by the login, check the DSN database and the user mapping in that database.
Set up the 32-bit System DSN
On the APPX server, run %windir%\SysWOW64\odbcad32.exe as an administrator. Select System DSN → Add and choose Microsoft ODBC Driver 18 for SQL Server. Set the DSN name to APPX-SQLSERVER, host to 127.0.0.1, standard port to 1433, and database to appxsb.
For a named instance, use its name or assigned TCP port. The local test used shared memory with lpc:.\SQLSERVER2019 in the Server field. For remote TCP connections, keep encryption enabled and use a server name and certificate the APPX server can validate. Test using appx_sql_demo. APPX supplies the password from the FMS identity.
Set up the FMS group and identity
Open a new APPX Desktop Client session. Choose System Administration → Configuration → File System Groups under File Maintenance.
- Add
APPX-SQLSERVERand enter FMS type6. - Click FMS Group Attributes. Enter the values below and save.
| FMS option | Value |
|---|---|
| Data Source Name | APPX-SQLSERVER |
| Table Naming Scheme | APPX_$db_$appl_$file |
| Proxy Database ID | Leave blank |
On the group row, click Identities. Enter your APPX user ID to give that user a database login. Leave APPX User blank to set a default identity for the group. Press Enter to accept the key and again to open the details.
Enter appx_sql_demo in User Id and leave Realm blank. Save the identity and reopen it in Change mode. Click Change Password, enter the database password, and press Tab to move to Confirm Password. Enter it again and click Change Password. When APPX reports Password Changed, END to Exit, close the password dialog and save the identity. Open a fresh APPX session before testing.
Assign one file and create the table
Use a test database and a file you can create empty. This example uses the Demo Application’s COUNTRY file.
- Open System Administration → Database/Applications → Databases / Applications under File Maintenance. Add
TSTwith the company name SQL Server Test. Set the startup application toDMO, version00. Save and accept the report defaults. - For
TST, open Related Applications. AddDMOin Live Operations if it is absent. Select theDMOrow and click Database Management. If APPX prompts for a Language ID, setENGand reopen Database Management. - In File Selection, click Unselect All. Highlight
COUNTRYand press F3. If you use the Select button, enterCOUNTRYin Name and confirm. Return to Data File Management and confirm that one file is selected. - Open File Specifications. Set FMS to
6and FMS Group toAPPX-SQLSERVERforCOUNTRY. Save and return. - Choose Create Files. The result for
COUNTRYshould say Created.
This naming scheme creates dbo.APPX_TST_DMO_COUNTRY in the selected database or schema. For an existing file, follow the APPX/IO migration and rollback guide.
Save and check a record
- From the APPX main menu, choose Run an Application and execute database
TST. In the demo’s top menu, choose Setup → Countries. - In Country Address Format Rules, choose Add. Enter
CANADAfor Country Name,PROVINCEfor Region Label, and2for # Words In Postal Code. Leave Postal Code Location at AFTER REGION. Click Save. - Repeat with
ENCODINGfor Country Name,Québecfor Region Label, and1for # Words In Postal Code. Save. - Open a fresh Desktop Client session, run
TST, and go back to Setup → Countries. Choose Inquire and click First Record if the key field is empty. Find the two keys and confirm the saved values, including the accent inQuébec.
Cannot Access Required File for PROSPECT. In a test database with only COUNTRY created, click Cancel on this message to continue the Countries test. Create the full demo database to run the rest of the application.
Connect independently in sqlcmd as appx_sql_demo. Check the records, encoded value, and unique index on country_name.
SELECT RTRIM(country_name), RTRIM(country_region_label), country_no_words,
CONVERT(varbinary(30), RTRIM(country_region_label)) AS stored_bytes
FROM dbo.APPX_TST_DMO_COUNTRY ORDER BY country_name;
EXEC sp_helpindex N'dbo.APPX_TST_DMO_COUNTRY';
GO
Expect CANADA / PROVINCE / 2 and ENCODING / Québec / 1. In the Windows-1252 text, é is byte E9.
Troubleshooting
Check the APPX engine
This guide uses FMS type 6 (ODBC) through the SQL Server ODBC driver.
In APPX Release Information, record Engine Release and Compile Date. The application release numbers on the same screen identify the APPX system applications. Match the database client or ODBC driver to the bitness of the engine that runs the operation. Check the settings on the APPX server and under the Windows or Linux account that runs the engine.
Test the connection outside APPX
On the APPX server, open the 32-bit ODBC Administrator (%windir%\SysWOW64\odbcad32.exe on 64-bit Windows). Under System DSN, open the DSN from FMS Group Attributes. Confirm the driver, server, port, and database. Run Test with the same login and password as APPX. Close the setup dialog with Cancel if you entered credentials just for the test.
For a timeout or connection refusal, check the database service, listening address and port, and firewall. A database authentication error means the request reached the server. Check the matching server log entry for the reason it rejected the login.
For a TLS error, check the certificate name and Windows trust. For a SQL login error, check the account, the instance’s authentication mode, and the SQL Server error log.
Check the FMS group and Identity
If APPX reports Can’t connect to server (or driver error) after a successful DSN test, confirm that the FMS group’s Data Source Name matches the tested System DSN. The DSN test uses the credentials supplied to the test. APPX uses the credentials from its FMS Identity.
Under Identities, look for an active, unexpired entry for the APPX user running the operation. That entry takes precedence over the blank default. Check User Id and re-enter the database password with Change Password. Save the Identity and open a fresh APPX session. APPX can cache connection and Identity settings for the life of the session.
If the same password works in the DSN test and fails in APPX, check the driver’s connection string rules and the password length supported by the APPX release. A dedicated test login with a newly generated alphanumeric password can help isolate a parsing or length problem.
Check where APPX creates the table
If the connection succeeds, read the Create Files result or open View Log for the database error. Check the database, login, and target schema or database with the same connection settings as APPX. Compare the APPX database ID, application ID, file name, and Table Naming Scheme with the SQL table name.
Check the DSN’s database and the database user’s default schema. The database user needs CREATE TABLE in the database and ALTER on the target schema. SQL Server table creation permissions
For an APPX error that names the ODB license, check the APPX/ODBC registration on the engine that opened the file. A driver connection test does not exercise the APPX registration check.
Capture one failing operation
In
appx.envunder the server’s effectiveAPPXPATH, temporarily set the variables below. Create a private log directory that the engine account can write to.0x13includes generated SQL, return statuses, and connection operations.APPX_SQL_CMD=C:\APPX\Logs\appx-rdbms.log APPX_DBG_CODE=0x13The engine reads these settings when it starts.
appx.envcan override values from the operating system.APPX_SQL_CMDandAPPX_DBG_CODEapply to the engine’s database access.APPX_ODBC_LOGandAPPX_ODBC_CODEapply to the APPX ODBC driver used by external programs to read APPX data.- In the same 32-bit ODBC Administrator, open Tracing. Set Log File Path to a private file the engine can write, such as
C:\APPX\Logs\odbc.log. Click Apply, then Start Tracing Now. If the engine runs under another Windows identity, use Machine-Wide tracing for all user identities for this attempt. Windows ODBC tracing - Open a fresh APPX session and reproduce the failure once. For Create Files, select only the intended test file. If the APPX log is missing, check the engine’s
APPXPATH, the effective variables, and the engine account’s access to the log directory. If the log exists, its environment section shows the settings APPX used. - Click Stop Tracing Now. If you enabled machine-wide tracing, clear that checkbox and apply the change. Restore the previous
appx.envsettings.
In the ODBC trace, find the first failed call and its diagnostic records. For SQLDriverConnect (which may appear as SQLDriverConnectW), capture the return code, SQLSTATE, and full driver message. IM002 points to DSN or driver lookup. 08001 covers several connection failures, so read the message that accompanies it.
Keep the logs private. The APPX SQL log can record the full connection string, including the password, and traces can include record data. Share only the relevant excerpt with secrets and record data removed. Include the time and time zone, APPX engine release and build date, the file and operation, and the database server’s matching diagnostic when available. APPX environment settings
If the SQL query returns a different result from the APPX screen, verify the effective FMS group, database, schema, and table name. Restart the APPX session after changing any of them.