How to use a SQL table or view as data source

Next to using Excel, CSV and ASCII files as a data source a SQL table or view can be used.
This can be useful if you want to generate an import file based on information stored in a Exact company database or other SQL database.

For example:
- interbranch transfers generating a General journal entry.
- sales orders in a company generating a purchase order.

step 1: Define the view or table containing the data
step 2: Set the correct settings in the import definition file

Example:

When setting these in the import definition file and selecting it in the XConnector it will look like shown in the picture below.
The source file text box is not editable and shows the name of the view and the in the bottom of the screen you will see the settings you made.

Working with date fields from a view with the XConnector

To work with date fields from a view the date must by converted to a string and then the XConnector is able to handle these dates.
Two changes need to be made on in the view and one in the import definition.

1) Change the date fields in the view

Convert the date field to a string in the format yyyymmdd with like
convert(char(8), <fieldname>, 112)

2) Change the date formats in the Import definition

in the import definition the date format must be changed to "yyyymmdd"

example:

More about the SQL convert function you can find at http://msdn.microsoft.com/en-us/library/ms187928.aspx

Changing the SQL Connection- and Command timeout

The Connection timeout is the time defined in seconds the XConnector gets to establish a connection with the Exact database
The Command timeout is the time defined in seconds the XConnector waits for the result to get back from the Exact database.

The XConnector has the following default values:
Connection timeout: 5 seconds
Command timeout: 180 seconds

In some cases these values are too short. This might happen when it takes a long time for a view to return the records to the XConnector.
The values can be changed with the following two settings in the XConnector.ini.

ConnectionTimeout=5
CommandTimeout=300

[unit in seconds]