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 ''' + @pref + ''' as Company,* FROM [dbo].[' + @pref + '$Inquiry Note] WHERE DATALENGTH([Note]) < 4' EXEC sp_executesql @sql_ext FETCH NEXT FROM company_cursor INTO @pref END CLOSE company_cursor DEALLOCATE company_cursor
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.
5 replies on “HOW TO run SQL query for multiple companies in NAV database”
It would be handy if the query results in the loop could be unioned and compnay name added as a column.
Hi Ajit,
What you are asking for of course is possible, adding the company name as part of the select is immediate but to achieve the union you must created a temporily table, insert the result there, make a SELECT to get the results and then delete the temporarly table when completed.
Hello,
Can you please make a post about that?
Hi khaled,
Please provide me details of what you want to achieve and I will help you
Hi, would like to know if you could post an example of Stored Procedure which receives Company Name as parameter and Select data from the table ?