[Bug] Copy-DbaDbTableDate does not copy table data between two different Azure SQL Databases

[gbargsley]

Environmental information

PowerShell Version        : 5.1.14393.3471
dbatools latest installed : 1.0.115
Culture of OS             : en-US

SQL Server:

Source = Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 31 2020 08:26:29   Copyright (C) 2019 Microsoft Corporation 
Destination = Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 31 2020 08:26:29   Copyright (C) 2019 Microsoft Corporation 

Source = us_english
Destination = us_english

Report

Host used

• powershell.exe
• ISE
• VS Code
• Other (please specify)

If anything other than powershell.exe was used, please confirm that you can duplicate the issue with powershell.exe

• Still buggy in powershell.exe

Errors Received

Screen output:

VERBOSE: [10:54:19][Connect-DbaInstance] Connecting to Data Source=TCP:source.database.windows.net,1433;User ID=serveradmin;Password=********;MultipleActiveResultSets=False;Connect 
Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"
VERBOSE: [10:54:19][Connect-DbaInstance] Connecting to Azure: source.database.windows.net
VERBOSE: [10:54:23][Connect-DbaInstance] Connecting to Data Source=TCP:destination.database.windows.net,1433;User ID=serveradmin;Password=********;MultipleActiveResultSets=False;Connect 
Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"
VERBOSE: [10:54:24][Connect-DbaInstance] Connecting to Azure: destination.database.windows.net
VERBOSE: Performing the operation "Truncating table [dbo].[TPGGLBrowseTransStagingDBATools]" on target "[TCP:destination.database.windows.net,1433]".
VERBOSE: [10:54:26][Connect-DbaInstance] Connecting to Data Source=TCP:destination.database.windows.net,1433;User ID=serveradmin;Password=********;MultipleActiveResultSets=False;Connect 
Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"
VERBOSE: [10:54:26][Connect-DbaInstance] Connecting to Azure: source.database.windows.net
VERBOSE: Performing the operation "Copy data from [dbo].[TPGGLBrowseTransStaging]" on target "[TCP:source.database.windows.net,1433]".
VERBOSE: Performing the operation "Writing rows to [dbo].[TPGGLBrowseTransStagingDBATools]" on target "[TCP:destination.database.windows.net,1433]".
WARNING: [10:54:28][Copy-DbaDbTableData] Something went wrong | Invalid object name 'dbo.TPGGLBrowseTransStaging'.

Stack Trace:

writeErrorStream      : True
PSMessageDetails      : 
Exception             : System.Exception: Invalid object name 'dbo.TPGGLBrowseTransStaging'. ---> System.Data.SqlClient.SqlException: Invalid object name 'dbo.TPGGLBrowseTransStaging'.
                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                           at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, 
                        TdsParserStateObject stateObj, Boolean& dataReady)
                           at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                           at System.Data.SqlClient.SqlDataReader.get_MetaData()
                           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean 
                        forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
                           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, 
                        Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 
                        completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                           at System.Data.SqlClient.SqlCommand.ExecuteReader()
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
TargetObject          : source.database.windows.net
CategoryInfo          : NotSpecified: (source.database.windows.net:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Copy-DbaDbTableData,Stop-Function
ErrorDetails          : Invalid object name 'dbo.TPGGLBrowseTransStaging'.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, C:\Users\admin_gb\Documents\WindowsPowerShell\Modules\dbatools\1.0.115\allcommands.ps1: line 83636
                        at Copy-DbaDbTableData<Process>, C:\Users\admin_gb\Documents\WindowsPowerShell\Modules\dbatools\1.0.115\allcommands.ps1: line 7217
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1}

Steps to Reproduce

Command I am running:

$SourceConnection = "source.database.windows.net"
$PasswordSource = ConvertTo-SecureString "sourcePW" -AsPlainText -Force
$AzureCredentialSource = New-Object System.Management.Automation.PSCredential("serveradmin"),$PasswordSource
$DestinationConnection = "destination.database.windows.net"
$PasswordDestination = ConvertTo-SecureString "destinationPW" -AsPlainText -Force
$AzureCredentialDestination = New-Object System.Management.Automation.PSCredential("serveradmin"),$PasswordDestination

