If when using AOI Access Analytics Pivot Tables, the below error is given:
'An error occurred whilst retrieving analytical data. A transport -level error has occurred when receiving results from the server (provider: Session Provider, error: 19 - Physical connection not usable)' then please see the following:
This error occurs when the ODBC is failing to correctly connect to the server which can be due to multiple reasons.
Firstly, ask your IT to investigate your issue to ensure you are able to communicate and connect within your internal network and server.
Once this is confirmed, ensure that your ODBC data source link is correctly setup.
The ODBC data link would need to be setup within the System DSN of the ODBC data source administrator (32bit), as per the guidance below, using the SQL Server Native Client 11.0 driver.
If this does not already exist on the machine when adding an ODBC link, then the driver would need to be downloaded and installed.
Below is the correct method of setting up the ODBC data source link:
Click the Windows Start menu, and search for ODBC Data Sources (32-bit).
Right-click and select Run as administrator.
Select the System DSN tab and click Add... to make a new link, or click Configure... an existing link to review / amend.
On adding a new link, we recommend choosing the SQL Server Native Client 11.0 and click Finish.
Note: Configuring an existing will skip this step.Enter a Name for the connection and enter the relevant SQL server\instance for the Server details.
We recommend to verify the connection authenticity with the second SQL authentication option using the SA credentials.
Click Next.
The only 2 options that should be ticked are the Use ANSI... options.
Click Next.
Untick the Perform translation for character data option. No option should be ticked on this screen.
Click Finish.
The prompted window will allow you to Test Data Source. We strongly recommend testing the data source you have setup to confirm the connection is successful.
Then click OK on the windows to save and exit.