Timeout error migrating large companies to multitenant

gilbertgilbert Member Posts: 11
edited 2014-02-06 in NAV Three Tier

I'm testing on migration of a database to a multitenant environment.
I followed the steps in of Waldo's video (http://www.waldo.be/2013/09/16/nav2013r2-readiness/) , and everything worked well when the company is "small". When I tried to move a "large" company to a new database, the powershell cmdlet gives me a timeout error.


Get-NAVCompany DynamicsNAV71 -tenant Default | Where-Object { $_."CompanyName" -like "*COMPANYNAME*" }|howto-movecompanytotenant DynamicsNAV71 -FromDatabase 'NAV2013' -OldTenantName 'Default' -NewTenantName 'NEWDATABASE' -RemoveCompanyWhenMoved


Invoke-Sqlcmd : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\install\multitenancy\HowTo-MoveCompanyToTenant.ps1:146 char:9
+ Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $FromDatabase -I ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

When I refresh the SQL Databases in Management Studio I can see the new Database, but the old-source database still contains the company tables.

I know I can avoid this error by setting the parameter-querytimeout, but do not know how to use this parameter with NAV cmdlets.

You can help me?

Thank you!


  • Options
    gilbertgilbert Member Posts: 11
    Well, now I can answer to my self:

    If you experience this error, you can avoid it modifying the HowTo-MoveCompanyToTenant.ps1 sample, adding "-querytimeout 0" parameter to the invoke sql sentence that moves the companies to the new tenant database:

    Original: Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $FromDatabase -InputFile $sqlFile -Variable $VariableArray
    Modified: Invoke-Sqlcmd -ServerInstance $DatabaseServer -Database $FromDatabase -InputFile $sqlFile -Variable $VariableArray -querytimeout 0

    Save the ps1 file and call it from ps.

Sign In or Register to comment.