Microsoft SQL Server

The following steps are necessary to set up a new Vertec database on a SQL server.

• On the SQL server, create a blank database named Vertec. You can also use a different name.
• On the SQL server, create a new login named VertecUser under Security > Logins.
• Switch to SQL Server authentication, assign a password and remember it. You will need this password when installing Vertec Server. Note: You can leave the default database on master in this dialog.
• Enter the VertecUser as owner of the new Vertec database:

• In the database Vertec > Security > Users under Schemas owned, check the db_owner.

• Open the properties dialog with the right mouse button on the Vertec database. Under Permissions, activate the point Tabellen erstellen (Create Table) for VertecUser.

• Under Optionen, activate the point Statistiken automatisch aktualisieren:

Technically this is called AUTO_UPDATE_STATISTICS and can also be set via T-SQL ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;.

• Install Vertec as a server according to the article Reinstalling Vertec.
• Please note the notes on handling transaction logs on MS SQL servers.

We recommend that you set up a maintenance plan with which the Index Statistics are recalculated regularly (e.g. over the weekend). How this works depends on the MS SQL Server version you are using and is described in the corresponding Microsoft documentation.

Supported Drivers (Provider)

The following drivers (connection modules) are supported for data access to the MS SQL server and the interaction between Vertec and the MS SQL server:

• OLE DB Provider for SQL Server (SQLOLEDB)
• SQL Server Native Client (SQLNCLI11)
• Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)

The provider is specified in the [Database] Section of the Vertec.ini file.

For the conversion of a Vertec database, please note the following articles:

Database Convert

For versions prior to Vertec 6.5, the following article is relevant:

Convert with microsoft sql server

When loading a SQL Server database from another SQL Server installation (i.e. backup was performed on another SQL Server installation), the Vertec database user must be synchronized with the corresponding server login in order to be able to use the database.

It is best to proceed in 2 steps:

1. Check if the Vertec database user (e.g. VertecUser or whatever is used in the imported database) already exists on the server. The server-wide users can be found in the SQL Manager under Security > Logins. If there is no login with the name of the DB user, create a new SQL Server login (Caution: case sensitive). The password must also be set.
2. If a login exists on the server, the following call to sp_change_users_login can be made on the imported database (with dbo rights) in the Query Analyzer:

   sp_change_users_login "AUTO_FIX", "VertecUser"

   Use the appropriate username instead of VertecUser. This will link the user to login.

The Query Analyzer returns a corresponding message when the login is synchronized successfully.

If the database server is located in a different domain, the following message appears when Vertec logs on to the database:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

By default, both named pipes and TCP/IP protocols are disabled in SQL Server Configuration Manager. For Vertec, however, both protocols must be enabled.

Make sure that the Named Pipes and TCP/IP protocol are Enabled.

Furthermore, the SQL Server port must be entered in the TCP/IP Properties under IP Addresses in all TCP/IP lines (default: 1433).

Then restart the SQL Service.

If you get the above error message when starting Vertec and you get the error message:

Login failed for user 'VertecUser’. (Microsoft SQL Server, Error 18456)

VertecUser cannot log in directly to the SQL server, the reason is probably that in the server properties of the corresponding server instance under security only the Windows Authentication Mode is enabled and not the SQL Server and Windows Authentication Mode. This setting is incorrect for Vertec and is the default setting when reinstalling a SQL server.

Turn on the SQL Server and Windows Authentication Mode and restart the SQL Service.

This error message appears if the SQL Server login has been configured to expire the password.

In SecurityLoginsVertecUser, Context menu Properties, remove Enforce password expiration.

For clients running Windows 2000, this error may occur when starting Vertec.

In this case, MDAC 2.81 for Windows 2000 must be installed on the clients that contain the up-to-date ADO driver.