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 take offline a database in SQL Server

HOW TO take offline a database in SQL Server?

Microsoft Sql Server Management Studio

Right click on the database node > Tasks > Take Offline

T-SQL Script

Execute the following query

ALTER DATABASE [DatabaseName] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

If it seems that the command doesn’t work or the execution requires more time or to force to take offline or

if you’ll receive the following error:
ALTER DATABASE failed because a lock could not be placed on database ‘{DatabaseName}’. Try again later.

click on the following link to show the Solution:
ALTER DATABASE failed because a lock could not be placed on database

Did my HOW TO help you? Leave a reply.