Change chosen SQL Server database in distributed database with Invantive Data Hub

Question

We run a daily job to load Exact Online into a SQL Server database for reporting purposes with Sumatra.

We now want to redirect the SQL insert statement to another database on the same SQL Server.

Connections are:

<connection name="EOLIN_MUT" ...>
  <database order="20" alias="eol" provider="ExactOnlineAll" connectionString="apiUrl=https://start.exactonline.nl;api-client-id=SOMETHING;apiredirecturl=https://eolclientredirect.invantive.com" />
  <database order="10" alias="sql" provider="SqlServer" connectionString="Data Source=something;UID=datahub;PWD=moresecrets" AllowConnectionStringRewrite="false" />

I've already change the name of the connection from EOLIN to EOLIN_MUT but without result. How can I redirect to another database?

Answer 1

You have three alternatives that you can use:

1. Change default database of user
2. Change default database on connection
3. Switch database.

To change default database of the user, have you SQL Admin change it in SQL Server. There is no database listed yet in your connection string.

To change default database of connection, see connectionstrings.com on SQL Server. Add database=NAME; to your connection string in settings.xml.

To switch database, change the Invantive SQL script you use by adding:

use XYZ@sql

where XYZ is the intended default database on SQL Server and sql is the alias on the SQL Server data container.

With use, you can also select multiple data containers, such as:

use XYZ@sql, 123123@eol, 456456@eol

which select XYZ on SQL Server and companies/administrations/divisions 123123 and 456456 on Exact Online with alias eol. More documentation on use statements.

Please note that the default SQL Server provider of Invantive does NOT allow you to select multiple database to be used for a query. So when using Exact Globe or Navision, you will need to explicitly include all companies in your query.