[Bug] Copy-DbaDbTableData using external azure tables and AutoCreateTable Switch causes error

[ca6dsm]

Report

Host used

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

Errors Received

writeErrorStream : True PSMessageDetails : Exception : System.Exception: Table [Data_Dictionary].[xxxxxxxxx] cannot be found in UKHD_FromAzure. Use -AutoCreateTable to automatically create the table on the destination. TargetObject : CategoryInfo : NotSpecified: (:) [Write-Error], Exception FullyQualifiedErrorId : dbatools_Copy-DbaDbTableData,Stop-Function ErrorDetails : Table [Data_Dictionary].[xxxxxxxxxx] cannot be found in UKHD_FromAzure. Use -AutoCreateTable to automatically create the table on the destination. InvocationInfo : System.Management.Automation.InvocationInfo ScriptStackTrace : at Stop-Function, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.133\allcommands.ps1: line 86001 at Copy-DbaDbTableData<Process>, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.133\allcommands.ps1: line 7604 at <ScriptBlock>, \pdc-sys-qnp-05.systems.informatix.loc\SQL_Backup\Powershell\UpdateUKHD_Azure.ps1: line 54 PipelineIterationInfo : {0, 1}

Steps to Reproduce

Expected Behavior

table created and data copied across

Actual Behavior

Table cant be created get error Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'schemaName.XXXXXXXXXXXXXXXXX'. ---> Microsoft.SqlServer.Management.Smo.UnknownPropertyException: RejectType: unknown property $tablescript = $sqlObject | Export-DbaScript -Passthru | Out-String seems to be the issue.

Environmental information

🚨🚨🚨🚨🚨🚨🚨🚨🚨🚨 Please provide the output of the below script

PowerShell Version : 5.1.18362.1110 dbatools latest installed : 1.0.133 Culture of OS : en-GB

SQL Server:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation

us_english

[ca6dsm]

hope this is ok

[wsmelton]

We do not support creating Azure External tables. This would be a feature request because those tables are not SQL Server tables, they are unique to Azure SQL service, and also have a unique syntax to create them.

[wsmelton]

Are you referring to these external tables @ca6dsm ?

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables#create-an-external-table-on-protected-data

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

I recall these tables are not loaded as normal tables you might have in SQL Server. The data for those tables comes from a storage account that contains the external files of the data. They are not utilized for moving data from one table to another in that fashion.

[ca6dsm]

I'm trying to create a SQL on prem std table from the external dB tables and was hoping I could do the export and creation on one go using the copy table . I'll just have to create the tables using another method instead thanks for confirming.

[wsmelton]

Yeah scripting those tables out from Azure SQL won't run on SQL Server because of syntax.

I'm not sure of mix of our other commands could be used for your processing. Not sure it would be efficient though, depends on data size.

[ca6dsm]

Copy-DbaDbTableData work great and is nice and quick so just need to makes sure the target table exists and all is good I think

[wsmelton]

Yes, that is true but getting the definition of an external table will require using lookups and regex to create the same table in SQL Server for Copy command to support it at least.

If someone wants to take a stab we can review the PR though.

[github-actions[bot]]

🚧🚨 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 you have further questions.

[potatoqualitee]

I think this will be easy to address and will reopen for myself in the future

[andreasjordan]

Let me change this from a bug to a feature request...