Copy Microsoft Dynamics NAV company by SQL script into another database

After Microsoft Dynamics NAV 2013 R2 release, there are complains about impossibility to copy company from one database to another (you can copy company inside database through standard NAV client interface). Thus I spent some time creating stored procedure which will do it for you… or, this stored procedure will generate script for you, which will do it for you… of course, you can modify the script as you wish to fix possible bugs or extend the functionality and I will be happy when you will share your versions with us.

 

There is script to create the stored procedure:

USE master
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Kamil Sáček
-- Create date: 18.10.2013
-- Description:    Function for copying comany from one database to another
-- =============================================
CREATE PROCEDURE sp_NAVCopyCompany 
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    declare @tablename varchar(1000)
    declare @columns varchar(max)
    declare @columnname varchar (max)
    declare @targettable varchar (max)
    declare @isidentity int

    DECLARE table_cursor CURSOR for
      select name from sys.all_objects where type='U' and object_id>0 and name like @sourcecompany+'$%'
    OPEN table_cursor

    FETCH NEXT FROM table_cursor 
    INTO @tablename
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE column_cursor CURSOR for
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename and COLUMN_NAME <> 'timestamp'
        select @columns=''
        OPEN column_cursor
        FETCH NEXT from column_cursor
        INTO @columnname
        WHILE @@FETCH_STATUS=0
        BEGIN
            SELECT @columns=@columns+',['+@columnname+']'
            FETCH NEXT from column_cursor
            INTO @columnname
        END
        CLOSE column_cursor;
        DEALLOCATE column_cursor;
        select @columns = SUBSTRING(@columns,2,LEN(@columns)-1)
        select @targettable= @targetdb+'.dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'
        select @isidentity=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tablename AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
        print 'RAISERROR ('''+REPLACE(@targettable,'%','%%')+''' , 0, 1) WITH NOWAIT '
        IF (@isidentity>0)
          print 'SET IDENTITY_INSERT '+@targettable+' ON'

        print 'delete from '+@targettable
        print 'insert into '+@targettable+ ' ('+ @columns + ')'
        print '    select '+@columns
        print '       from ['+@tablename+']' 

        IF (@isidentity>0)
          print 'SET IDENTITY_INSERT '+@targettable+' OFF'

        FETCH NEXT FROM table_cursor 
        INTO @tablename
    END 
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END
GO
------------ Update 6.11.2013--------
After that you need to mark the SP as system by running this, else the SP will return no data (will work with master DB instead actual DB):
 
EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany
------------End of update -----------

After you create the stored procedure on the server, you can use it by running something like this:
use myNavDatabase
exec sp_NAVCopyCompany 'CRONUS International Ltd_','NAV2009R2_W1','Test'



In output window you will have script with needed commands which will copy the data. Copy it, run it. It will do what you need. If you focus the output window after starting the script (by pressing F6 for example), you will see the tables which are copied.


 


This script copies all “per company” tables and it is able to copy the data into database, in which you already prepared the company (all existing data in the target company are deleted!). It means, the structure of tables must be same between source and target company!


You can use this script to “update” existing copy of the company to keep e.g. testing company actual Veselý obličej


Usage of this script are on your own danger!


 


I wish you many copied companies without any problem…

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>