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 SQL Server

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
[sourcecode lang=”sql”]
ALTER DATABASE [DatabaseName] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
[/sourcecode]

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.