Category: Microsoft

This section of the website contains posts in the category Microsoft related to Microsoft porducts

Categories
Dynamics NAV HOW TO Microsoft SQL Server

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 ''' + @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.

Categories
Dynamics NAV HOW TO Microsoft

HOW TO show BigText in a message window in C/AL

According to the MSDN documentation in the link BigText Data Type

"cannot be shown in a message window"

This example requires that you create the following variables:

Name DataType Subtype
varBigText BigText

It is true that you cannot insert the BigText in a message
[sourcecode lang=”Cside”]MESSAGE(varBigText);[/sourcecode]

HOW TO show BigText in a message window in C/AL?

You can show the content of the variable easily using the FORMAT Function (Code, Text) to convert the BigText in a String.
[sourcecode lang=”Cside”]MESSAGE(FORMAT(varBigText));[/sourcecode]

Did my HOW TO help you? Leave a reply.