Kine's Info

What I found and what I know about Microsoft Dynamics NAV

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…



  • Thanks Kine, this is very helpful! :)

  • Anonymous
    Anonymous

    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 ?

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

  • Anonymous
    Anonymous

    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?

  • 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.)

  • Anonymous
    Anonymous

    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.

  • Anonymous
    Anonymous

    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

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

  • Anonymous
    Anonymous

    It's worked for me. Thank you.

  • Anonymous
    Anonymous

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

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

  • Anonymous
    Anonymous

    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?

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

  • Anonymous
    Anonymous

    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!

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