HOW TO run SQL query for multiple companies in NAV database

In Micrososoft Dynamics NAV, tables with property DataPerCompany = No are stored in SQL in multiple tables having the company name as prefix, than $ and the name.

Example, the Customer table in Cronus Company:

CRONUS UK Ltd_$Customer

The following T-SQL Script retrieves the list of the companies, loop through them and use the Company Name as prefix (@pref) to run a query (@sql_ext).

DECLARE @pref nvarchar(30)
DECLARE @sql_ext nvarchar(max)

DECLARE company_cursor CURSOR FOR 
SELECT replace(Name,'.','_') FROM Company

OPEN company_cursor
FETCH NEXT FROM company_cursor INTO @pref 

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql_ext = N'SELECT * FROM [dbo].[' + @pref + '$Inquiry Note] WHERE DATALENGTH([Note]) < 4'

EXEC sp_executesql @sql_ext
FETCH NEXT FROM company_cursor INTO @pref 
END

Here find another reading suggestion, this time in C/AL: HOW TO change data for all Companies in NAV

Did my HOW TO help you? Leave a reply.

HOW TO change data for all Companies in NAV

HOW TO change data for all Companies?

You have different ways to complete this task, the choice depends from how many lines or Companies you have. In the following steps I will explain you how to change data for all Companies in C/SIDE using the CHANGECOMPANY Function (Record):

  1. Create a new ProcessingOnly Report using with the System Table 2000000006 – Company as Data Source of a DataItem.
  2. Declare a C/AL Globals Variable of DataType Record with the table that you need.
  3. Insert the following code in the OnAfterGetRecord trigger

    TO MODIFY

    GLSetup.CHANGECOMPANY(Name);
    
    GLSetup.MODIFYALL("Allow Posting From", 010114D); 
    GLSetup.MODIFYALL("Allow Posting To", 310114D);
    

    or

    TO DELETE

    ChangeLogSetup.CHANGECOMPANY(Name);
    
    ChangeLogSetup.DELETEALL; 
    

Here find another reading suggestion, this time in T-SQL: HOW TO run SQL query for multiple companies in NAV database

Did my HOW TO help you? Leave a reply.