Copying Exact online data to Google Cloud SQL

Questions
,
1

We built an application during introduction session to copy data from Exact online to Google Cloud SQL. At first, we had two tables, Sales Orders and Sales Order Lines. I updated the code yesterday to include more tables:

begin
create or replace table eol_SalesOrdersV2Incremental@mss
as
select *
from   
SalesOrdersV2Incremental@eol
  ;
create or replace table eol_SalesOrderLinesV2Incremental@mss
as 
select * 
from 
SalesOrderLinesV2Incremental@eol
 ;
create or replace table eol_AccountsIncremental@mss
as 
select *
from 
AccountsIncremental@eol
 ;
create or replace table eol_ContactsIncremental@mss
as 
select * 
from 
ContactsIncremental@eol
 ;
create or replace table eol_ItemWarehouses@mss
as 
select * 
from 
ItemWarehouses@eol
 ;
create or replace table eol_AccountOwners@mss
as 
select * 
from 
AccountOwners@eol
 ;
end;

I waited till today’s morning since I thought it needs some time but I ended only seeing Sales Orders, Sales Order Lines and Accounts. I re-executed the module and now Sales Order Lines is not even there anymore. Could you tell me what is the problem here?

3

Sorry to write again, but I updated my code:

begin
create or replace table eol_SalesOrdersV2Incremental@mss
as
select *
from   
SalesOrdersV2Incremental@eol
  ;
create or replace table eol_SalesOrderLinesV2Incremental@mss
as 
select * 
from 
SalesOrderLinesV2Incremental@eol
 ;
create or replace table eol_AccountsIncremental@mss
as 
select *
from 
AccountsIncremental@eol
 ;
create or replace table eol_ContactsIncremental@mss
as 
select * 
from 
ContactsIncremental@eol
 ;
create or replace table eol_AccountClassifications@mss
as 
select * 
from 
AccountClassifications@eol
 ;
create or replace table eol_.SelectionCodes@mss
as 
select * 
from 
SelectionCodes@eol
 ;
create or replace table ExactOnlineREST.Logistics.Items@mss
as 
select * 
from 
ExactOnlineREST.Logistics.Items@eol
 ;
end;

Now with this code I got the following error:

An error occured - 30c7147d-e20b-403d-a018-e3aa33374189
itgeneor620: The identifier ‘ACCOUNTCLASSIFICATIONS’ is ambiguous.
Please specify the full name, for instance: ExactOnlineREST.CRM.AccountClassifications, ExactOnlineXML.XML.AccountClassifications. (ACCOUNTCLASSIFICATIONS).

but when i write my code like this:

begin
create or replace table eol_SalesOrdersV2Incremental@mss
as
select *
from   
SalesOrdersV2Incremental@eol
  ;
create or replace table eol_SalesOrderLinesV2Incremental@mss
as 
select * 
from 
SalesOrderLinesV2Incremental@eol
 ;
create or replace table eol_AccountsIncremental@mss
as 
select *
from 
AccountsIncremental@eol
 ;
create or replace table eol_ContactsIncremental@mss
as 
select * 
from 
ContactsIncremental@eol
 ;
create or replace table eol_ExactOnlineREST.CRM.AccountClassifications@mss
as 
select * 
from 
ExactOnlineREST.CRM.AccountClassifications@eol
 ;
create or replace table eol_SelectionCodes@mss
as 
select * 
from 
SelectionCodes@eol
 ;
create or replace table eol_Items@mss
as 
select * 
from 
Items@eol
 ;
end;

I get this error:

An error occured - e18d2159-8e14-4f9b-8f21-90d2f2057d15
itgenase347: There is no catalog support on the SqlServer driver. (EOL_EXACTONLINEREST).

I don’t understand what is the problem here. The tables from the schema Incremental seem to be synching normally but not the tables from the other schemas. What can I do differently here?

4

It is recommended during the learning of Invantive UniversalSQL to make small changes iteratively.

In this case, the failing statement are resolving the ambiguity of the Exact Online schema is:

create or replace table eol_ExactOnlineREST.CRM.AccountClassifications@mss
as 
select * 
from 
ExactOnlineREST.CRM.AccountClassifications@eol

This statement reads all columns and rows from the fully qualified table ExactOnlineREST.CRM.AccountClassifications@eol in Exact Online. And then creates and loads a table on SQL Server (@mss) named: eol_ExactOnlineREST.CRM.AccountClassifications@mss.

As with Exact Online, the catalog is eol_ExactOnlineREST, schema is CRM and table is AccountClassifications. But probably you don’t want to use a new catalog or schema.

The easiest way to test such is to run and test the individual statements in the UniversalSQL-editor, such as:

begin
create or replace table eol_AccountClassifications@mss
as 
select * 
from 
ExactOnlineREST.CRM.AccountClassifications@eol
end;

The full grammar is available on:

https://cloud.invantive.com/sql-grammar/invantive-sql-grammar-current.html

5

Dit topic is 3 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.