Database FAQ

General

What are the default passwords?

SQL server/database

Default username: sa

Default password that is assigned during the installation of the SQL server via the installation routine in JTL-Wawi: sa04jT14

The database password is needed during the import of a backup file.

JTL-Wawi

The username is admin and the password is pass.

If you cannot find your JTL-Wawi admin login data, you can reset it via the database command line:

Open the command line via START > Ausführen (START > Run). Type cmd.exe and confirm your entry with Enter.

In the next window, enter the following command for JTL-Wawi:

osql -S (local)\JTLWAWI -U sa -P sa04jT14 -d eazybusiness -Q "UPDATE tBenutzer SET cPasswort=master.dbo.fn_varbintohexsubstring(0, HASHBYTES('SHA1', CONVERT(varchar(255), iSalt)+'JTL'+'NEUES PASSWORT'), 1, 0) WHERE cLogin='admin'"

Can I use mySQL as an SQL server?

Deinstalling Microsoft SQL Server Express

A current WinRar version is required! You can obtain it here.

Select the file SQLEXPR_DEU.exe in the setup folder SQL-Server  by double-clicking and select Entpacken in SQLEXPR_DEU (Extract to SQLEXPR_DEU) (This is a RAR archive). The file will now be extracted. Next, copy the new folder SQLEXPR_DEU to C:\.

Go to the Command Prompt (Start > Run > »cmd.exe«).
With the command cd \SQLEXPR_DEU you now switch to the folder with the extracted files.

You can then remove the SQL server with this setup call:

start /wait setup.exe /qn REMOVE=All INSTANCENAME=JTLWAWI

This process may take a few minutes.

Settings and workflows

Connecting to several database servers with one client

Error messages

Issue with Windows 7/Windows Server 2008 and Internet Explorer 10

With Windows 7 and Windows Server 2008 R2, issues may arise with the online shop synchronisation/JTL-Worker and Internet Explorer 10. If the synchronisation/Worker freezes after some time, you must implement a Microsoft fix.

You can find the hotfix at : http://support.microsoft.com/kb/2465772

JTLwawi.dll or JTLmisc.dll cannot be opened

Database not found

If issues with the SQL database occur, please proceed as follows:

1. In Control Panel > Administrative Tools > Services, check whether the service SQL Server (JTLWAWI) has been started.
2. Check whether the database files eazybusiness.mdf and eazybusiness_log.ldf are present.

SQL database: c:\programme\microsoft sql server\mssql.1\mssql\data

If there are several instances of the Microsoft SQL Express Server, the folder may vary from mssql.1 and be, for example, mssql.2, etc. instead.

If eazybusiness_log.ldf is missing, please end the service and send a support ticket with an error description and the attached eazybusiness.mdf file (zipped) to JTL. You can also try generating the file yourself.

This is how you can test the SQL server and the database:

Go to Start> Run> cmd.exe and enter all bold commands.

If everything is correct, it should look like this (after -S, write the server name as described in 2).

osql -S (local)\JTLWAWI -U sa -P sa04jT14 -d master

First enter use eazybusiness, then go, then quit.

Missing eazybusiness_log.ldf file

The following paragraph describes how to generate the log file for the eazybusiness database for MSDE.

First, log in to the SQL server with osql -D eazy -U sa -P sa04jt14 -d master.

Now execute the following commands (in bold):

1) Reset the status:

EXEC sp_resetstatus ‘eazybusiness’

2) Set the database property to “allow updates”:

exec sp_configure ‘allow updates’, 1 RECONFIGURE WITH OVERRIDE

3) Set the database to “Emergency Mode”:

UPDATE master..sysdatabases SET status=-32768 WHERE name=’eazybusiness’

4) Rebuild the database log file:

dbcc rebuild_log (‘eazybusiness’,’C:\Programme\Microsoft SQL Server\MSSQL$EAZYSALES\Data\eazybusiness_log.LDF’)

5) Check database:

DBCC CHECKDB(‘eazybusiness’)

6) Reset the status:

UPDATE master..sysdatabases SET status = 0 WHERE name=’eazybusiness’

Activating SQL log file

