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

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

Open ca6dsm opened this issue 3 years ago • 11 comments

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:

us_english

Dec 03 '20 10:12 ca6dsm

hope this is ok

Dec 04 '20 15:12 ca6dsm

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.

Dec 04 '20 19:12 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.

Dec 04 '20 19:12 wsmelton

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.

Dec 06 '20 00:12 ca6dsm

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.

Dec 06 '20 02:12 wsmelton

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

Dec 07 '20 09:12 ca6dsm

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.

Dec 07 '20 13:12 wsmelton

🚧🚨 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 ⌛️