Connect to SQL Server and Nmbrs in one session

Question

I would like to insert data into SQL Server directly from NMBRS in a distributed transaction, like for instance:

insert into table@sqlserver
( fields )
select fields from employees@nmbrs

By default only the datadictionary, inmemorystorage, os and mail aliases are defined. How can I add the right aliases?

Answer 1

Use the following configuration in your settings.xml or settings-<SOMETHING>.xml file:

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3" forcedefault="false">
  <group name="Production" >
    <connection name="Stuff" >
      <database order="30" alias="nmbrs" provider="NmbrsNl" connectionString="apiurl=https://api.nmbrs.nl/soap/v2.1" />
      <database order="20" alias="sqlserver" provider="SqlServer" connectionString="Data Source=blabla;UID=stuff;PWD=secret" AllowConnectionStringRewrite="false" />
    </connection>
  </group>
</settings>

Put them in your %USERPROFILE%\invantive folder. A settings-*.xml will be merged with your central settings.xml file.

The disable on allow connection string rewrite makes it possible to hard-code the user name/password for SQL Server. When you remove it, the log on will ask you for the credentials of SQL Server too.