Data moved from one database to another database in same SQL server in C/AL ?

Pradeep1611

Hi Folk,
Need your assist.
I need to moved "Change Log Entries" table data from one database to another database in same SQL server in C/AL code, is this possible or not in C/AL code?

Thanks in advance.

ShaiHulud

To achieve this, you need to do several things:

• Make a new table in your _another_ database that will store your changelog, define columns, keys, etc in SQL
• Create a new table in your NAV database using Development Environment. It must have the same columns (fields) and primary key as the table you made in the another database. Before you save it, make sure you define 3 extra properties on the table - TableType = ExternalSQL; ExternalName = <name of the table in another DB without the schema>; ExternalSchema = <name of the schema of the table, by default [dbo]>
• Next you need to establish a link to the other database. Use command REGISTERTABLECONNECTION to do so. An example of the "connection" parameter: "Data Source=tcp:<databaseAddress>,<port>;Initial Catalog=<databaseName>;User ID=<userID>;Password=<password>"
• Now you can use that table as any other normal table in CAL to read/write/delete to/from it

jln

Not totally in CAL only.
In principle you can create a change log table as a view towards a different database. Move your records from the existing change log entries to there, rename the view to companyname$Change Log Entries (deleting the normal change log table) and change Change log Entries to a linked object. Not completely trivial, but certainly doable

Pradeep1611

Thanks @jln for solution but my requirement is data moved in another database table from one database in C/AL code. example suppose I created one nav database and at the same time I created an another nav database having all table same properties, So now my requirement is data moved from one database to another database in respective table, which code written in C/AL.

again thanks @jln

RockWithNAV

@Pradeep1611 - You talking about two different things -

1. If you simply need to move the logs of change log to different Database then do it from SQL however you want with the filters and doing with SQL is best option.

2. If you want this to always happen like executing in DB1 and logs getting inserted in DB 2 then it you need to do a lot of customisation and write your codes for every instance, it will be a cumbersome job.

Pradeep1611

@RockWithNAV thanks for your helpful suggestion, Yes your 2nd option I needed, 1st option it can also fulfilled my requirement but I need completed my requirement with your 2nd option because I don't want to depended on SQL developer and any type of middle ware work like calling procedure of SQL from Window Task scheduler etc etc.
So I need fulfilled my requirement with the help of C/AL only from a particular Codeunit, once codeunit run then required table data will moved from one database to another database in respective configured table.

Thanks @RockWithNAV .

ShaiHulud

To achieve this, you need to do several things:

• Make a new table in your _another_ database that will store your changelog, define columns, keys, etc in SQL
• Create a new table in your NAV database using Development Environment. It must have the same columns (fields) and primary key as the table you made in the another database. Before you save it, make sure you define 3 extra properties on the table - TableType = ExternalSQL; ExternalName = <name of the table in another DB without the schema>; ExternalSchema = <name of the schema of the table, by default [dbo]>
• Next you need to establish a link to the other database. Use command REGISTERTABLECONNECTION to do so. An example of the "connection" parameter: "Data Source=tcp:<databaseAddress>,<port>;Initial Catalog=<databaseName>;User ID=<userID>;Password=<password>"
• Now you can use that table as any other normal table in CAL to read/write/delete to/from it

Pradeep1611

@ShaiHulud thanks a lot for the solution, Hope this will work I also did same but I missed about company change parameter.
Thanks for the valuable solution @ShaiHulud