PowerShell - Write-SqlTableData: Cannot Ignore Compute Column

[van-thieu]

Prerequisites

• Write a descriptive title.
• Make sure you are able to repro it on the latest released version
• Search the existing issues.
• Refer to the FAQ.
• Refer to Differences between Windows PowerShell 5.1 and PowerShell.

Steps to reproduce

I'm having issue inserting new data onto a table using the SqlServer PowerShell Module, with the Write-SqlTableData command.
I've yet to find a way to bypass compute column using this method.
It's a reproducible error, and would appreciate if someone could assist.
Thanks.

Expected behavior

Write-SqlTableData -ServerInstance $Server -DatabaseName $Database -TableName $Table -SchemaName "dbo" -InputData (
[PSCustomObject] @{
   Column1 = "A"
   Column2 = "B"  
})

Column3 = Column1 + Column2
"AB"

Actual behavior

Given Column1, Column2, Column3 (Compute) in MSSQL.

Write-SqlTableData -ServerInstance $Server -DatabaseName $Database -TableName $Table -SchemaName $Schema -InputData (
[PSCustomObject] @{
   Column1 = "A"
   Column2 = "B"  
})

Error: The column "Column3" cannot be modified because it is either a computed column or is the result of a UNION operator.

Error details

Exception             : 
    Type               : System.Data.SqlClient.SqlException
    Errors             : 
        Source     : Core .Net SqlClient Data Provider
        Number     : 271
        State      : 1
        Class      : 16
        Server     : localhost
        Message    : The column "Column3" cannot be modified because it is either a computed column or is the result of a UNION operator.
        LineNumber : 1
    ClientConnectionId : b6483fb0-f875-49f3-976e-3acdf845d1a8
    Class              : 16
    LineNumber         : 1
    Number             : 271
    Server             : localhost
    State              : 1
    Source             : Core .Net SqlClient Data Provider
    ErrorCode          : -2146232060
    TargetSite         : 
        Name          : OnError
        DeclaringType : System.Data.SqlClient.SqlConnection
        MemberType    : Method
        Module        : System.Data.SqlClient.dll
    Message            : The column "Column3" cannot be modified because it is either a computed column or is the result of a UNION operator.
    Data               : System.Collections.ListDictionaryInternal
    HResult            : -2146232060
    StackTrace         : 
   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.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at System.Data.SqlClient.SqlBulkCopy.SubmitUpdateBulkCommand(String TDSCommand)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts,
TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1
source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
   at Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData.ProcessTarget(Table table, SmoRecordContext context)
TargetObject          : [dbo].[Test]
CategoryInfo          : WriteError: ([dbo].[Test]:Table) [Write-SqlTableData], SqlException
FullyQualifiedErrorId : WriteToTableFailure,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData
InvocationInfo        : 
    MyCommand        : Write-SqlTableData
    ScriptLineNumber : 49
    OffsetInLine     : 19
    HistoryId        : 3
    ScriptName       : C:\Temp\Test.ps1
    Line             : $Table = (Write-SqlTableData -ServerInstance $Server -DatabaseName $Database -TableName $Table -SchemaName $Schema -InputData ([PSCustomObject]@{

    PositionMessage  : At C:\Temp\Test.ps1:49 char:19
                       + …   $Table = (Write-SqlTableData -ServerInstance $Server -DatabaseName  …
                       +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    PSScriptRoot     : C:\Temp\
    PSCommandPath    : C:\Temp\Test.ps1
    InvocationName   : Write-SqlTableData
    CommandOrigin    : Internal
ScriptStackTrace      : at <ScriptBlock>, C:\Temp\Test.ps1: line 49
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo :

Environment data

Name                           Value
----                           -----
PSVersion                      7.3.0
PSEdition                      Core
GitCommitId                    7.3.0
OS                             Microsoft Windows 10.0.17763
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visuals

No response

[SteveL-MSFT]

I don't see anything here to indicate this is an issue with PS7 vs the specific module? You'll need to follow-up with SqlServer team who owns this module.

[van-thieu]

I don't see anything here to indicate this is an issue with PS7 vs the specific module? You'll need to follow-up with SqlServer team who owns this module.

MS owns it and it's impossible to get in touch with the team that handles SqlServer PS module.

This issue has been marked as external and has not had any activity for 1 day. It has been be closed for housekeeping purposes.