It is easier to analyse SQL-related errors in JTL-Wawi, especially SQL Error -1 performing exec, if there is an SQL log file. The settings of the ODBC connection can be adjusted under Start > Control Panel > Administrative Tools > OBCD data sources. Important: On 64-bit Windows systems, the ODBC connection must be started under C:\Windows\SysWOW64\odbcad32.exe!

There, select the tab Tracing  and specify the storage location for the log file. Then click Start tracing now.

Exit JTL-Wawi and restart the software. Reproduce the error.

Error message: User has no rights to access this tenant

This message is shown when you create a new tenant in JTL-Wawi. By default, even the admin user cannot initially access new tenants.

1. Log in to the tenant eB-Standard as admin.
2. In the menu Admin > Benutzer / Rechte (Admin > Users/Rights), go to the dialogue box Benutzer, Benutzergruppen und Rechte anlegen / bearbeiten (Create/Edit users, user groups and rights).
3. Double-click on the user to whom you want to give access to the newly created tenant. The dialogue box Benutzer anlegen / bearbeiten und Login-Daten einrichten (Create/Edit users and setup login data) opens.
4. In the pane Firma / Mandant (Company/Tenant), select the newly created tenant. In the pane Firma (Company), select the company for which the tenant database should be activated.

5. Click Speichern (Save) and close all open dialogue windows.
6. Then log in to the new tenant again.

Repairing databases

If consistency issues occur in the database, it must be repaired as soon as possible. The following paragraphs will show you how.

If it is not already installed on your computer, please download the free Microsoft SQL Server Management Studio on the Microsoft website.

After starting Microsoft SQL Server Management Studio, you need to establish the connection to the SQL server.

• Servername (Server name): (local)\JTLWAWI
• Authentifizierung (Authentication): SQL server
• Anmeldename (Login name): sa
• Passwort (Password): sa04jT14

Once you are logged in, open the folder Datenbanken (Databases) und select the entry eazybusiness. Right-click on eazybusiness to view the database properties.

In the dialogue box Datenbankeigenschaften (Database properties), select Optionen (Options). In Weitere Optionen (Other options), go to the entry Zugriff beschränken (Restrict Access) and set it to SINGLE_USER.

Select Datei > Neu > Abfrage mit aktueller Verbindung (File > New > Query with Current Connection).

Enter the following into the pane SQLQuery1.sql and then click on ! Ausführen (Run):

DBCC CHECKDB (eazybusiness,REPAIR_REBUILD)

If the error cannot be fixed, you can also use REPAIR_ALLOW_DATA_LOSS instead of REPAIR_REBUILD. Please note that this can lead to data loss! This process takes a few minutes. If the pane Meldungen (Messages) reports consistency issues, these issues must also be marked as solved. Close the SQLQuery1.sql window.

Running scripts and commands in Microsoft SQL Server Management Studio

Read:

Executing scripts and commands via the command line (osql.exe)

Proceed as follows, to run SQL commands via osql.exe on the computer on which the SQL server is installed:

Start the Windows Command Prompt with START > Run > cmd.exe.

Enter the following command:

osql -D eazy -U sa -P sa04jT14 -d eazybusiness -Q “SQL Befehl”

FAQ on backups and recoveries

Manually restoring backups

Start the Windows Command Prompt with START > Run > cmd.exe.

osql.exe -D eazy -U sa -P sa04jT14 -d master -Q "restore database eazybusiness from disk='c:\meine.bak' with replace"

Backup cannot be restored

If you cannot reimport a database, restore the database using Microsoft SQL Server Management Studio.

Backup not possible

When trying to perform a backup in JTL-Wawi, the following error message appears:

Meldung: Error -1 performing exec
SQL State = 00000
SQL Message=
Src: Exec Direct

This is an issue related to the database service rights.

For security reasons, the database can only be stored in c:\program files\microsoft sql server\mssql.1\mssql\backup.

Help: In Control Panel > Administrative Tools > Services , look for SQL SERVER (JTLWAWI). Right-click to open the context menu. Select Properties. In the tab Log in, select Local System account and tick the check box in front of Allow service to interact with desktop. Now the service must be closed and started again. Afterwards it should be possible to perform database exports in JTL-Wawi.