$RunSettings = @{
    SqlInstance = $SourceConnection
    Destination = $DestinationConnection
    Database = 'ax_ods'
    DestinationDatabase = 'AX_ODS'
    Table = 'TPGGLBrowseTransStaging'
    DestinationTable = 'TPGGLBrowseTransStagingDBATools'
    Truncate = $true
    SqlCredential = $AzureCredentialSource
    DestinationSqlCredential = $AzureCredentialDestination
    Verbose = $true
}

Copy-DbaDbTableData @RunSettings

Expected Behavior

I expect the data in the source table to be copied to the destination table.

Actual Behavior

Errors out saying invalid object name.

[iampeterdaniels]

Seems to potentially be an issue copying from Azure SQL DB to a SQL Server Instance, too.

[iampeterdaniels]

My error:

writeErrorStream : True
PSMessageDetails :
Exception : System.Exception: Invalid object name 'dbo.dimPosition'. ---> System.Data.SqlClient.SqlException:
Invalid object name 'dbo.dimPosition'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection,
Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout,
Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior
runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at CallSite.Target(Closure , CallSite , Object )
--- End of inner exception stack trace ---
TargetObject : e000crbdw1sqls1.database.windows.net
CategoryInfo : NotSpecified: (e000crbdw1sqls1.database.windows.net:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Copy-DbaDbTableData,Stop-Function
ErrorDetails : Invalid object name 'dbo.dimPosition'.
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.116\allcommands.ps1: line 83938
at Copy-DbaDbTableData, C:\Program
Files\WindowsPowerShell\Modules\dbatools\1.0.116\allcommands.ps1: line 7219
at , : line 1
PipelineIterationInfo : {0, 1}

[sdecker]

Running into the same issue. I found it is fixed in this commit a5c1417#diff-6a1a2a29aba2fa26d4a038d408c6375e. We just need it released.

[wsmelton]

@sdecker if you can confirm your issue goes away using our dev branch it would be appreciated.

I can see if I can get a release out this weekend.

[sdecker]

Yes, my issue goes away with the dev branch.

…

On Fri, Sep 11, 2020, 18:11 Shawn Melton ***@***.***> wrote: @sdecker <https://github.com/sdecker> if you can confirm your issue goes away using our dev branch it would be appreciated. I can see if I can get a release out this weekend. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#6743 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABO4UDBTMIIJEMSKE27GFWTSFKOCLANCNFSM4QBZTYSQ> .

[Ncedia]

I'm trying to copy a table from SQL Server to Azure SQL Server and get this error: Table dbo.xxxx cannot be found in xxxx-db-test. Use -AutoCreateTable to automatically create the table on the destination.

The table exists, and when i add a new name to the destination table parameter, and use -AutoCreateTable, the new table is created, but when data should be copied, I get the same error:

Table dbo.[xxxx] cannot be found in xxx-db-test. Use -AutoCreateTable to automatically create the table on the destination.
At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.126\allcommands.ps1:84595 char:9

•     throw $records[0]
  

•     ~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], Exception
  • FullyQualifiedErrorId : dbatools_Copy-DbaDbTableData

Could this be another issue with Azure SQL Server? (No success with the dev branch)

[lancasteradam]

@Ncedia, I tried the scenario and it is working for me. I suggest running the Update-Dbatools command to ensure you have the latest build. If the issue still persists we'll need you to run the command with -Verbose and post the output here along with the command arguments being used.

[github-actions]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary
  🚨🚧
  ⌛️ This issue will be closed in 30 days ⌛️

[andreasjordan]

I will close this for now, but you can reopen it if this is still an issue with the current version.

[dalehhirt]

I am still seeing this error. I need to use an AccessToken from Get-AzAccessToken as no other method is currently available to me.

[niphlod]

hi @dalehhirt , can you try this "trick" ? #9104 (comment) . We're in the process of overhauling accesstoken #9612 but try it in the meantime