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…



Bookmark and Share

Comments

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Thanks Kine, this is very helpful! :)

Monday, October 21, 2013 7:54 AM by Erik P. Ernst

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Hi Kine

I did a solution in NAV that created a copy script.  In that script I had to skip fields with AutoIncrement set to True.

Is that not a problem in you procedure ?

Friday, October 25, 2013 11:08 AM by Gunnar Gestsson

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

@Gunnar: It is solved by setting the "SET IDENTITY_INSERT" for tables having identity column.

Monday, October 28, 2013 9:41 AM by Kine

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Hi Kine,

I tried to use this stored procedure but it's not working for me.

The sp is succesfull created under the master database.

- If I run the sp directly, the result is: 0.

- If I run the sp by code, the result is: Command(s) completed successfully.

------------Code------------------

use Dynamics_NAV

exec sp_NAVCopyCompany 'SourceCompany','NAVDB','NewCompany'

-------------------------------------

Both action are super fast completed, and the new company is not created.

After this, I've created the NewCompany manually and try these steps again: same result.

What do I wrong?

Wednesday, October 30, 2013 11:27 AM by bvbeek

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

I have notice that few days ago. I think it depends on SQL version. It seems that in some configuration the "select name from sys.all_objects " is running under MASTER db and not in your context (from DB you are running the sp from).

It seems that you need to mark the SP as systém object by:

"EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany"

And than the SP should generate script, which you need to copy and run (it will copy the data to already existing company, deleting existing data in it first.)

Wednesday, November 06, 2013 6:56 AM by Kine

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Hi,

I've just tried this script. But couldn't get it working. I'm trying on a SQL server 11.0.2100.60. The SP is generated, then I ran the

EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany

And then

use myNavDatabase

exec sp_NAVCopyCompany 'SourceCompany','DestinationDB','DestinationCompany'

I have created the Company before running the script.

Monday, December 02, 2013 12:09 PM by Mads W

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Hi Kine,

I've tried exec the script. Then exec the "EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany".

And finally:

use SourceDB

exec sp_NAVCopyCompany 'SourceCompany','DestinationDB','DestinationCompany'

I see the script running and finishing with no errors. But no data is copied.

SQL server is ver. 11.xx

Monday, December 02, 2013 2:00 PM by Mads W

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

You need to take the output of the script and run it as script. This stored procedure is only "generator" for the copy script.

Wednesday, December 04, 2013 9:24 AM by Kine

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

It's worked for me. Thank you.

Wednesday, December 18, 2013 12:23 PM by Angel

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

As iam trying on it. it is not copying effected data from source tables to destination tables.

Saturday, January 04, 2014 3:31 PM by Navaneeth

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

@Navaneeth> And have you run the generated script? Or you just run the stored procedure?

Tuesday, January 14, 2014 9:11 AM by Kine

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Running on SQL 2008 R2. No script gets generated.  sp runs without errors.  I have 15 companies that need to be set up from a template.  Can you please assist?

Tuesday, January 21, 2014 11:49 PM by Alan G.

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

have you runthe 'EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany'  command to mark the SP as system?

Wednesday, January 22, 2014 7:10 AM by Kine

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Found a minor issue when using this excellent sp.

When running the sp, if you have dashes (and probably other strange characters) in your database name it fails since there is no [] around the target database name.

Change line 53 from:

       select @targettable= @targetdb+'.dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'

to:

       select @targettable= '['+@targetdb+'].dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'

Thanks for sharing your work!

Thursday, January 30, 2014 12:37 PM by Andreas L

# re: Copy Microsoft Dynamics NAV company by SQL script into another database

Or you can just enter the parameter (the name of the database) including the []... ;-)

Thursday, March 06, 2014 2:31 PM by Kine

Leave a Comment

(required) 
(required) 
(optional)
(required)