Copy-DbaDbTableData - Support external azure tables with AutoCreateTable switch

ca6dsm commented 3 years ago

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, C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.133\allcommands.ps1: line 7604 at , \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

ca6dsm commented 3 years ago

hope this is ok

wsmelton commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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] commented 3 years ago

🚧🚨 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 commented 3 years ago

I will close this for now, but you can reopen it if you have further questions.

potatoqualitee commented 2 years ago

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

andreasjordan commented 2 years ago

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

dataplat / dbatools